0

In SQL Server 2008 R2, If I alter the database master key do I have to make any changes to certificates that change user data base encryption keys. I have an annual update requirement.

So if I do this:

USE [Master];
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'blah((blah&something';
GO

  1. will it also update keys encrypting all the the certificates that were created for user database encryption keys?

  2. is there a data loss risk?

There are a lot of examples on how to make the change but I'm having trouble finding on point commentary on this specific situation.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jeff Maier
  • 131
  • 1
  • 2
  • 6

1 Answers1

1

Regenerating the DBMK will re-encrypt all keys encrypted with the DBMK, as well as some internal stuff encrypted with DBMK (eg. service broker conversation endpoints session keys). There is no risk of data loss. If the old DBMK cannot be opened (eg. unknown password) then the ALTER statement will fail. In such a situation if FORCE option is used then data loss may occur if the old DBMK encryption was the only one available and is lost in the forced regeneration.

Is not clear what you mean by 'user database encryption keys'. All keys physically stored in the database (this includes any database scoped certificate with a private key encrypted with the master key) will be re-encrypted. But, for instance, Transparent Database Encryption keys will not be re-encrypted, since they are not stored in the database they encrypt.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • Sorry for being ambiguous. Certificates were created using the database master key and then were used for various user databases like this: use [somedb] create database encryption key with algorithm = AES_256 encryption by server certificate xxx_cert; Is the impact, if any, on the user database encryption transparent? – Jeff Maier Feb 04 '13 at 21:00
  • the impact should be completely transparent. Why do you need to regenerate the master key? – Remus Rusanu Feb 04 '13 at 21:28
  • pci compliance calls for changing the key annually. – Jeff Maier Feb 04 '13 at 21:41