Problem
Setting permissions and updating connection strings are common tasks familiar to most SQL Server Database Administrators (DBAs). The exporting and importing of certificates is not as familiar, and that is what we are going to look at in this tip.
Solution
Let’s look at an example of data that has been encrypted previously. I have enabled Always Encrypted for the NationalIDNumber column as shown here:
Only the ciphertext is returned for the NationalIdNumber column. In order to decrypt this data I need the following three things to be true:
- Necessary permissions to select the data
- A modified connection string
- The certificate created on the database server
Exporting the Certificate
After data has been encrypted using Always Encrypted, a certificate is created on the database server. You will need to export this certificate, then import the certificate to the client machine(s) that require access to work with the encrypted data.
To view the list of certificates that are created on the database server you can execute certmgr from a command line:
This will launch the certmgr MMC, here’s what it looks like on my VM I am using for this post:
As with the example linked above, the Always Encrypted certificate was created as the current user, and it can be found in the Personal folder. You can also do a search for “Always Encrypted” to locate the certificate(s) created on the database server.
To export the certificate we will do a right-click, select “All Tasks”, then export:
This launches the Certificate Export Wizard:
We will click next, and you arrive at the most important screen in the export process:
You must select the “Yes, export the private key” option. If you do not select this option, then the certificate will be worthless upon import. We select the option, then click Next, and we have more options:
I choose the “Include all certificates in the certification path if possible” and “Export all extended properties” options. I will not delete the private key at this time. Depending on your requirements, you may want to remove the key later, but I would advocate that you verify the import works correctly before removing the private key. Otherwise you are going to be stuck with encrypted data and you will need to recover from backups and start the encryption process again.
We click Next and will be prompted to set a password:
We choose a strong password then click Next:
We choose a path and filename, then click Next:
Review the details and click Finish. You should then see this:
Now that we have exported the certificate, let’s try to do the import to a client machine and verify that we can view the decrypted data. We start the import process by opening certmgr again.
Importing the Certificate
Navigate to the certificates folder in the personal store, and right click to start the import process:
This will launch the Certificate Import Wizard, and you can see that the current user is already chosen:
We will click next, and navigate to the file we exported:
We click next, and enter the strong password used in the export process:
I chose to include the extended properties, but nothing else. Note that I am not allowing this key to be exportable. More on that later.
We now click next:
The Personal store is already chosen, that’s the option we are using in this example, so we click Next:
Review the details, then click Finish, and we should see:
Next, we want to verify the import is working as expected.
Verify import was successful
Remember I stated that 3 things are needed to work with the encrypted data. The permissions are already in place, so we will next update our connection string. We can do that inside of SSMS, in the advanced properties for the connection. We want to add in the string “Column Encryption Setting = Enabled”, like this:
Once we update our connection to be enabled for encryption, we can run our query again and view the NationalIDNumber column:
Success!
Summary
Exporting certificates for is straightforward provided you know the steps involved. There are two things to note here. First, the imported key in the example here is not able to be exported (remember I said more on this later? Now is later). That means the client won’t be able to pass this key around. That is a good thing. Second, if you remove the certificate from the database server, you will want to keep an extra copy of the certificate somewhere. Otherwise you run the risk of losing your keys, forcing you to recover data from backups prior to the encryption being set. Something to keep in mind as you review your security requirements. I recommend that you export the key, put it in a safe location, use that key for future export/import activities, and remove the certificate form the database server as an extra security measure.
Next Steps
- Download the latest version of SQL Server.
- Review the steps to encrypt data using Always Encrypted.
- All Encryption Tips
- All Security Tips
