Cannot use Certificate because its Private Key is not Present

Introduction

Transparent Database Encryption, or TDE as it’s colloquially known, is a great solution for data security “at rest”. That is, data is encrypted on-disk, preventing someone with a copy of the database files from viewing data stored in the database. With a non-TDE encrypted database, if you have a copy of the database files, you can simply attach them to any other SQL Server1. If you store personally identifiable information, or PII, in your database, or you’re storing financial data and need to comply with PCI-DSS, it’s imperative that you encrypt that data.

cannot use certificate

   TDE is almost as hard to setup as this never ending waterfall.
    Almost.

Let’s setup TDE!

TDE makes the process fairly simple, although there are some “gotchas”. Be aware of the encryption requirements imposed by SQL Server. For instance, you must trust SQL Server to handle the private keys. In other words, if you attempt to encrypt a database using a password-protected certificate, SQL Server will not allow you to proceed, as I show in this post.

The script below looks like it should work:

Msg 33101, Level 16, State 1, Line 31
Cannot use certificate 'TDETestCert', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

Trying to encrypt the certificate with a password, in line 17 with the ENCRYPTION BY PASSWORD = N'^MaffiaDhakaWingerBari3' clause, prevents SQL Server being able to use the certificate for database encryption. Removing that single line will allow the CREATE DATABASE ENCRYPTION KEY statement to complete successfully:

However, the ENCRYPTION BY PASSWORD = ... clause protects the certificate’s private key, and without that clause SQL Server can’t create the certificate unless we first create a database master key, in the master database. The master key is used to encrypt the certificate’s private key. With the required statements, the script now looks like:

Running the code above now results in a warning by SQL Server:

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Backup the certificate using this code:

The WITH PRIVATE KEY clause specifies that SQL Server will create a file containing a copy of the certificate’s private key. The ENCRYPTION BY PASSWORD = N'=BebopAcetalCareReims1' clause encrypts the TDETestCert.key file with that password. If you lose that password, you’ll never be able to restore the certificate from the .cert file. Store both the .cert and the .key file in a secure location. SQL Server will protect the exported .cert and .key files by limiting the Access Control List (or ACL) on each file. Only the following Windows principals will have access to the file; OWNER RIGHTS, Administrators, and the service account used by SQL Server.

You also need to backup the master key; like this:

In the example above, I’m using #BlotchOwenLonganOrly8 as the password to encrypt the backup file. If you lose this password you will never be able to restore the master key. It is imperative that you record the password in a secure location. I’d recommend using a password management tool such as the free and open-source KeePass.

Please Note!

Oh, and please, don’t use the passwords presented in this blog post. They are just examples.

One thing to consider, the above code doesn’t actually encrypt the database. At this point, we’ve only created the encryption infrastructure required. To encrypt the database, run this code:

That code causes SQL Server to begin encryption of all data and log files, including full-text indexes, in the background. Use this query to see the process of the background encryption process:

Let me know if you have an questions or comments about this. I hope this post helps clarify what causes the “cannot use certificate” error.

This post is part of our series on data security.


Footnotes

1 – any SQL Server that is the same or higher version.

Ads by Google, Paying the Rent: