Jayizzle Jayizzle - 6 months ago 10
SQL Question

Encrypting with SQL in test database, can't drop Master Key because it encrypted a 'test' certificate

So I'm not sure I understand how severe this problem is, but I'm messing around in a test database for practice for encyption. I was planning on implementing TDE on the data, but I tried making a Master Key on the SQL 2012 manager interface and made certificates earlier, and thought I could just Drop the Master Key... but I couldn't. It said that it had a 'testcertificate' that was encrypted by the master key, something I made during the practice session.

I do not have the password for the master key nor the certificate. Does that mean that I am completely screwed? The column and testcertificate are meaningless. Everything else runs fine, but I can't make a new Master Key... which should be done because this time I copy-pasted the password.

Answer

If you encrypted by password, you can simply generate a new master key with a new password as follows:

USE master
GO
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Password1234';
GO

Any certificates using the old master key will automatically inherit the new master key since there can be only one.