September 4, 2009 at 10:50 am
We have some of our data encrypted and in order to be PCI compliant we need to have relevant keys / certificates replaced at least annually.
Is there (safe and) an easy way of doing this as opposed to;
a) Changing the DMK password and regenerating the data - i'm not comfortable with this in case it fails part way through
b) Create a new column per table, EncryptNew. Decrypt all the data and re-create into new columns using the new certificate / symmetric key. Delete old column and rename new column to old name.
Now, either way is a lot of processing as we have in excess of 100million rows of data (only 1 column). Is there any better methods?
Does anyone have any procedures in place to resolve this issue? I know 2008 has key management built in but upgrading to this is not an option in the short term...
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
September 4, 2009 at 2:05 pm
What is this "built in key management" you are talking about?
To answer your questions, for a) make sure you have appropriate time and backups. For b), I would re-encrypt to a second table. When complete, rename the tables.
The probability of survival is inversely proportional to the angle of arrival.
September 4, 2009 at 2:51 pm
I would ask your auditor concerning this.
Our auditor informed us that DMKs did NOT need to be regenerated. Rather the certificates and keys other than DMK needed to be regenerated. And of course the certs have to come from a different server than the one on which they are being used.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 7, 2009 at 1:19 am
i'm meeting again with him atoday so i will ask regarding the DMK but he did say last week that the password would require changing as well as the Certs/Symmetric keys annually. Also, the password was to be changed if a member of staff was to leave who would have had access to the certs/keys.
Sturner, as for Key Management in SQL 2008, see here:
_________________________________________________________________________________SQLGeordieWeb:- Jarrin ConsultancyBlog:- www.chrisjarrintaylor.co.ukTwitter:- @SQLGeordie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply