Always Encypted Certificates

Question

Always Encypted Certificates

David Chase 681

I just setup Always Encrypted on our SQL Server 2016 Std server on a test database. The master key and encryption key were created as CMK_Auto1 and CEK_Auto1. I chose "Windows certificate store" and "Master key source" of Current User. I searched for where they are stored and I found HKEY_CURRENT_USER in the registry but I cannot find anything there.

Also, when we encrypt the production server how can I restore the production database to my development database without having those certificates on my development database?

AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2020-10-28T06:24:09.983+00:00

Hi @David Chase ,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer. This would benefit the community, and encourage the community member to keep working on your issues.
Best Regards,
Amelia

Accepted answer

2 additional answers

Your answer

AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff

2020-10-28T06:24:09.983+00:00

Hi @David Chase ,
Did the answers help you?
Please feel free to let us know if you have any other question.
If you find any post in the thread is helpful, you could kindly accept it as answer. This would benefit the community, and encourage the community member to keep working on your issues.
Best Regards,
Amelia

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

Also, when we encrypt the production server how can I restore the production database to my development database without having those certificates on my development database?

I meant to answer this question last night, but something must have happened. It seems that Amelia has answered the first half of your post, but I don't think her answer to your second question is what you are looking for.

There is no problem with copying the production database elsewhere. In difference to TDE there are no keys you need to worry about. As long as you are connecting from a client that has all the certificates you can retrieve the data. Keep in mind that all decryption/encryption occurs client-side. Thus, if you move to a new client machine, for instance a new laptop, you need to copy the certificates and you need to store them in the same place. (The location for the certificates are stored in the database.)

Amelia suggests that you should first remove the encryption before you copy the database. I think you agree that this is a terrible idea.

David Chase 681 Reputation points

2020-10-22T21:23:43.89+00:00

Since this database will only be used on the web, and the SQL Server and the Web Server are the same box, then is the web page the client? And who cares how many different PC's (or devices) you have accessing the database?
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2020-10-22T21:34:28.877+00:00

Spot on! Yes, the PCs that access the web page has no use for the certificates.

The setup can be questioned from a security perspective. One of the key things about Always Encrypted is that the keys are separate from the database, which means that the DBA cannot access the data. Furthermore, if someone compromises the SQL Server machine, they can also get the encryption keys. So from that perspective, there could be reason to separate the web server from SQL Server. But this is a business consideration you have to decide on. I only want to point the potential risks.

Going back to your previous question, so if you take a copy of the database home, you also need to copy the certs from the certificate store on the server.
David Chase 681 Reputation points

2020-10-22T21:53:09.687+00:00

Our server is the development server so we would want to be able to occasionally backup the production server and restore it on our development server. Not sure how to do that yet as we are still struggling (separate post) to fix a decryption error due to certificate location. We need to know how to get the master key and column key stored where the website can get to them.

Answer 2

Hi @David Chase ,
If you chose Certificate Store - Current User as key store, then the certificate is stored in %APPDATA%\Roaming\Microsoft\SystemCertificates\My\Certificates.

You also can find it in certmgr.msc->Personal->Certificates.

-how can I restore the production database to my development database without having those certificates on my development database?

You can go to database-> Tasks-> Encrypt Columns to open the Always Encrypted Wizard.
On the Column Selection page, set the Encryption Type to Plaintext to remove always encrypted from a column.

Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @David Chase ,
Thanks for your reply.
When you back up a database, the resulting backup file contains encrypted stored in encrypted columns and all metadata for Always Encrypted keys. When you restore a database, all encrypted data and all metadata for Always Encrypted keys are restored. You just need to export and import Always encrypted certificate from development server to development server. Please refer to Exporting and Importing SQL Server Always Encrypted Certificates for more details.
Based on my test, after importing the certificate, the Always encrypted will work fine in the target server.
Best Regards,
Amelia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

Always Encypted Certificates

2 additional answers

Your answer