SQL Server TDE

Category: sql server security

Question

donsjones on Mon, 21 Jan 2013 17:59:57


Are certificates from a 2008/R2 Enterprise Certificate Authority Supported in Transparent Data Encryption in SQL Server 2008 R2 and SQL Server 2012?  If so, can you point me to the documentation?

I have been unable to find valid documentation on how to do this.  I tried using openssl to extract the private key and export the cert in DER and Base64 format and no luck.  I keep on getting errors about the certificate, and or private key is invalid or I do not have access to the files.  Tried v2 and v3 certificates.

Sevice Master Key has been created and backed up.

Any assistance would be appreciated.

Thanks.

Don Jones

Replies

SQLWork on Mon, 21 Jan 2013 20:38:02


I am not currently using TDE, but perhaps these pages will help you understand your choices.  These are the 2012 links, but you can choose the 2008 R2 from the "Other Versions" selection list.

Encryption Hierarchy  Information about the encryption hierarchy in SQL Server.

Transparent Data Encryption (TDE)   General information about how to encrypt data transparently.  (You will see that certificates do play a part.)

FWIW,
RLF

Maggie Luo on Tue, 22 Jan 2013 03:40:45


Hi donsjones,

Transparent Data Encryption (TDE) uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.

You need to create certificate in master database to encrypt the Database Encryption Key. Then use the DEK to encrypt the user database.

Refer to the Transparent Data Encryption recommended by Russell.

Thanks.

donsjones on Tue, 22 Jan 2013 11:37:34


Thanks for the reply.

I have already gone through the links you mentioned, and they do not provide the information I am after.

I am already using TDE in a development/test environment and provides what we are looking for as far as protecting the database. 

In order to move TDE into production, we are required to use Certificates from our Microsoft Certificate Authority, or our Third Party Certificate Authority.

Does SQL Server 2008/R2 and or SQL Server 2012 support using certificates from Microsoft Certificate Authority or a Third Party Certificate Authority?

Don Jones

SQLWork on Tue, 22 Jan 2013 15:42:43


The following whitepaper exists:  

http://download.microsoft.com/download/8/F/A/8FABACD7-803E-40FC-ADF8-355E7D218F4C/SQL_Server_2012_Security_Best_Practice_Whitepaper_Apr2012.docx

On page 14 it makes this comment:  "In SQL Server 2012, when creating certificates from external sources, the maximum length of private keys imported from an external source is expanded from 3,456 to 4,096 bits."

So, it seems to support external sources and I cannot think why either the Microsoft or Third Party certificate authority would be excluded. 

Can you spin up a test server and try it out?

RLF

donsjones on Tue, 22 Jan 2013 16:00:51


Thanks for the reply.

The document was not very helpful.  It talks about using external sources via EKM (extensible Key Management) such as a Hardware Security Module; this sounds like a smart card.

Tried various key lengths and still received errors.

I am using a test server, and working on building a second one and try some other things.

Don Jones

SQLWork on Tue, 22 Jan 2013 21:26:45


I reread page 14 again.  It is true that 3 paragraphs later it mentions: "As an alternative ... EKM...".   I did not interpret that to be a requirement.   See the text in the Books Online article:  CREATE CERTIFICATE

There it says in part: "A certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. CREATE CERTIFICATE can load a certificate from a file or assembly. "  It says nothing about the source of the file, but something about the standard.  The other document mentions the size limit.

Hope your testing makes the answer (whatever it turns out to be) clear.  Look forward to reading the results.

RLF

lubichjd on Wed, 21 Jan 2015 19:20:26


RLF

I've found through trial and error, that even through certs up to 4096 bits can be imported, you will get an error creating a database encryption key using them because the encryption algorithm can only handle up to 3456 bit certificates.

Anything over 3456 bits gets this error

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE CertFor3520bits;

Msg 15209, Level 16, State 26, Line 2
An error occurred during encryption.

Jeremy Lubich