Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Exporting and Importing SQL Server Always Encrypted Certificates for Client Access


By:   |   Read Comments (2)   |   Related Tips: More > Security

Attend these FREE SQL Server 2017 webcasts >> click to register


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:

SQL Server Management Studio Always Encrypted

Only the ciphertext is returned for the NationalIdNumber column. In order to decrypt this data I need the following three things to be true:

  1. Necessary permissions to select the data
  2. A modified connection string
  3. 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:

Certificate Manager

This will launch the certmgr MMC, here's what it looks like on my VM I am using for this post:

Certificate Manager MMC

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:

Certificate Manager Export

This launches the Certificate Export Wizard:

Certificate Manager Export Wizard Splash

We will click next, and you arrive at the most important screen in the export process:

Certificate Manager Export Wizard private key

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:

Certificate Manager Export Wizard file format 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:

Certificate Manager Export Wizard file password

We choose a strong password then click Next:

Certificate Manager Export Wizard file name

We choose a path and filename, then click Next:

Certificate Manager Export Wizard Finish

Review the details and click Finish. You should then see this:

Certificate Manager Export Wizard Success

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:

Certificate Manager Import

This will launch the Certificate Import Wizard, and you can see that the current user is already chosen:

Certificate Manager Import Wizard

We will click next, and navigate to the file we exported:

Certificate Manager Import File

We click next, and enter the strong password used in the export process:

Certificate Manager Import File password

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:

Certificate Manager Import certificate store

The Personal store is already chosen, that's the option we are using in this example, so we click Next:

Certificate Manager Import Finish

Review the details, then click Finish, and we should see:

Certificate Manager Import Success

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:

SQL Server Management Studio Always Encrypted Connection String

Once we update our connection to be enabled for encryption, we can run our query again and view the NationalIDNumber column:

SQL Server Management Studio Always Encrypted decrypted data

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


Last Update:


signup button

next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, October 17, 2017 - 4:57:17 AM - Thomas Back To Top

Hi Ganesh

You need to import the certificate to the IIS server in order for the web app to be able to handle decryption. Further, web app should be .Net 4.6+, and you must include the Column Encryption Setting=Enabled; in your connection string. 

Best regards,

Thomas


Monday, October 02, 2017 - 9:40:06 AM - Ganesh Back To Top

 Hi, Thanks for the above post its pretty straight forward and working perfectly. We have one concern - When we trying to access the encrypted coluns from Asp.net Web application  from IIS server its not working and throwing errors as

1. "The wait operation timed out" This occur when we try to pull list of encrypted column data

2. Certificate with thumbprint '817862855998E2C88AE37E841B8137EC7ED6FB7E' 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

can you please suggest solution if any.


Learn more about SQL Server tools