none
Unable to decrypt the SQL server column - SQL 2005

    Question

  • Hi,

    We recently downgraded an Enterprise edition of sql server 2005 to Standard edition. We uninstalled the Enterprise edition and re-installed the standard edition. We then restored all user databases and transferred the logins.

    We did not notice that there is database with an encrypted column. This column was encrypted using a certificate and a symmetric key. 

    We are unable to decrypt the column as I guess the SQL instance master key is not available. Please let us know if there is a way to decrypt this column and get the data. We have forgotten to backup the master key from the old instance and all we have now is just the system &user database backups.

    I get the below error when i try to open the database master key.

    Msg 15313, Level 16, State 1, Line 2
    The key is not encrypted using the specified decryptor.

    Any help would he greatly appreciated.

    Thanks,

    John 

    SQL DBA


    • Edited by John DBA Friday, April 06, 2012 10:14 AM
    Friday, April 06, 2012 9:49 AM

Answers

  • Hi John,
    If you know the password which is used to encrypt the database master key (DbMK), you can create a service master key (SMK) on the new server, and regenerate the DbMK as below:

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



    For more information, please pay attention to the following blogs:
    SQL Server 2005: Restoring the backup of a database that uses encryption
    SQL Server 2005: How to recover when the service master key (SMK) is not accessible
     


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Stephanie Lv Monday, April 16, 2012 7:08 AM
    Monday, April 09, 2012 7:05 AM
  • We have forgotten to backup the master key from the old instance and all we have now is just the system &user database backups

    Hello John,

    Other option: Get a second (may virtuell) machine with similar configuration as your first server (same drive letters). Install a new SQL Server with same instance name as before, and then restore the master database (a disaster recovery), then you may will be able to backup the master key which is stored in the master database.

    And don't forget to backup all your key this time.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Stephanie Lv Monday, April 16, 2012 7:08 AM
    Monday, April 09, 2012 7:11 AM
    Moderator

All replies

  • Unfortunately there is no way if you do not having original encryption key.


    Kindly mark the reply as answer if they help

    Friday, April 06, 2012 11:27 AM
  • Hello,

    Sorry to say that you need the original encryption key. :(

    Friday, April 06, 2012 1:32 PM
  • Hi John,
    If you know the password which is used to encrypt the database master key (DbMK), you can create a service master key (SMK) on the new server, and regenerate the DbMK as below:

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



    For more information, please pay attention to the following blogs:
    SQL Server 2005: Restoring the backup of a database that uses encryption
    SQL Server 2005: How to recover when the service master key (SMK) is not accessible
     


    Stephanie Lv

    TechNet Community Support

    • Marked as answer by Stephanie Lv Monday, April 16, 2012 7:08 AM
    Monday, April 09, 2012 7:05 AM
  • We have forgotten to backup the master key from the old instance and all we have now is just the system &user database backups

    Hello John,

    Other option: Get a second (may virtuell) machine with similar configuration as your first server (same drive letters). Install a new SQL Server with same instance name as before, and then restore the master database (a disaster recovery), then you may will be able to backup the master key which is stored in the master database.

    And don't forget to backup all your key this time.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Marked as answer by Stephanie Lv Monday, April 16, 2012 7:08 AM
    Monday, April 09, 2012 7:11 AM
    Moderator