使用 EKM 在 SQL Server 上启用 TDEEnable TDE on SQL Server Using EKM

适用对象:是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

本文介绍如何在 SQL ServerSQL Server 中启用透明数据加密 (TDE),以便通过结合使用存储在可扩展密钥管理 (EKM) 模块中的非对称密钥和 Transact-SQLTransact-SQL 来保护数据库加密密钥。This article describes how to enable transparent data encryption (TDE) in SQL ServerSQL Server to protect a database encryption key by using an asymmetric key stored in an extensible key management (EKM) module with Transact-SQLTransact-SQL.

TDE 使用称为数据库加密密钥的对称密钥加密整个数据库的存储。TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. 还可以使用受主数据库的数据库主密钥保护的证书来保护数据库加密密钥。The database encryption key can also be protected using a certificate, which is protected by the database master key of the master database. 有关使用数据库主密钥保护数据库加密密钥的详细信息,请参阅透明数据加密 (TDE)For more information about protecting the database encryption key by using the database master key, see Transparent Data Encryption (TDE). 有关当 SQL ServerSQL Server 在 Azure VM 上运行时配置 TDE 的信息,请参阅使用 Azure Key Vault 的可扩展密钥管理 (SQL Server)For information about configuring TDE when SQL ServerSQL Server is running on an Azure VM, see Extensible Key Management Using Azure Key Vault (SQL Server). 有关使用 Azure 密钥保管库中的密钥配置 TDE 的信息,请参阅 使用具有 SQL 加密功能的 SQL Server 连接器For information about configuring TDE using a key in the Azure key vault, see Use SQL Server Connector with SQL Encryption Features.

开始之前Before You Begin

限制和局限Limitations and Restrictions

  • 必须是高特权用户(如系统管理员)才能创建数据库加密密钥以及加密数据库。You must be a high privileged user (such as a system administrator) to create a database encryption key and encrypt a database. 该用户必须能够通过 EKM 模块进行身份验证。That user must be able to be authenticated by the EKM module.

  • 启动时,数据库引擎Database Engine 必须打开数据库。Upon startup the 数据库引擎Database Engine must open the database. 为此,您应创建一个将通过 EKM 进行身份验证的凭据,并将该凭据添加到一个基于非对称密钥的登录名。To do this, you should create a credential that will be authenticated by the EKM, and add it to a login that is based on an asymmetric key. 用户无法使用该登录名进行登录,但 数据库引擎Database Engine 将能够通过 EKM 设备对其自身进行身份验证。Users cannot sign in using that login, but the 数据库引擎Database Engine will be able to authenticate itself with the EKM device.

  • 如果存储在 EKM 模块中的非对称密钥丢失, SQL ServerSQL Server将无法打开数据库。If the asymmetric key stored in the EKM module is lost, the database will not be able to be opened by SQL ServerSQL Server. 如果 EKM 提供程序允许你备份非对称密钥,则应该创建备份并将该备份存储到安全的位置。If the EKM provider lets you back up the asymmetric key, you should create a backup and store it in a secure location.

  • 您的 EKM 提供程序所需的选项和参数可能与下面的代码示例中所提供的选项和参数不同。The options and parameters required by your EKM provider can differ from what is provided in the code example below. 有关详细信息,请参阅 EKM 提供程序。For more information, see your EKM provider.

SecuritySecurity

权限Permissions

本文使用了以下权限:This article uses the following permissions:

  • 若要更改配置选项以及运行 RECONFIGURE 语句,您必须具有 ALTER SETTINGS 服务器级别权限。To change a configuration option and run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. ALTER SETTINGS 权限由 sysadminserveradmin 固定服务器角色隐式持有。The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.

  • 需要 ALTER ANY CREDENTIAL 权限。Requires ALTER ANY CREDENTIAL permission.

  • 需要 ALTER ANY LOGIN 权限。Requires ALTER ANY LOGIN permission.

  • 需要 CREATE ASYMMETRIC KEY 权限。Requires CREATE ASYMMETRIC KEY permission.

  • 需要拥有对数据库的 CONTROL 权限才能加密该数据库。Requires CONTROL permission on the database to encrypt the database.

使用 Transact-SQLUsing Transact-SQL

使用 EKM 启用 TDETo enable TDE using EKM

  1. 将由 EKM 提供程序提供的文件复制到 SQL ServerSQL Server 计算机上的相应位置。Copy the files supplied by the EKM provider to an appropriate location on the SQL ServerSQL Server computer. 在本示例中,我们使用 C:\EKM 文件夹。In this example, we use the C:\EKM folder.

  2. 根据 EKM 提供程序的要求,将证书安装到计算机上。Install certificates to the computer as required by your EKM provider.

    备注

    SQL ServerSQL Server 不提供 EKM 提供程序。does not supply an EKM provider. 每个 EKM 提供程序可以有不同的安装、配置和授权用户的过程。Each EKM provider can have different procedures for installing, configuring, and authorizing users. 请查阅 EKM 提供程序文档,完成此步骤。Consult your EKM provider documentation to complete this step.

  3. “对象资源管理器” 中,连接到 数据库引擎Database Engine的实例。In Object Explorer, connect to an instance of 数据库引擎Database Engine.

  4. 在标准菜单栏上,单击 “新建查询”On the Standard bar, click New Query.

  5. 将以下示例复制并粘贴到查询窗口中,然后单击“执行” 。Copy and paste the following example into the query window and click Execute.

    -- Enable advanced options.  
    sp_configure 'show advanced options', 1 ;  
    GO  
    RECONFIGURE ;  
    GO  
    -- Enable EKM provider  
    sp_configure 'EKM provider enabled', 1 ;  
    GO  
    RECONFIGURE ;  
    GO  
    -- Create a cryptographic provider, which we have chosen to call "EKM_Prov," based on an EKM provider  
    
    CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov   
    FROM FILE = 'C:\EKM_Files\KeyProvFile.dll' ;  
    GO  
    
    -- Create a credential that will be used by system administrators.  
    CREATE CREDENTIAL sa_ekm_tde_cred   
    WITH IDENTITY = 'Identity1',   
    SECRET = 'q*gtev$0u#D1v'   
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;  
    GO  
    
    -- Add the credential to a high privileged user such as your   
    -- own domain login in the format [DOMAIN\login].  
    ALTER LOGIN Contoso\Mary  
    ADD CREDENTIAL sa_ekm_tde_cred ;  
    GO  
    -- create an asymmetric key stored inside the EKM provider  
    USE master ;  
    GO  
    CREATE ASYMMETRIC KEY ekm_login_key   
    FROM PROVIDER [EKM_Prov]  
    WITH ALGORITHM = RSA_512,  
    PROVIDER_KEY_NAME = 'SQL_Server_Key' ;  
    GO  
    
    -- Create a credential that will be used by the Database Engine.  
    CREATE CREDENTIAL ekm_tde_cred   
    WITH IDENTITY = 'Identity2'   
    , SECRET = 'jeksi84&sLksi01@s'   
    FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;  
    
    -- Add a login used by TDE, and add the new credential to the login.  
    CREATE LOGIN EKM_Login   
    FROM ASYMMETRIC KEY ekm_login_key ;  
    GO  
    ALTER LOGIN EKM_Login   
    ADD CREDENTIAL ekm_tde_cred ;  
    GO  
    
    -- Create the database encryption key that will be used for TDE.  
    USE AdventureWorks2012 ;  
    GO  
    CREATE DATABASE ENCRYPTION KEY  
    WITH ALGORITHM  = AES_128  
    ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;  
    GO  
    
    -- Alter the database to enable transparent data encryption.  
    ALTER DATABASE AdventureWorks2012   
    SET ENCRYPTION ON ;  
    GO  
    

有关详细信息,请参见以下内容:For more information, see the following:

另请参阅See Also

借助 Azure SQL 数据库实现透明数据加密Transparent Data Encryption with Azure SQL Database