CODEWITHSUNDEEP
sqlsql-serverencryptionsql-server-2012
Jayizzle
Jayizzle

Reputation: 532

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.

Upvotes: 1

Views: 12192

Answers (2)

Ben Thul
Ben Thul

Reputation: 32707

Since you don't care about retaining any of the certificates, you can just drop them; the password isn't required. Based on what you have above, I'd try the following:

drop certificate [testcertificate];
drop master key;

Upvotes: 2

Dave C
Dave C

Reputation: 7392

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.

Upvotes: 2

Related Questions