Exporting and Importing SQL Server Always Encrypted Certificates for Client Access

By:   |   Comments (5)   |   Related: > Encryption


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.


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



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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, December 23, 2020 - 6:16:33 PM - David Chase Back To Top (87945)
What do you do if the web (iis) server and sql server are the same machine?

Wednesday, March 13, 2019 - 7:07:10 AM - Anuj Soni Back To Top (79280)


My ask is if we import certificate on IIS server to view encrypt and decrypt data then it will allow any users to encrypt and decrypt data who has access to that site. 

Isn't it install on client machine only to encrypt and decrypt data.

Like if we have certificate installed in our machine and if we have enabled Column Encryption Setting in SSMS then and then only we can encrypt and decrypt data otherwise we can not. 

Such way it should not work with individual client.

Any help is appreciated.

Thank you.


Anuj Soni.

Friday, January 4, 2019 - 1:16:34 AM - My Filter Back To Top (78621)

"Depending on your requirements, you may want to remove the key later", how to do this?

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

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,


Monday, October 2, 2017 - 9:40:06 AM - Ganesh Back To Top (66792)

 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.

get free sql tips
agree to terms