大网数据广告活动
数据库与应用安全

Can't restore backups from SQL Server that uses SQL Server Connector for Key Vault

· 52CE 我爱测

This article helps you resolve the error 33111 while restoring backups from the previous versions of SQL Server.

Original KB number:   4470999

This article discusses an error that occurs when you try to restore a Transparent Data Encryption (TDE) backup that's made on servers that use older versions of SQL Server Connector for the Microsoft Azure Key Vault.

Symptoms

You experience problems when you try to restore a database backup from SQL Server that uses SQL Server Connector for Key Vault 1.0.4.0 or an earlier version to SQL Server Connector for Microsoft Azure Key Vault 1.0.5.0.

Assume that you deploy the following instances of Microsoft SQL Server:

  • SQL Server instance sql1 has SQL Server Connector for Key Vault 1.0.4.0 deployed.

  • SQL Server instance sql2 has SQL Server Connector for Key Vault 1.0.5.0 deployed.

  • Use the following query to deploy an asymmetric key on both sql1 and sql2 instances from the same asymmetric key source in Key Vault:

    CREATE ASYMMETRIC KEY TDE_KEY 
    FROM PROVIDER AzureKeyVaultProvider 
    WITH PROVIDER_KEY_NAME = 'key1', 
    CREATION_DISPOSITION = OPEN_EXISTING
    

If you review the value of thumbprints on both servers, you will notice that the thumbprint lengths differ though they're created from the same source. The version 1.0.5.0 thumbprint is longer than version 1.0.4.0.

Example of 1.0.4.0 thumbprint:

0x2C5677D76F76D77F80

Example of 1.0.5.0 thumbprint:

0x373B314B78E8D59A0925494558FEF14B726216C5

The change causes problems to occur during backup and restore operations.

Example:

You have a database backup that is encrypted by an asymmetric key in Key Vault in the sql1 instance.

The sql2 instance has an asymmetric key created.

If you try to restore the backup on the sql2 instance, the operation fails and returns an error message that resembles the following message:

Msg 33111, Level 16, State 4, Line <LineNumber>

Cannot find server asymmetric key with thumbprint '0x2C5677D76F76D77F80'.

Notes:

The query to retrieve the thumbprint of each key is as follows:

SELECT thumbprint, * FROM master.sys.asymmetric_keys

The query to retrieve the thumbprint of each TDE database is as follows:

SELECT DatabaseName(ddek.database_id) AS DatabaseName, ak.name
AS [Asymmetric key Name], ak.thumbprint FROM
sys.dm_database_encryption_keys ddek INNER JOIN
master.sys.asymmetric_keys ak ON
ak.thumbprint = ddek.encryptor_thumbprint

Cause

An update was introduced in version 1.0.5.0 of SQL Server Connector for Key Vault that changes the way that the program calculates thumbprints. In version 1.0.5.0, this calculation matches the logic that's used by the program engine to support the following migration scenario:

From: On-premises Microsoft SQL Server that uses Extensible Key Management (EKM)

To: Microsoft Azure SQL Database that uses Bring Your Own Key (BYOK) support for Transparent Data Encryption (TDE)

Because of this change, you might experience problems when you try to restore database backups from version 1.0.4.0 or an earlier version.

Resolution

  1. Copy the SQL Server Connector for Key Vault 1.0.4.0 or an earlier version to the sql2 instance server.

  2. Run the following query on the sql2 server to change the CRYPTOGRAPHIC PROVIDER to version 1.0.4.0:

    ALTER CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider
    FROM FILE =
    'FilePath\FileName\SQL Server Connector for Microsoft Azure Key Vault\1.0.4.0\Microsoft.AzureKeyVaultService.EKM.dll'
    
    1. Restart SQL Server.

    2. Create a new asymmetric key by using CRYPTOGRAPHIC PROVIDER 1.0.4.0.

        CREATE ASYMMETRIC KEY TDE_KEY_1040 
        FROM PROVIDER AzureKeyVaultProvider 
        WITH PROVIDER_KEY_NAME = 'key1', 
        CREATION_DISPOSITION = OPEN_EXISTING
      
  3. You can confirm the existence of both the asymmetric keys by using the following query:

    SELECT thumbprint,* FROM master.sys.asymmetric_keys
    
  4. Add credentials to asymmetric-key mapped login (TDE_Login in the following example) by using a query similar to the following query:

    ALTER LOGIN [Contoso\DomainUser] DROP CREDENTIAL sysadmin_ekm_cred; 
    ALTER LOGIN TDE_Login ADD CREDENTIAL sysadmin_ekm_cred;
    
  5. You should now be able to restore the backup.

  6. Run the following query on sql2 to revert the CRYPTOGRAPHIC PROVIDER to version 1.0.5.0:

    ALTER CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider 
    FROM FILE =
    'FilePath\FileName\SQL Server Connector for Microsoft Azure Key Vault\1.0.5.0\Microsoft.AzureKeyVaultService.EKM.dll'
    
  7. Restart SQL Server.

  8. To use the new thumbprint, run the following query by using either the same asymmetric key or the new version asymmetric key.

    ALTER DATABASE ENCRYPTION KEY
    ENCRYPTION BY SERVER ASYMMETRIC KEY <KeyName_1.0.5.0_version>
    

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 10/11/2022
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。