August 9, 2016 at 11:41 am
Hi,
We have encrypted a SQL Server 2014 database with an HSM successfully.
The asymmetric key has been created with the following statement :
USE master
GO
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_AsymKey_TDE
FROM PROVIDER SQL_EKM_Provider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Asym_TDE', ALGORITHM = RSA_2048, CREATION_DISPOSITION = CREATE_NEW
GO
Below is the statement we ran to create the database encryption key :
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_AsymKey_TDE ;
GO
On the target instance, we retrieve the asymmetric key from the HSM, using the following instruction :
CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_AsymKey_TDE
FROM PROVIDER SQL_EKM_Provider
WITH PROVIDER_KEY_NAME = 'EKM_RSA_2048_Asym_TDE', CREATION_DISPOSITION = OPEN_EXISTING
GO
Now we are willing to restore that database on a distinct instance; both are SQL Server 2014 with the exact same build number : 12.0.4213.0
The RESTORE DATABASE instruction fails with the following message :
Msg 33111, Level 16, State 4, Line 2
Cannot find server asymmetric key with thumbprint '0xCBC582F206A84C4B78F9A966B6E77F5BEDB26D66'.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
And sure enough, when looking into sys.asymmetric_keys, on both instances, the thumbprints are not matching.
Have you encountered this issue, and if so, could you share how you solved it ?
ElSüket.
August 10, 2016 at 4:18 am
We have found the root cause of this issue : we actually how been mislead by the documentation of the HSM.
It mentions the need to add an environment variable when in a cluster configuration, and we are in a standalone one, so we skipped that part.
However to setup a cluster for SQL Server, we could have thought earlier that we need to restore the database on all the participating nodes.
Therefore the need to add this environment variable.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply