使用具有 SQL 加密功能的 SQL Server 连接器Use SQL Server Connector with SQL Encryption Features

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

使用由 Azure 密钥保管库保护的非对称密钥的常见 SQL ServerSQL Server 加密活动包括以下三个方面。Common SQL ServerSQL Server encryption activities using an asymmetric key protected by the Azure Key Vault include the following three areas.

  • 使用 Azure 密钥保管库的非对称密钥实现透明数据加密Transparent Data Encryption by using an Asymmetric Key from Azure Key Vault

  • 通过使用 Key Vault 的非对称密钥加密备份文件Encrypting Backups by Using an Asymmetric Key from the Key Vault

  • 通过使用 Key Vault 的非对称密钥实现列级加密Column Level Encryption by Using an Asymmetric Key from the Key Vault

请先完成主题 Setup Steps for Extensible Key Management Using the Azure Key Vault(使用 Azure 密钥保管库的可扩展密钥管理的设置步骤)的第 I 到 IV 部分,然后执行该主题中的步骤。Complete parts I through IV of the topic Setup Steps for Extensible Key Management Using the Azure Key Vault, before following the steps on this topic.

备注

已替换版本 1.0.0.440 和更早的版本,且生产环境不再支持这些版本。Versions 1.0.0.440 and older have been replaced and are no longer supported in production environments. 要升级至版本 1.0.1.0 或更高版本,请访问 Microsoft 下载中心 ,并参照“升级 SQL Server 连接器”下 SQL Server 连接器维护与故障排除页面上的指南。Upgrade to version 1.0.1.0 or later by visiting the Microsoft Download Center and using the instructions on the SQL Server Connector Maintenance & Troubleshooting page under "Upgrade of SQL Server Connector."

使用 Azure 密钥保管库的非对称密钥实现透明数据加密Transparent Data Encryption by using an Asymmetric Key from Azure Key Vault

完成主题“Setup Steps for Extensible Key Management Using the Azure Key Vault(使用 Azure 密钥保管库的可扩展密钥管理的设置步骤)”的第 I 到 IV 部分之后,使用 Azure 密钥保管库密钥来加密使用 TDE 的数据库加密密钥。After completing Parts I through IV of the topic Setup Steps for Extensible Key Management Using the Azure Key Vault, use the Azure Key Vault key to encrypt the database encryption key using TDE. 有关使用 PowerShell 轮换密钥的详细信息,请参阅使用 PowerShell 轮换透明数据加密 (TDE) 保护程序For more information about rotating keys using PowerShell, see Rotate the Transparent Data Encryption (TDE) protector using PowerShell.

你需要创建一个凭据和登录名,以及创建一个可以对数据库中的数据和日志进行加密的数据库加密密钥。You will need to create a credential and a login, and create a database encryption key which will encrypt the data and logs in the database. 若要对数据库进行加密,需要有数据库的 CONTROL 权限。To encrypt a database requires CONTROL permission on the database. 下图显示了使用 Azure 密钥保管库时的加密密钥的层次结构。The following graphic shows the hierarchy of the encryption key when using the Azure Key Vault.

ekm-key-hierarchy-with-akvekm-key-hierarchy-with-akv

  1. 创建要用于 TDE 的数据库引擎的 SQL ServerSQL Server 凭据Create a SQL ServerSQL Server credential for the Database Engine to use for TDE

    在数据库加载期间数据库引擎使用凭据来访问密钥保管库。The Database Engine uses the credential to access the Key Vault during database load. 我们建议在第 I 部分为 创建另一个 Azure Active Directory 客户端 ID 密码 数据库引擎Database Engine,以限制所授予的密钥保管库权限。We recommend creating another Azure Active Directory Client ID and Secret in Part I for the 数据库引擎Database Engine, to limit the Key Vault permissions that are granted.

    采用以下方式修改下面的 Transact-SQLTransact-SQL 脚本:Modify the Transact-SQLTransact-SQL script below in the following ways:

    • 编辑 IDENTITY 参数 (ContosoDevKeyVault) 以指向 Azure 密钥保管库。Edit the IDENTITY argument (ContosoDevKeyVault) to point to your Azure Key Vault.

      • 如果使用“全局 Azure”,请将 IDENTITY 参数替换为第 II 部分中的 Azure Key Vault 的名称 。If you're using global Azure, replace the IDENTITY argument with the name of your Azure Key Vault from Part II.
      • 如果使用 Azure 私有云 (例如,If you're using a private Azure cloud (ex. Azure 政府、中国区 Azure 世纪互联或 Azure 德国),请将 IDENTITY 参数替换为第 II 部分的步骤 3 中返回的保管库 URI。Azure Government, Azure China 21Vianet, or Azure Germany), replace the IDENTITY argument with the Vault URI that is returned in Part II, step 3. 保管库 URI 中不能包含 “https://” 。Do not include "https://" in the Vault URI.
    • SECRET 参数的第一部分替换为第 I 部分中的 Azure Active Directory 客户端 ID 。在此示例中, 客户端 IDEF5C8E094D2A4A769998D93440D8115DReplace the first part of the SECRET argument with the Azure Active Directory Client ID from Part I. In this example, the Client ID is EF5C8E094D2A4A769998D93440D8115D.

      重要

      必须删除 客户端 ID中的连字符。You must remove the hyphens from the Client ID.

    • 使用第 I 部分的 SECRET 客户端密码 完成 参数的第二部分。在此示例中,第 I 部分的 客户端密码Replace-With-AAD-Client-SecretComplete the second part of the SECRET argument with Client Secret from Part I. In this example the Client Secret from Part 1 is Replace-With-AAD-Client-Secret. SECRET 参数的最终字符串是一长串 不带连字符的字母和数字。The final string for the SECRET argument will be a long sequence of letters and numbers, with no hyphens.

    USE master;  
    CREATE CREDENTIAL Azure_EKM_TDE_cred   
        WITH IDENTITY = 'ContosoDevKeyVault', -- for global Azure
        -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government
        -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China 21Vianet
        -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany   
        SECRET = 'EF5C8E094D2A4A769998D93440D8115DReplace-With-AAD-Client-Secret'   
    FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;  
    
  2. 为 TDE 创建 SQL ServerSQL Server数据库引擎Database Engine 登录名Create a SQL ServerSQL Server login for the 数据库引擎Database Engine for TDE

    创建 SQL ServerSQL Server 登录名并向其添加步骤 1 中的凭据。Create a SQL ServerSQL Server login and add the credential from Step 1 to it. Transact-SQLTransact-SQL 示例使用之前导入的密钥。This Transact-SQLTransact-SQL example uses the same key that was imported earlier.

    USE master;  
    -- Create a SQL Server login associated with the asymmetric key   
    -- for the Database engine to use when it loads a database   
    -- encrypted by TDE.  
    CREATE LOGIN TDE_Login   
    FROM ASYMMETRIC KEY CONTOSO_KEY;  
    GO   
    
    -- Alter the TDE Login to add the credential for use by the   
    -- Database Engine to access the key vault  
    ALTER LOGIN TDE_Login   
    ADD CREDENTIAL Azure_EKM_TDE_cred ;  
    GO  
    
  3. 创建数据库加密密钥 (DEK)Create the Database Encryption Key (DEK)

    DEK 将对数据库实例中的数据和日志文件进行加密,并且反过来被 Azure 密钥保管库的非对称密钥加密。The DEK will encrypt your data and log files in the database instance, and in turn be encrypted by the Azure Key Vault asymmetric key. 可使用任何 SQL ServerSQL Server 支持的算法或密钥长度来创建 DEK。The DEK can be created using any SQL ServerSQL Server supported algorithm or key length.

    USE ContosoDatabase;  
    GO  
    
    CREATE DATABASE ENCRYPTION KEY   
    WITH ALGORITHM = AES_256   
    ENCRYPTION BY SERVER ASYMMETRIC KEY CONTOSO_KEY;  
    GO  
    
  4. 启用 TDETurn On TDE

    -- Alter the database to enable transparent data encryption.  
    ALTER DATABASE ContosoDatabase   
    SET ENCRYPTION ON;  
    GO  
    

    使用 Management StudioManagement Studio,通过对象资源管理器连接到数据库来确认是否已启用 TDE。Using Management StudioManagement Studio, verify that TDE has been turned on by connecting to your database with Object Explorer. 右键单击数据库,指向 “任务”,然后单击 “管理数据库加密”。Right-click your database, point to Tasks, and then click Manage Database Encryption.

    ekm-tde-object-explorerekm-tde-object-explorer

    在“管理数据库加密” 对话框中,确认 TDE 处于打开状态,以及使用哪个非对称密钥对 DEK 进行加密。In the Manage Database Encryption dialog box, confirm that TDE is on, and what asymmetric key is encrypting the DEK.

    ekm-tde-dialog-boxekm-tde-dialog-box

    或者,你可以执行以下 Transact-SQLTransact-SQL 脚本。Alternatively, you can execute the following Transact-SQLTransact-SQL script. 加密状态 3 表示已加密的数据库。An encryption state of 3 indicates an encrypted database.

    USE MASTER  
    SELECT * FROM sys.asymmetric_keys  
    
    -- Check which databases are encrypted using TDE  
    SELECT d.name, dek.encryption_state   
    FROM sys.dm_database_encryption_keys AS dek  
    JOIN sys.databases AS d  
         ON dek.database_id = d.database_id;  
    

    备注

    任何数据库只要启用 TDE 就会自动加密 tempdb 数据库。The tempdb database is automatically encrypted whenever any database enables TDE.

通过使用 Key Vault 的非对称密钥加密备份文件Encrypting Backups by Using an Asymmetric Key from the Key Vault

SQL Server 2014 (12.x)SQL Server 2014 (12.x)开始支持加密备份。Encrypted backups are supported starting with SQL Server 2014 (12.x)SQL Server 2014 (12.x). 以下示例创建并还原了经过数据加密密钥加密的备份文件,其中该加密密钥受到 key vault 中的非加密密钥保护。The following example creates and restores a backup encrypted a data encryption key protected by the asymmetric key in the key vault.
在数据库加载期间 数据库引擎Database Engine 需要凭据来访问密钥保管库。The 数据库引擎Database Engine needs the credential when accessing the Key Vault during database load. 我们建议在第 I 部分为数据库引擎创建另一个 Azure Active Directory 客户端 ID 和密码,以限制所授予的密钥保管库权限。We recommend creating another Azure Active Directory Client ID and Secret in Part I for the Database Engine, to limit the Key Vault permissions that are granted.

  1. 创建要用于备份加密的数据库引擎的 SQL Server 凭据Create a SQL Server credential for the Database Engine to use for Backup Encryption

    采用以下方式修改下面的 Transact-SQLTransact-SQL 脚本:Modify the Transact-SQLTransact-SQL script below in the following ways:

    • 编辑 IDENTITY 参数 (ContosoDevKeyVault) 以指向 Azure 密钥保管库。Edit the IDENTITY argument (ContosoDevKeyVault) to point to your Azure Key Vault.

      • 如果使用“全局 Azure”,请将 IDENTITY 参数替换为第 II 部分中的 Azure Key Vault 的名称 。If you're using global Azure, replace the IDENTITY argument with the name of your Azure Key Vault from Part II.
      • 如果使用 Azure 私有云 (例如,If you're using a private Azure cloud (ex. Azure 政府、中国区 Azure 世纪互联或 Azure 德国),请将 IDENTITY 参数替换为第 II 部分的步骤 3 中返回的保管库 URI。Azure Government, Azure China 21Vianet, or Azure Germany), replace the IDENTITY argument with the Vault URI that is returned in Part II, step 3. 保管库 URI 中不能包含 “https://” 。Do not include "https://" in the Vault URI.
    • SECRET 参数的第一部分替换为第 I 部分中的 Azure Active Directory 客户端 ID 。在此示例中, 客户端 IDEF5C8E094D2A4A769998D93440D8115DReplace the first part of the SECRET argument with the Azure Active Directory Client ID from Part I. In this example, the Client ID is EF5C8E094D2A4A769998D93440D8115D.

      重要

      必须删除 客户端 ID中的连字符。You must remove the hyphens from the Client ID.

    • 使用第 I 部分的 SECRET 客户端密码 完成 参数的第二部分。在此示例中,第 I 部分的 客户端密码Replace-With-AAD-Client-SecretComplete the second part of the SECRET argument with Client Secret from Part I. In this example the Client Secret from Part I is Replace-With-AAD-Client-Secret. SECRET 参数的最终字符串是一长串 不带连字符的字母和数字。The final string for the SECRET argument will be a long sequence of letters and numbers, with no hyphens.

      USE master;  
      
      CREATE CREDENTIAL Azure_EKM_Backup_cred   
          WITH IDENTITY = 'ContosoDevKeyVault', -- for global Azure
          -- WITH IDENTITY = 'ContosoDevKeyVault.vault.usgovcloudapi.net', -- for Azure Government
          -- WITH IDENTITY = 'ContosoDevKeyVault.vault.azure.cn', -- for Azure China 21Vianet
          -- WITH IDENTITY = 'ContosoDevKeyVault.vault.microsoftazure.de', -- for Azure Germany   
          SECRET = 'EF5C8E094D2A4A769998D93440D8115DReplace-With-AAD-Client-Secret'   
      FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov;    
      
  2. 为备份加密创建 SQL ServerSQL Server数据库引擎Database Engine 登录名Create a SQL ServerSQL Server login for the 数据库引擎Database Engine for Backup Encryption

    为备份加密创建 SQL ServerSQL Server 所使用的 数据库引擎Database Engine登录名,并向其添加步骤 1 中的凭据。Create a SQL ServerSQL Server login to be used by the 数据库引擎Database Enginee for encryption backups, and add the credential from Step 1 to it. Transact-SQLTransact-SQL 示例使用之前导入的密钥。This Transact-SQLTransact-SQL example uses the same key that was imported earlier.

    重要

    如果你已将该非对称密钥用于 TDE (以上示例) 或列级加密 (以下示例),则不能将此同一个密钥用于备份加密。You cannot use the same asymmetric key for backup encryption if you've already used that key for TDE (the above example) or column level Encryption (the following example).

    此示例使用存储在密钥保管库中的 CONTOSO_KEY_BACKUP 非对称密钥,该密钥可以是之前为 master 数据库导入或创建的,如前面的第 IV 部分第 5 步所述。This example uses the CONTOSO_KEY_BACKUP asymmetric key stored in the key vault, which can be imported or created earlier for the master database, as Part IV, Step 5 earlier.

    USE master;  
    
    -- Create a SQL Server login associated with the asymmetric key   
    -- for the Database engine to use when it is encrypting the backup.  
    CREATE LOGIN Backup_Login   
    FROM ASYMMETRIC KEY CONTOSO_KEY_BACKUP;  
    GO   
    
    -- Alter the Encrypted Backup Login to add the credential for use by   
    -- the Database Engine to access the key vault  
    ALTER LOGIN Backup_Login   
    ADD CREDENTIAL Azure_EKM_Backup_cred ;  
    GO  
    
  3. 备份数据库Backup the Database

    备份数据库,并使用密钥保管库中存储的非对称密钥指定加密。Backup the database specifying encryption with the asymmetric key stored in the key vault.

    请注意,在下面的示例中,如果数据库已使用 TDE 加密,且非对称密钥 CONTOSO_KEY_BACKUP 不同于 TDE 非对称密钥,则会同时通过 TDE 非对称密钥和 CONTOSO_KEY_BACKUP 加密备份。In the below example, note that if the database was already encrypted with TDE, and the asymmetric key CONTOSO_KEY_BACKUP is different from the TDE asymmetric key, the backup will be encrypted by both the TDE asymmetric key and CONTOSO_KEY_BACKUP. 目标 SQL ServerSQL Server 实例将需要两个密钥才能解密备份。The target SQL ServerSQL Server instance will need both keys in order to decrypt the backup.

    USE master;  
    
    BACKUP DATABASE [DATABASE_TO_BACKUP]  
    TO DISK = N'[PATH TO BACKUP FILE]'   
    WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD,   
    ENCRYPTION(ALGORITHM = AES_256,   
    SERVER ASYMMETRIC KEY = [CONTOSO_KEY_BACKUP]);  
    GO  
    
  4. 还原数据库Restore the Database

    若要还原使用 TDE 加密的数据库备份,目标 SQL ServerSQL Server 实例必须先对用于加密的非对称 Key Vault 密钥进行复制。To restore a database backup that is encrypted with TDE, the target SQL ServerSQL Server instance must first have a copy of the asymmetric Key Vault key used for encryption. 为此,可执行如下操作:This is how this would be achieved:

    • 如果用于 TDE 的原始非对称密钥不再位于 Key Vault 中,请还原 Key Vault 密钥备份,或者从本地 HSM 重新导入该密钥。If the original asymmetric key used for TDE is no longer in Key Vault, restore the Key Vault key backup or reimport the key from a local HSM. 重要提示: 为了让密钥的指纹与数据库备份中记录的指纹匹配,密钥的名称与以前的原始名称必须为同一 Key Vault 密钥名称 。Important: In order to have the key's thumbprint match the thumbprint recorded on the database backup, the key must be named the same Key Vault key name as it was originally named before.

    • 对目标 SQL ServerSQL Server 实例应用步骤 1 和 2 的操作。Apply Steps 1 and 2 on the target SQL ServerSQL Server instance.

    • 目标 SQL ServerSQL Server 实例能够访问用于加密备份的非对称密钥以后,即可在服务器上还原数据库。Once the target SQL ServerSQL Server instance has access to the asymmetric key(s) used to encrypt the backup, restore the database on the server.

    示例还原代码:Sample restore code:

    RESTORE DATABASE [DATABASE_TO_BACKUP]  
    FROM DISK = N'[PATH TO BACKUP FILE]'   
        WITH FILE = 1, NOUNLOAD, REPLACE;  
    GO  
    

    有关备份选项的详细信息,请参阅 BACKUP (Transact-SQL)For more information about backup options, see BACKUP (Transact-SQL).

通过使用 Key Vault 的非对称密钥实现列级加密Column Level Encryption by Using an Asymmetric Key from the Key Vault

以下示例创建了受 key vault 中非对称密钥保护的对称密钥。The following example creates a symmetric key protected by the asymmetric key in the key vault. 然后该对称密钥用于对数据库中的数据进行加密。Then the symmetric key is used to encrypt data in the database.

重要

如果已将非对称密钥用于 TDE 或备份加密(以上示例),则不能将此同一个密钥用于备份加密。You cannot use the same asymmetric key for backup encryption if you've already used that key for TDE or backup encryption (the preceding examples).

此示例使用存储在密钥保管库中的 CONTOSO_KEY_COLUMNS 非对称密钥,该密钥可能是以前导入或创建的,如 Setup Steps for Extensible Key Management Using the Azure Key Vault(使用 Azure 密钥保管库的可扩展密钥管理的设置步骤)的第 3 部分的步骤 3 所述。This example uses the CONTOSO_KEY_COLUMNS asymmetric key stored in the key vault, which can be imported or created earlier, as described in Step 3, section 3 of Setup Steps for Extensible Key Management Using the Azure Key Vault. 若要在 ContosoDatabase 数据库中使用此非对称密钥,必须再次执行 CREATE ASYMMETRIC KEY 语句,以便为 ContosoDatabase 数据库提供对该密钥的引用。To use this asymmetric key in the ContosoDatabase database, you must execute the CREATE ASYMMETRIC KEY statement again, to provide the ContosoDatabase database with a reference to the key.

USE [ContosoDatabase];  
GO  
  
-- Create a reference to the key in the key vault  
CREATE ASYMMETRIC KEY CONTOSO_KEY_COLUMNS   
FROM PROVIDER [AzureKeyVault_EKM_Prov]  
WITH PROVIDER_KEY_NAME = 'ContosoDevRSAKey2',  
CREATION_DISPOSITION = OPEN_EXISTING;  
  
-- Create the data encryption key.  
-- The data encryption key can be created using any SQL Server   
-- supported algorithm or key length.  
-- The DEK will be protected by the asymmetric key in the key vault  
  
CREATE SYMMETRIC KEY DATA_ENCRYPTION_KEY  
    WITH ALGORITHM=AES_256  
    ENCRYPTION BY ASYMMETRIC KEY CONTOSO_KEY_COLUMNS;  
  
DECLARE @DATA VARBINARY(MAX);  
  
--Open the symmetric key for use in this session  
OPEN SYMMETRIC KEY DATA_ENCRYPTION_KEY   
DECRYPTION BY ASYMMETRIC KEY CONTOSO_KEY_COLUMNS;  
  
--Encrypt syntax  
SELECT @DATA = ENCRYPTBYKEY  
    (  
    KEY_GUID('DATA_ENCRYPTION_KEY'),   
    CONVERT(VARBINARY,'Plain text data to encrypt')  
    );  
  
-- Decrypt syntax  
SELECT CONVERT(VARCHAR, DECRYPTBYKEY(@DATA));  
  
--Close the symmetric key  
CLOSE SYMMETRIC KEY DATA_ENCRYPTION_KEY;  

另请参阅See Also

Setup Steps for Extensible Key Management Using the Azure Key Vault(使用 Azure 密钥保管库的可扩展密钥管理的设置步骤) Setup Steps for Extensible Key Management Using the Azure Key Vault
使用 Azure Key Vault 的可扩展密钥管理Extensible Key Management Using Azure Key Vault
EKM provider enabled 服务器配置选项 EKM provider enabled Server Configuration Option
SQL Server 连接器维护与故障排除SQL Server Connector Maintenance & Troubleshooting