locked
SQL Symmetric key backup RRS feed

  • 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_CERT

    Problem 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


    Sean Gallardy | Blog | Twitter

    Thursday, November 8, 2012 4:40 PM
    Answerer

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


    Sean Gallardy | Blog | Twitter

    Thursday, November 8, 2012 4:40 PM
    Answerer
  • 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 PM
    Answerer
  • 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
    GO

     is 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 PM
    Answerer
  • Hi Sean,

    Thank you very much for your help. Luckily we got the password.

    Thanks,

    BB

    Friday, May 3, 2019 4:54 PM