SQL 2008 R2: Generate trusted TDE certificate

As previously mentioned in my blog about SQL TDE (Transparent Data Encryption), the example script I gave just used a SQL self-signed certificate to encrypt the database. While this is fine for a demo, you should only used trusted certificates in a production environment.

Getting a trusted certificated inserted into SQL 2008 R2 is easier than it sounds and took quite a bit of digging. BUT, there is a way and it's not too terrible. Plus, this method can be used with commercial certificate authorities or an internal CA of your choice. It does not rely on a Microsoft CA, but works perfectly fine with one.

1. Download OpenSSL 1.x and install it. Do not use v0.9.x releases as they won't work.

2. Open a command prompt and type: openssl.exe genrsa 2048 > private.key

3. Type: openssl.exe req -new -key private.key > certificate.csr

4. You will be prompted with a series of questions. Input data as you see fit, but pay attention to the "Common Name". This will be the subject of the certificate.

5. Open the certificate.csr file and submit it to your favorite certificate authority. It could be a commercial or internal CA. Save the resulting certificate as a DER (not BASE64) file, let's say certificate.cer.

6. Type: openssl rsa -in private.key -outform PVK -pvk-strong -out private.pvk
-You will be prompted to type a password to protect the private key. Remember the password.

7. Open SQL Management studio and create a new query. Cut and paste the following query, adjusting the paths, filenames and password from step #6 you used. You can change "My_New_Certificate" to any name you wish. Probably best to use the common name you input during the certificate request.

----
CREATE CERTIFICATE My_New_Certificate
FROM FILE = 'D:\certificate.cer'
WITH PRIVATE KEY (FILE = 'D:\private.pvk',
DECRYPTION BY PASSWORD = 'MyPassword');
GO
----

8. Press the Execute button and you should get "command(s) completed successfully." If not, triple check your paths, filenames, and password. The error messages are not helpful if you get it wrong.

9. To verify the certificate was actually installed and to view all the other certificates, in SQL Server Studio execute this query:

----
use master
go
select * from sys.certificates
go
----

And there you have it! You can now refer to back to my TDE blog post and change the sample script to use this new trusted certificate instead of the self-signed "RMSServerCert". You should backup the two certificates you imported into SQL and delete all copies on the local hard disk. If you ever need to restore your encrypted database you MUST have these two certificate files...no certificates..no data!

Happy encrypting!