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:
<ol>
<li>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.</li>
</ol>
status: abandoned
<ol start="2">
<li>create new password on master key on production, new certificate, new symmetric key. Source: <a href="https://mattsql.wordpress.com/2012/...rver-databases-that-use-database-master-keys/" rel="nofollow">https://mattsql.wordpress.com/2012/...rver-databases-that-use-database-master-keys/</a>
-Failed - can't remember error.</li>
</ol>
status: abandoned
<ol start="3">
<li>Regenerate key
-ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD
-Doesn't seem to work, can't remember exact error.</li>
</ol>
status: abandoned
<ol start="4">
<li>Drop everything, readd everything
-Per <a href="https://sqlsailor.com/2012/01/03/drop-master-key-understanding-encryption-hierarchy/" rel="nofollow">https://sqlsailor.com/2012/01/03/drop-master-key-understanding-encryption-hierarchy/</a></li>
</ol>
<div class="snippet" data-lang="js" data-hide="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override">
</div>
</div>
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)
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:
<ol>
<li>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.</li>
</ol>
status: abandoned
<ol start="2">
<li>create new password on master key on production, new certificate, new symmetric key. Source: <a href="https://mattsql.wordpress.com/2012/...rver-databases-that-use-database-master-keys/" rel="nofollow">https://mattsql.wordpress.com/2012/...rver-databases-that-use-database-master-keys/</a>
-Failed - can't remember error.</li>
</ol>
status: abandoned
<ol start="3">
<li>Regenerate key
-ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD
-Doesn't seem to work, can't remember exact error.</li>
</ol>
status: abandoned
<ol start="4">
<li>Drop everything, readd everything
-Per <a href="https://sqlsailor.com/2012/01/03/drop-master-key-understanding-encryption-hierarchy/" rel="nofollow">https://sqlsailor.com/2012/01/03/drop-master-key-understanding-encryption-hierarchy/</a></li>
</ol>
<div class="snippet" data-lang="js" data-hide="false">
<div class="snippet-code">
<pre class="snippet-code-html lang-html prettyprint-override">
Code:
DROP DATABASE ENCRYPTION KEY
DROP CERTIFICATE TDECertificate
DROP MASTER KEY</div>
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)