locked
decrypting a column when moving a database RRS feed

  • Question

  • I have a column of data in a table which is encrypted by a self-signed certificate/key.  The database master key is protected by the SMK only.

    I need to move a BAK of this database to another server/instance on a nightly basis.

    How do I decrypt the column on the target server / restored database without using OPEN MASTER KEY DECRYPTION BY PASSWORD = 'whatever' and then ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY.

    Is there an alternative?  

    I tried creating a backup of the master key and restoring it on the target, but SQL Server wants the existing key to be open.

    The goal is to allow the target instance to read the column without including the database master key password in the restore sequence each evening.

    thanks

    Tuesday, April 1, 2014 2:39 PM

Answers

  • Hello,

    You need to open the master key to my knowledge.

    Alternatively, you can reencrypt the database master key with a new server key, on the server where you are restoring the database, but you still have to open the master key.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Tuesday, April 1, 2014 3:51 PM
  • Try forcing to reopen the key

    http://msdn.microsoft.com/en-us/library/ms186336.aspx

    restore master key from file = 'f:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\EncryptTest.dat' 
    decryption by password = 'SQL1234'
    encryption by password = 'SQL1234'
    FORCE

    You need to open the SMK in the target database.

    USE EncryptTest GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol FROM TestTable GO

    Reference:-

    http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/



    -Prashanth



    Tuesday, April 1, 2014 4:23 PM

All replies

  • Hello,

    You need to open the master key to my knowledge.

    Alternatively, you can reencrypt the database master key with a new server key, on the server where you are restoring the database, but you still have to open the master key.

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com


    Tuesday, April 1, 2014 3:51 PM
  • Try forcing to reopen the key

    http://msdn.microsoft.com/en-us/library/ms186336.aspx

    restore master key from file = 'f:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\EncryptTest.dat' 
    decryption by password = 'SQL1234'
    encryption by password = 'SQL1234'
    FORCE

    You need to open the SMK in the target database.

    USE EncryptTest GO OPEN SYMMETRIC KEY TestTableKey DECRYPTION BY CERTIFICATE EncryptTestCert SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol FROM TestTable GO

    Reference:-

    http://blog.sqlauthority.com/2009/04/28/sql-server-introduction-to-sql-server-encryption-and-symmetric-key-encryption-tutorial-with-script/



    -Prashanth



    Tuesday, April 1, 2014 4:23 PM