Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.
View products that this article applies to.
You experience problems when you try to migrate a database backup from SQL Server Connector for Microsoft Azure Key Vault 1.0.4.0 or an earlier version to SQL Server Connector for Microsoft Azure Key Vault 1.0.5.0.
Copy the SQL Server Connector for Microsoft Azure Key Vault 1.0.4.0 or an earlier version to the sql2 instance server.
ALTER CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider
FROM FILE =
'FilePath\FileName\SQL Server Connector for Microsoft Azure Key Vault\1.0.4.0\Microsoft.AzureKeyVaultService.EKM.dll'
Create a new asymmetric key by using the 1.0.4.0 provider.
CREATE ASYMMETRIC KEY TDE_KEY_1040
FROM PROVIDER AzureKeyVaultProvider
WITH PROVIDER_KEY_NAME = 'key1',
CREATION_DISPOSITION = OPEN_EXISTING
You should now be able to restore the backup.
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'
To be able 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 KeyName1050Version
Assume that you deploy the following instances of Microsoft SQL Server:
The following query is used to create both the sql1 and sql2 instances to deploy an ASYMMETRIC key from the same ASYMMETRIC key in Azure Key Vault.
CREATE ASYMMETRIC KEY TDE_KEY FROM PROVIDER AzureKeyVaultProvider WITH PROVIDER_KEY_NAME = 'key1', CREATION_DISPOSITION = OPEN_EXISTING
Notice that the thumbprints lengths differ. The version 1.0.5.0 thumbprint is longer than the version 1.0.4.0 thumbprint.
Example of 1.0.4.0 thumbprint:
- 0x2C5677D76F76D77F80
Example of version 1.0.5.0 thumbprint:
- 0x373B314B78E8D59A0925494558FEF14B726216C5
Note Both asymmetric keys are created from same source.
The change causes problem to occur during backup and restore operations.
If you try to restore the backup of the sql2 instance, the restore fails and returns an error message that resembles the following:
Msg 33111, Level 16, State 4, Line LineNumber
Cannot find server asymmetric key with thumbprint '0x2C5677D76F76D77F80'.
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
Keywords: kbHotfixAuto, CI93122, Database security (object, row, TDE, Always Encrypted, data masking), KbSupportTopic