How to move MSSQL_CERTIFICATE_STORE from a machine to other machine?

Question

I am using the SQL Server "Always Encrypted" feature.

I open SSMS from my local machine, create a column master key (MyCMK1), then create a column encryption key (MyCEK1).

On SSMS, I use the MyCEK1 to encrypt the SSN column in the User table. Good.

I create a .NET console app + ADO.NET to test the SQL Server "Always Encrypted" feature (Column Encryption Setting=Enabled; added into the database connection string).

The result is good, I got an unencrypted SSN from ADO.NET result.

Next, I move my console app (MyConsoleApp.exe) to other machine, then run the app -> ERROR:

Failed to decrypt column 'SSN'. Failed to decrypt a column encryption key using key store provider: 'MSSQL_CERTIFICATE_STORE'. The last 10 bytes of the encrypted column encryption key are:
'3B-60-3A-40-AF-16-22-6F-ED-DE'. Certificate with thumbprint
'93DE41DC5F46FE6FF1436829B9362A508BB3E920' not found in certificate store 'My' in certificate location 'CurrentUser'. Verify the certificate path in the column master key definition in the database is correct, and the certificate has been imported correctly into the certificate location/store. Parameter name: masterKeyPath

The error makes sense since I use MSSQL_CERTIFICATE_STORE store type and the app works on my local machine because there is a certificate store file to store the master key at the below location:

CurrentUser/my/93DE41DC5F46FE6FF1436829B9362A508BB3E920

OR

file:\%APPDATA%\Microsoft\SystemCertificates\My\Certificates\93DE41DC5F46FE6FF1436829B9362A508BB3E920

My question is: how can I move the certificate store file that stores the master key from my local machine to the other machine?

Notes: I open CertMgr on my local machine / Personal / Certificates -> Empty.

user_12211157 · Answer

This can all be done utilizing your Certificate Manager.

Firstly, on the machine with the certificate you want to export, load up your Certificate Manager (certmgr). Locate your Always Encrypted certificate under Personal -> Certificates, right click on it, and click export. This will open up an Export Wizard which should be pretty self-explanatory.

Next, on the machine you wish to import your certificate on, again load up the Certificate Manager, navigate to Personal -> Certificates, right click in the empty space, and click import. Again, this will open up a wizard which should be pretty self explanatory.

Once you've done these two steps, it should work again.

user_2609525 · Answer

Go to the your Certificate Manager. Use the search certificate by key in your KeyIdentifier "93DE41DC5F46FE6FF1436829B9362A508BB3E920" to find your certificate. Then, you can export the certificate and import this exported certificate to your client machine.

If you double click the certificate, on the details, you will see this KeyIdentifier is the thumbprint.

How to move MSSQL_CERTIFICATE_STORE from a machine to other machine?

2 Answers