locked
Decrypting columns in a restored SQL Server 2008 database RRS feed

  • Question

  • SQL Server 2008. Using symmetric keys, I need to encrypt columns in a table. I can successfully do the encryption and decryption in the database. However I cannot decrypt the columns when I restore the database, either to an instance on the same SQL Server, or on another SQL Server. 

    One proposed method that I have found: in the restored database, delete the symmetric key, then the certificate and lastly the database master key. In the reverse order I then recreate these items, using the original names and encryption password. This also fails.

    I have not been able to locate a definitive method to restore encrypted data that I can decrypt. What am I leaving out?

    Tuesday, June 12, 2012 1:37 PM

Answers

  • You need to re-encrypt the database master key against the new server key. To do it run:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pasword'
    
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    CLOSE MASTER KEY

    • Marked as answer by Altawan Thursday, June 14, 2012 1:12 PM
    Wednesday, June 13, 2012 9:36 AM

All replies

  • You need to re-encrypt the database master key against the new server key. To do it run:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pasword'
    
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    CLOSE MASTER KEY

    • Marked as answer by Altawan Thursday, June 14, 2012 1:12 PM
    Wednesday, June 13, 2012 9:36 AM
  • Thanks, but I am still getting a NULL value returned for the unencrypted value. This is what I tried:

    1) I ran your 3 statements against the existing problem test database

    2) I restored the database from the original server (where the decryption works). I then ran

    DROP SYMMETRIC KEY SSN_Key01;
    DROP CERTIFICATE CA01;
    DROP MASTER KEY;
    
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'my password'
        
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'my password'
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    CLOSE MASTER KEY
    
    CREATE CERTIFICATE CAreer01 WITH SUBJECT = 'Encryption';
    CREATE SYMMETRIC KEY SSN_Key01
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE CA01;
     
    OPEN SYMMETRIC KEY SSN_Key01 DECRYPTION BY CERTIFICATE CA01;
    SELECT ACCOUNT, ENCRYPTED_VALUE
        AS 'Encrypted Value',
        CONVERT(varchar(100), DecryptByKey(ENCRYPTED_VALUE )) 
        AS 'Decrypted Value',ORIGINAL_VALUE
        FROM dbo.TEST_TABLE
     

    Wednesday, June 13, 2012 8:27 PM
  • If you have something X which is encrypted Y and you through Y away, what do you do? You do of course lose the ability to decrypt X.

    In your original post: One proposed method that I have found: in the restored database, delete the symmetric key, then the certificate and lastly the database master key. In the reverse order I then recreate these items, using the original names and encryption password. This also fails.

    I don't know where you found this proposal, but it seems to be clearly wrong.

    Once you have dropped a key - be that a symmetric key, an asym key or a cert, you cannot recreate it. If you create a new key with the same name and password, you do just that - create a new key. Unless, of course, you create it from file, because in this case you import it into SQL Server.

    I have not worked that much with encryption to say exactly what the steps are, and I don't have the time to research right now. But what irusul says makes sense. Open the key and re-encrypt. But never drop a key that protects something - unless you want to lose it forever.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, June 13, 2012 9:36 PM
  • Thanks irusul and Erlund. I started off with a fresh database and ran the encryption process from scratch, starting from the creation of the master key. I then restored that database to a second server, ran the "alter master key" command and the decryption worked. Obviously, I had tried so many variations trying to get this to work on my original test databases that I had lost track of what went on before and corrupted the process; not something that would happen in the natural course of events.

    My sources on how to do the encryption/decryption process were the Microsoft help files, but I found no distinct article on how to restore a database for subsequent decryption. I followed this by "googling", which was of little use, except for the "expert" article that expounded the method I listed here. The moral of this story is that when in doubt, the best (and first) place to restort to is the Technet Forum!

    Thanks again.

    Michael

    Thursday, June 14, 2012 1:24 PM
  • You need to re-encrypt the database master key against the new server key. To do it run:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pasword'
    
    ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
    CLOSE MASTER KEY

    Many Thanks!! Those 2 lines are the culmination of 3 hours of research.  I wish I found your post first! :)
    Tuesday, August 14, 2012 9:11 PM