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!
I just tried this with openssl 1.0.0f and SQL is giving “the certificate, asymmetric key, or private key file does not exist or has invalid format.” error.
Paths are correct and files are present. Not sure what to try next.
SQL is 2008 R2 on 64bit. Openssl is 32bit.
Anonymous, as I recall I did it in the master database, but it’s been a while since I configured it. I would of course recommend testing any encyption in a lab environment and not on a production server.
Hi Derek,
This is awesome article and it is very easily understandable like starters like me.
I just wanted to understand one point here is "Why Trusted certificates should be used in the production environment?". Could you please eloborate little bit and I can have a discussion with my team to use only trusted certificates while configuring TDE.
Thanks again for the article.
In a production environment you should always use trusted certificates. Why? You want to ensure the security of your data. If you use self-signed certificates then you open yourself up to man in the middle attacks, where you data could be compromised. Security depends on trusting the certificates, so you should use a trusted CA and protect the certificates carefully. Just like when browsers warn you about untrusted SSL certificates on web sites, you need to validate and trust all of your internal certs as well.
That is a good argument for SLL certificates used for encryption in transit, such as the PFX cert that gets bound to a SQL instance via SSCM. For encryption at rest, however, there is no “middle”. Outside servers or clients are not connecting to the database server and using this certificate to validate that the server is who it says it is. This cert is solely used by SQL Server TDE as an encryption key. The trusted cert’s string of random characters is no better or worse than the same length string of random characters generated by SQL. Of course,… Read more »
Hi, we just tried this and step 6 i.e. the following command doesnot work->
openssl rsa -in private.key –outform PVK –pvk-strong -out private.pvk
I think is doesnot accepts a PVK as outform argument, secondly cnnot understand what does the parameter -pvk-strong does?
My only concern here is how we will add password to the private key so that we can provide a “decrypt by password” in step 5