Managing and Rotating keys for encryption for many SQL Servers

There is all kinds of great info out there about the mechanics behind column level encryption in SQL2005, but it all seems to assume I only have 1 or 2 database servers. If I am using an X509 certificate to encrypt my data, it looks as if I can script the administration of this fairly easily.

But what if I have 1000 SQL Servers?

Is there any guidance/best practices/tools out there that will help me manage the 1000 certificates that I would need to deploy in such a scenario. Also, what if I need to 'rotate' the certificates for some reason. Can a PKI for the domain help me to automate and manage this?

It seems as if the management of these certificates is purely 'manual' at this point.

Thanks for any help,

...Andrew

Currently there is no support for PKI, and the infrastructure for key recycling is limited to the SERVICE MASTER KEY and DB MASTER KEY. CERTIFICATE, ASYMMETRIC KEY and SYMMETRIC KEY objects need to be managed by on your application, but the available catalog views and builtins should help you in this task.

The SQL Server encryption infrastructure is better suited for encrypting data using SYMMETRIC KEYs, and protecting the SYMMETRIC KEYs with CERTIFICATEs or ASYMMETRIC KEYs.

BTW. Data encryption based on SYMMETRIC KEYs is recommended over CERTIFICATEs for performance and plaintext length limitation (only 1 block of data, typically ~117 bytes).

We really appreciate your feedback, and we would like to encourage you to share more details on how do you expect to use this feature and what type of improvements you consider are necessary.

-Raul Garcia

SDE/T

SQL Server Engine

|||

"The SQL Server encryption infrastructure is better suited for encrypting data using SYMMETRIC KEYs, and protecting the SYMMETRIC KEYs with CERTIFICATEs or ASYMMETRIC KEYs."

Right - this is what we are doing - but I didn't communicate that well in my previous post.

I have had two major clients who are implementing column level encryption due to PCI (Payment Card Industry) requirements in retail.

The PCI specification calls for a defined and documented mechanism for "rotating" keys on a regular basis if needed. These retailers are HUGE, thousands of stores - each running their own instance of SQL server holding financial transactions which inclue credit card info.

So this info qualifies as "data at rest" and must be encrypted.

I am recommending certficates to protect the symmetric keys - but my customers want to know how to manage the certificates that need to be deployed to each SQL machine. As the SQL box is just a W2K3 machine in a domain, I was thinking that could be managed via PKI or some kind of auto-enrolment?

From what you are saying, the actual "rotation" of the certificate could only be handled through some kind of script.

IMO - a great KB would cover the steps required to rotate certificates (including the subsequent decryption and re-encryption of symmetric keys) in a large, enterprise deployment.

|||

There are some useful ideas on the topic of rotating certificates in the comments for one of the posts on my blog - see the conversation with Boaz:

http://blogs.msdn.com/lcris/archive/2006/03/13/550904.aspx

Thanks

Laurentiu