CODEWITHSUNDEEP
sql-serverencryption
Cliff Chambers
Cliff Chambers

Reputation: 73

SQL 2014 remove or fix master key issue

To make an incredibly long story short, how can I return a database back to the point where it never had any master keys, certificates, etc on it?

The data that is currently encrypted is test data, therefore I don't need to keep it.

I detached the database and moved it to the future production server, but didn't do all the stuff I should have done to make it work.

Here is what I've tried and the results:

  1. backup existing master key from old server and restore to new -Turns out I lost the password. So I added another. -backup went ok -Unable to copy the file to google drive (this is my only way of getting stuff to the server) and also cannot move the file around or even email or open it, receive message that I don't have permission. Tried 3 different backups.

status: abandoned

  1. create new password on master key on production, new certificate, new symmetric key. Source: https://mattsql.wordpress.com/2012/11/13/migrating-sql-server-databases-that-use-database-master-keys/ -Failed - can't remember error.

status: abandoned

  1. Regenerate key -ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD -Doesn't seem to work, can't remember exact error.

status: abandoned

  1. Drop everything, readd everything -Per https://sqlsailor.com/2012/01/03/drop-master-key-understanding-encryption-hierarchy/

DROP DATABASE ENCRYPTION KEY
DROP CERTIFICATE TDECertificate
DROP MASTER KEY

status: worked on home machine, able to get everything running perfectly, but that was the machine with the original set up so it was not broken in the way production is currently. Plan to try this on production tonight (I can only work on this remotely for about an hour per night)

Is there anything else I need to do to sorta return it to out of the box in terms of master keys and encryption? Do I need to regenerate the service master key as well ( I think I read that can be done)

Upvotes: 3

Views: 3319

Answers (1)

Michael Keleher
Michael Keleher

Reputation: 216

I suspect you are asking the wrong question because the answer to "how can I return the database back to the point where is has no keys?" is to drop all of the keys. You also said you don't care about the encrypted data since it is test data. However, if your goal is to migrate the encryption from your system to the production system and it is working on your system, then you need to back up the Database Master Key to a file and restore it to the production database. The fact that you did not retain the original password used to encrypt the key in SQL Server is irrelevant because when the DMK was created, the system also encrypted it with the Service Master Key. When you backup the DMK, the system can decrypt the key for you without you providing the original password. Unless you explicitly executed a command to drop the encryption by service master key, you can backup the DMK to a file, provide a password which encrypts the file, migrate the file to your production system and restore it using the file encryption password previously provided. Or you can drop all keys and rebuild the encryption hierarchy giving the keys and certificates the same name.

Upvotes: 0

Related Questions