Answered by:
SQL Symmetric key backup

Question
-
Hi,
I have a problem here. We had created a AES symmetric key in SQL Server 2008 database using below statement:
CREATE SYMMETRIC KEY MY_KEY_AES WITH ALGORITHM = AES_128 ENCRYPTION
BY CERTIFICATE MY_DB_CERTProblem is we have not specified IDENITY_VALUE and hence private key is created by SQL itself in form of a GUID and there is no way you can check that.
Which means there is no way to backup the symmetric key.
I have tried on my test environment and found that if we delete the key there no way you are able to decrypt the data which is like a serious problem to me.
Can anyone help me to fix this?
Thanks,
SAM
Thursday, November 8, 2012 1:04 PM
Answers
-
Hello Sam,
You're correct, there is no T-SQL command to backup a single symmetric key (unless you count the service master key and the database master key). Without the values you've specified it isn't possible, again correct.
If the key is deleted, correct, you won't be able to get that data back - but there is SOME hope.
1. Backups of the user database will CONTAIN the symmetric key. If it was dropped, restore an older database backup and copy the data from the newer database into the older. Use the key to decrypt the information and use a new key to encrypt it with the identity and key source values set.
2. If the key still exists, decrypt the data and encrypt it again with a new symmetric key that uses the key source and identity values set. This way it can be duplicated in the event of a disaster or corrupt backups.
Here is a quick example of re-creating what I believe to be the situation you describe:
CREATE DATABASE Symmetric_Key_test GO USE Symmetric_Key_test GO CREATE TABLE Encrypted_Table ( ID INT IDENTITY(1,1), Plain_Text VARCHAR(100) NOT NULL, Encrypted_Text VARBINARY(1000) NOT NULL ) GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$trongPW' GO CREATE CERTIFICATE Cert_To_Encrypt_Sym_key WITH SUBJECT = 'Certificate used to encrypt all symmetric keys' GO CREATE SYMMETRIC KEY My_Bad_Sym_Key WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key GO OPEN SYMMETRIC KEY My_Bad_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key INSERT INTO Encrypted_Table(Plain_Text, Encrypted_Text) VALUES ('This is a test!', ENCRYPTBYKEY(Key_GUID('My_Bad_Sym_key'), 'This is a test!')) CLOSE SYMMETRIC KEY My_Bad_Sym_Key GO SELECT * FROM Encrypted_Table GO UPDATE Encrypted_Table SET Plain_Text = 'OOOPS!' GO -- we lost the original data, so let's decrypt to a temporary table and fix this while we regen a new key CREATE TABLE #Temp_Key_Table ( ID INT NOT NULL, Plain_Text VARCHAR(100) NOT NULL, Encrypted_Text VARBINARY(1000) NOT NULL ) GO INSERT INTO #Temp_Key_Table SELECT ID, Plain_Text, Encrypted_Text FROM Encrypted_Table GO OPEN SYMMETRIC KEY My_Bad_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key UPDATE #Temp_Key_Table SET Plain_Text = DECRYPTBYKEY(Encrypted_Text) CLOSE SYMMETRIC KEY My_Bad_Sym_Key GO SELECT * FROM #Temp_Key_Table -- now update the symmetric key from the known good information CREATE SYMMETRIC KEY My_Good_Sym_Key WITH Algorithm = AES_128, KEY_SOURCE = 'My key source!', IDENTITY_VALUE = 'Oh no, IDENTITY!' ENCRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key GO OPEN SYMMETRIC KEY My_Good_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_Key UPDATE #Temp_Key_Table SET Encrypted_Text = ENCRYPTBYKEY(KEY_GUID('My_Good_Sym_Key'), Plain_Text) CLOSE SYMMETRIC KEY My_Good_Sym_Key -- now update the source table SET IDENTITY_INSERT Encrypted_Table ON GO DELETE FROM Encrypted_Table GO INSERT INTO Encrypted_Table(ID, Plain_Text, Encrypted_Text) SELECT ID, Plain_Text, Encrypted_Text FROM #Temp_Key_Table GO SET IDENTITY_INSERT Encrypted_Table OFF --DROP THE OLD KEY DROP SYMMETRIC KEY My_Bad_Sym_Key DROP TABLE #Temp_Key_Table -- make sure we can get the information OPEN SYMMETRIC KEY My_Good_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_Key SELECT ID, Plain_Text, Encrypted_Text, CASE WHEN DECRYPTBYKEY(Encrypted_Text) = Plain_Text THEN 'SUCCESSFUL!' ELSE 'NOT Successful :(' END AS [Decrypted_Value_Success] FROM Encrypted_Table CLOSE SYMMETRIC KEY My_Good_Sym_Key GO --re-test DROP SYMMETRIC KEY My_Good_Sym_Key GO CREATE SYMMETRIC KEY My_Good_Sym_Key WITH Algorithm = AES_128, KEY_SOURCE = 'My key source!', IDENTITY_VALUE = 'Oh no, IDENTITY!' ENCRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key GO OPEN SYMMETRIC KEY My_Good_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_Key SELECT ID, Plain_Text, Encrypted_Text, CASE WHEN DECRYPTBYKEY(Encrypted_Text) = Plain_Text THEN 'SUCCESSFUL!' ELSE 'NOT Successful :(' END AS [Decrypted_Value_Success] FROM Encrypted_Table CLOSE SYMMETRIC KEY My_Good_Sym_Key GO
-Sean
- Proposed as answer by Ed Price - MSFTMicrosoft employee Saturday, November 17, 2012 4:51 AM
- Marked as answer by Maggie Luo Sunday, November 18, 2012 3:24 PM
Thursday, November 8, 2012 4:40 PMAnswerer
All replies
-
Hello Sam,
You're correct, there is no T-SQL command to backup a single symmetric key (unless you count the service master key and the database master key). Without the values you've specified it isn't possible, again correct.
If the key is deleted, correct, you won't be able to get that data back - but there is SOME hope.
1. Backups of the user database will CONTAIN the symmetric key. If it was dropped, restore an older database backup and copy the data from the newer database into the older. Use the key to decrypt the information and use a new key to encrypt it with the identity and key source values set.
2. If the key still exists, decrypt the data and encrypt it again with a new symmetric key that uses the key source and identity values set. This way it can be duplicated in the event of a disaster or corrupt backups.
Here is a quick example of re-creating what I believe to be the situation you describe:
CREATE DATABASE Symmetric_Key_test GO USE Symmetric_Key_test GO CREATE TABLE Encrypted_Table ( ID INT IDENTITY(1,1), Plain_Text VARCHAR(100) NOT NULL, Encrypted_Text VARBINARY(1000) NOT NULL ) GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '$trongPW' GO CREATE CERTIFICATE Cert_To_Encrypt_Sym_key WITH SUBJECT = 'Certificate used to encrypt all symmetric keys' GO CREATE SYMMETRIC KEY My_Bad_Sym_Key WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key GO OPEN SYMMETRIC KEY My_Bad_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key INSERT INTO Encrypted_Table(Plain_Text, Encrypted_Text) VALUES ('This is a test!', ENCRYPTBYKEY(Key_GUID('My_Bad_Sym_key'), 'This is a test!')) CLOSE SYMMETRIC KEY My_Bad_Sym_Key GO SELECT * FROM Encrypted_Table GO UPDATE Encrypted_Table SET Plain_Text = 'OOOPS!' GO -- we lost the original data, so let's decrypt to a temporary table and fix this while we regen a new key CREATE TABLE #Temp_Key_Table ( ID INT NOT NULL, Plain_Text VARCHAR(100) NOT NULL, Encrypted_Text VARBINARY(1000) NOT NULL ) GO INSERT INTO #Temp_Key_Table SELECT ID, Plain_Text, Encrypted_Text FROM Encrypted_Table GO OPEN SYMMETRIC KEY My_Bad_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key UPDATE #Temp_Key_Table SET Plain_Text = DECRYPTBYKEY(Encrypted_Text) CLOSE SYMMETRIC KEY My_Bad_Sym_Key GO SELECT * FROM #Temp_Key_Table -- now update the symmetric key from the known good information CREATE SYMMETRIC KEY My_Good_Sym_Key WITH Algorithm = AES_128, KEY_SOURCE = 'My key source!', IDENTITY_VALUE = 'Oh no, IDENTITY!' ENCRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key GO OPEN SYMMETRIC KEY My_Good_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_Key UPDATE #Temp_Key_Table SET Encrypted_Text = ENCRYPTBYKEY(KEY_GUID('My_Good_Sym_Key'), Plain_Text) CLOSE SYMMETRIC KEY My_Good_Sym_Key -- now update the source table SET IDENTITY_INSERT Encrypted_Table ON GO DELETE FROM Encrypted_Table GO INSERT INTO Encrypted_Table(ID, Plain_Text, Encrypted_Text) SELECT ID, Plain_Text, Encrypted_Text FROM #Temp_Key_Table GO SET IDENTITY_INSERT Encrypted_Table OFF --DROP THE OLD KEY DROP SYMMETRIC KEY My_Bad_Sym_Key DROP TABLE #Temp_Key_Table -- make sure we can get the information OPEN SYMMETRIC KEY My_Good_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_Key SELECT ID, Plain_Text, Encrypted_Text, CASE WHEN DECRYPTBYKEY(Encrypted_Text) = Plain_Text THEN 'SUCCESSFUL!' ELSE 'NOT Successful :(' END AS [Decrypted_Value_Success] FROM Encrypted_Table CLOSE SYMMETRIC KEY My_Good_Sym_Key GO --re-test DROP SYMMETRIC KEY My_Good_Sym_Key GO CREATE SYMMETRIC KEY My_Good_Sym_Key WITH Algorithm = AES_128, KEY_SOURCE = 'My key source!', IDENTITY_VALUE = 'Oh no, IDENTITY!' ENCRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key GO OPEN SYMMETRIC KEY My_Good_Sym_Key DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_Key SELECT ID, Plain_Text, Encrypted_Text, CASE WHEN DECRYPTBYKEY(Encrypted_Text) = Plain_Text THEN 'SUCCESSFUL!' ELSE 'NOT Successful :(' END AS [Decrypted_Value_Success] FROM Encrypted_Table CLOSE SYMMETRIC KEY My_Good_Sym_Key GO
-Sean
- Proposed as answer by Ed Price - MSFTMicrosoft employee Saturday, November 17, 2012 4:51 AM
- Marked as answer by Maggie Luo Sunday, November 18, 2012 3:24 PM
Thursday, November 8, 2012 4:40 PMAnswerer -
Hi Sean,
We are planning to migrate encrypted database from 2012 to 2016(Side by Side migration), unfortunately we don't have master key password, also don't want to disturb existing prod database. Could you please help me how to proceed with step by step.
Thanks in advance..
Wednesday, May 1, 2019 7:54 PM -
First check to see if the current database master key is encrypted by the service master key:
select is_master_key_encrypted_by_server from sys.databases where name = 'MyDBName'
If it is, the result will be a 1, if it isn't, then it'll be a 0.
If it is, simply change it to a new password:
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'MyNewP@$$2'
Backup/Restore the database to the new instance. Open the master key by using OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyNewP@$$2'
Once the master key is open, drop and add the service master key encryption.
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
That should do it. It's still incumbent upon you to test and validate.
The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.
Wednesday, May 1, 2019 9:04 PMAnswerer -
Hi Sean,
Thank you very much for quick reply.
select is_master_key_encrypted_by_server from sys.databases where name = 'MyDBName'
Above query return value 1 in my prod. Now i have create one database in DEV environment as below
CREATE DATABASE Symmetric_Key
GO
USE Symmetric_Key
GO
CREATE TABLE Encrypted_Table
(
ID INT IDENTITY(1,1),
Plain_Text VARCHAR(100) NOT NULL,
Encrypted_Text VARBINARY(1000) NOT NULL
)
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ZAq!@wSXCDe#$rFV'
GO
CREATE CERTIFICATE Cert_To_Encrypt_Sym_key
WITH SUBJECT = 'Certificate used to encrypt all symmetric keys'
GO
CREATE SYMMETRIC KEY My_Bad_Sym_Key WITH ALGORITHM = AES_128
ENCRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key
GO
OPEN SYMMETRIC KEY My_Bad_Sym_Key
DECRYPTION BY CERTIFICATE Cert_To_Encrypt_Sym_key
INSERT INTO Encrypted_Table(Plain_Text, Encrypted_Text) VALUES ('This is a test!', ENCRYPTBYKEY(Key_GUID('My_Bad_Sym_key'), 'This is a test!'))
CLOSE SYMMETRIC KEY My_Bad_Sym_Key
GO
SELECT * FROM Encrypted_Table
GO
ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'ZAq!@wSXCDe#$rFVYH'After that i took backup and restore on Test environment as below
use [Symmetric_Key]
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'ZAq!@wSXCDe#$rFVYH'
go
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
SELECT * FROM Encrypted_Table
GOis there any way to test the encryption on test environment?
Wednesday, May 1, 2019 10:40 PM -
> is there any way to test the encryption on test environment?
You inserted a value into the table, attempt to decrypt it without opening the master key using a password.
The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.
Wednesday, May 1, 2019 11:46 PMAnswerer -
Hi Sean,
Thank you very much for your help. Luckily we got the password.
Thanks,
BB
Friday, May 3, 2019 4:54 PM