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
Sean Gallardy | Blog |
Twitter
