https://support.microsoft.com/en-us/help/4470999/db-backup-problems-to-sql-server-connector-for-azure-1-0-5-0
A breaking change has been introduced in Version 1.0.5.0 of 'SQL Server Connector for Microsoft Azure Key Vault' . The 1.0.5.0 version updated the way it calculates thumbprints to match the logic used in the engine, in order to support the migration scenario of On Prem SQL Server using EKM to Azure SQL DB using TDE BYOK. Because of this change, customer may hit issues when they are trying to restore database backups from when they were using 1.0.4.0 or old version to a server using 1.0.5.0 version.
Here is an example
===
SQL Server instance sql1 has 'SQL Server Connector for Microsoft Azure Key Vault' 1.0.4.0 deployed. SQL Server instance sql2 has 'SQL Server Connector for Microsoft Azure Key Vault' 1.0.5.0 deployed. Both instance sql1 and sql2 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
The thumbprints are different. The length of thumbprint of 1.0.5.0 version is little bit greater than the thumbprint of 1.0.4.0 version.
Here is example of thumbprint of 1.0.4.0 version
0x2C5677D76F76D77F80
Here is example of thumbprint of 1.0.5.0 version. Please note, both asymmetric keys are created from same source.
0x373B314B78E8D59A0925494558FEF14B726216C5
This breaking change causes problem in backup/restore scenario.
For example, if you have a backup of a database encrypted by an asymmetric key in Azure Key vault in instance sql1.
The instance sql2 has same asymmetric key created.
If you try to restore the backup to instance sql2, the restore fails with the error message below
Msg 33111, Level 16, State 4, Line 20
Cannot find server asymmetric key with thumbprint '0x2C5677D76F76D77F80'.
Here is the query to retrieve the thumbprint of each key.
select thumbprint,* from master.sys.asymmetric_keys
Here is the query to retrieve the thumbprint of each TDE database
select db_name(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
Solution
===
Copy the 'SQL Server Connector for Microsoft Azure Key Vault' 1.0.4.0 or related version to the instance sql2 box.
Run the query below in sql2 to change the CRYPTOGRAPHIC PROVIDER to 1.0.4.0 version.
ALTER CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider
FROM FILE =
'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\1.0.4.0\Microsoft.AzureKeyVaultService.EKM.dll'
Create a new asymmetric key 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
After that, you are able to restore the backup.
Run the query below in sql2 to revert the CRYPTOGRAPHIC PROVIDER to 1.0.5.0 version.
ALTER CRYPTOGRAPHIC PROVIDER AzureKeyVaultProvider
FROM FILE =
'C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault\1.0.5.0\Microsoft.AzureKeyVaultService.EKM.dll'
You can either use the same asymmetric key or change to a new version asymmetric key to use the new thumbprint.
alter DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY KeyName1050Version