November 24, 2011 at 1:54 am
I want to learn about altering db encryption key.
Because it is possible that the security team want the key change periodically.
I have 2 database servers - Primary and Secondary.
Both are encrypted with TDE.
1. I encrypted Primary database with TDE by creating master key, then certificate, encrypt, ...
2. At Primary server, I create back up of certificate and private key files.
3. At Primary, I create back up of database.
4. At Secondary server, I create master key and create certificate from the files copied from Primary server.
5. At Secondary server, I restore database successfully.
Now both databases are TDE encrypted. Both work normally
6. Back to Primary server, I alter the encryption key
USE [DB_NAME];
GO
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256;
GO
7. At Primary server, I back up this database.
8. At Secondary server, I try restore this database.
I expect that it should fail because the encryption key is already changed at Primary but it is not yet applied to Secondary.
9. But surprise that restore successful !!!
Why ????
November 24, 2011 at 1:59 am
November 24, 2011 at 2:21 am
USE [DB_NAME];
GO
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256;
GO
You're not changing the certificate, you're simply regenerating the encryption. To change the certificate you would have to use:
USE [DB_NAME];
GO
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256
ENCRYTPION BY CERTIFICATE 'NewCert';
GO
Without the new certificate details you're simply refreshing the encryption using the current certificate, which is why it still works on the second server.
November 24, 2011 at 2:58 am
Before running your suggested command, I have to create a new certificate first also ?
November 24, 2011 at 3:04 am
Yes, otherwise you're simply regenerating the encryption based on the existing certificate.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply