FIX: Problems occur in database backups from older versions of SQL Server Connector for Microsoft Azure Key Vault to version 1.0.5.0

S'aplica a: SQL Server 2014SQL Server 2016

Symptoms


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.

Cause


An update was introduced in version 1.0.5.0 of SQL Server Connector for Microsoft Azure Key Vault that changes the way that the program calculates thumbprints. In version 1.0.5.0, this calculation matches the logic that is 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 may experience problems when you try to restore database backups from version 1.0.4.0 or an earlier version.

Resolution


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

  1. Run the following query on the sql2 server to change the CRYPTOGRAPHIC PROVIDER to version 1.0.4.0.
  2. Create a new asymmetric key by using the 1.0.4.0 provider.

     

    You should now be able to restore the backup.

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

  4. 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.

More information


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

  • 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.

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_KEYFROM PROVIDER AzureKeyVaultProviderWITH 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.

Example:
  • You have a database backup that is encrypted by an asymmetric key in Azure Key Vault in the sql1 instance.
  • The sql2 instance has an asymmetric key created.

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

Status


Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. 

References


Learn about the terminology that Microsoft uses to describe software updates.