September 16, 2021 at 11:29 am
I'm a developer rather than a DBA, so please excuse the straightforward question!
If I restore a database containing encrypted data (encryption using only a Database Master Key, by password) on a new instance, what do I need to do to preserve access to the encrypted data on the new instance?
Thanks for any input.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 16, 2021 at 2:30 pm
I am 99% sure you just need to restore the database as the DMK will come across with it. Once you do that, it depends on how you were accessing the data encrypted by the DMK. If it was always decrypted when you went to use it, then you should just need to open the DMK, alter the DMK to add encryption by SMK, close the DMK.
If you needed to decrypt it every time you wanted to use it, (open master key with decryption by password='supersecretpassword' for example), then don't add the encryption by the SMK.
If you restore the service master key, then anything else that relies on the old SMK will break. If you JUST restore the DMK without tying it to the SMK, you will need to run "OPEN MASTER KEY" prior to using it. If you re-create the DMK on the new instance, all previously encrypted data may be lost. There are ways to restore the DMK from a backup with a new password without data loss IF you can back it up to disk successfully (which you should be able to do if it is encrypted by the SMK, even if you don't know the DMK password).
The EASIEST way to confirm the above though is to restore the database onto a lower system (test, dev, etc) and test it out.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 16, 2021 at 2:51 pm
Brian, thanks for the response. This is very much at the proof of concept stage right now. To give you an idea of how I was thinking of setting things up, here is some code.
--Create a database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'qwerty'; --No one will ever guess this!
--Create a Beers certificate
CREATE CERTIFICATE Beers
WITH SUBJECT = 'Beer data';
--Create a symmetric key, to be used for column encryption
CREATE SYMMETRIC KEY BeersKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Beers;
--The above statements set things up. They need to be executed only once.
--Create a sample table and put some data in it
DROP TABLE IF EXISTS #SomeData;
CREATE TABLE #SomeData
(
Id INT IDENTITY(1, 1)
, PlainText VARCHAR(100)
, EncryptedText VARBINARY(MAX)
);
INSERT #SomeData (PlainText)
VALUES
('Some text here');
SELECT *
FROM #SomeData sd;
--Now let's set the EncryptedText column to contain an encrypted version of PlainText
--First 'open' the symmetric key
OPEN SYMMETRIC KEY BeersKey
DECRYPTION BY CERTIFICATE Beers;
--Update the EncryptedText column
UPDATE sd
SET sd.EncryptedText = ENCRYPTBYKEY(KEY_GUID('BeersKey'), sd.PlainText)
FROM #SomeData sd;
--Close the symmetric key
CLOSE SYMMETRIC KEY BeersKey;
--Look at the encrypted rows in #SomeData
SELECT *
FROM #SomeData sd;
--Decrypt the rows in #SomeData
--First 'open' the symmetric key
OPEN SYMMETRIC KEY BeersKey
DECRYPTION BY CERTIFICATE Beers;
SELECT
sd.Id
, sd.PlainText
, sd.EncryptedText
, DecryptedText = CONVERT(VARCHAR(100), DECRYPTBYKEY(sd.EncryptedText))
FROM #SomeData sd;
--Close the symmetric key
CLOSE SYMMETRIC KEY BeersKey;
I imagine your comments still apply and yes, I will definitely test the restore process to another instance when this is set up.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 16, 2021 at 5:54 pm
I just read through the code and to me I think what I said above should be correct.
The only thing is you will likely need to open the master key a lot with that as it is not tied to the service master key isn't tied to decrypting the database master key.
But as long as you know the DMK password and never forget it (I know qwerty isn't secure and is pretty easy to remember, BUT I'd still toss it into a password manager so that the next person who comes along to work on your stuff isn't shuffling through notes and creation code to try to find the password), you should be good. The nice part about having it tied to the Service master key is that you can add a new password without knowing the old one in the event you ever forget it. You still need to know one of the DMK passwords when you do the restore or you won't be able to open the DMK as the service master key is tied to the instance.
Another thing you could (should) do is backup the database master key. If something happens to it (corruption, accidental deletion) and it needs to be recovered, you can restore it from a backup without needing to restore the entire database. I'd also backup the certificate for the same reason. Unfortunately, there isn't a good way to back up a symmetric key that I am aware of.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 17, 2021 at 10:18 am
Thanks, Brian.
To be clear, 'qwerty' was an attempted joke. The actual password will be long and complex. We store stuff like this in an Azure data vault, to ensure it's available to any authorised users.
Your advice and feedback is much appreciated. I have more R&D to get into now, as I work out how best to modify my existing ETL process (and SSRS procs) to work with encrypted columns.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 17, 2021 at 3:21 pm
I thought it was a joke, but I've learned (the hard way) to never assume that people are joking 🙂
And I think you should be good to go then! Good luck with the process changes!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 17, 2021 at 5:09 pm
--Create a symmetric key, to be used for column encryption
CREATE SYMMETRIC KEY BeersKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Beers;
'qwerty123' is more complex. I never encrypt my beer, takes too long to open.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply