locked
Restroring Encrypted Production database to Encrypted Dev database RRS feed

  • Question

  • I have a production instance of SQL Server 2008 R2 running Master Data Services encrypted using using a certificate named "MDSCertP" by password "CertP1234".  I have a development instance of SQL Server 2008 R2 running Master Data Services encrypted using using a certificate named "MDSCertD" by password "CertD1234".    I've been asked by the the developer to restore a production copy of the MDS database to dev.

    I understand that to restore successfully, I l need to physically copy the certificate (.cer) and private key (.pvk) to the dev server.

    Am I going to kill the dev instance by copying the prod certs?  Is there a better practice to refresh the data on dev?

    Thanks,

    :g

    Wednesday, April 2, 2014 2:52 PM

All replies

  • Hi,

    To restore a encrypted database on another instance, besides the certificate and private key, you also need backup the master key and restore it on the second instance.

    RESTORE MASTER KEY FROM FILE = 'path_to_file'

        DECRYPTION BY PASSWORD = 'password'

        ENCRYPTION BY PASSWORD = 'password'

    [ FORCE ]

    You can refer to the below article for details:

    http://philipflint.com/2011/01/19/how-to-encrypt-a-sql-server-database/comment-page-1/

    BACKUP and RESTORE Statements (Transact-SQL)

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

    Hope it helps.


    Tracy Cai
    TechNet Community Support

    Thursday, April 3, 2014 6:26 AM
  • Thanks Tracy.  My issue is that the destination database is encrypted using its own certificate.  If I restore the source database's master key, what happens to the destinations master key?
    Thursday, April 3, 2014 6:09 PM
  • I would recommend to drop the target certificate and MasterKey. Copy and restore the master key, create the certificate on the source then open the master key and restore the database

    USE <DevDBName>
    GO
    DROP CERTIFICATE <CertificateName>
    GO
    DROP MASTER KEY

    Refer the below link

    http://basitaalishan.com/2012/07/08/securing-database-contents-with-transparent-data-encryption-tde/

    -Prashanth

    Thursday, April 3, 2014 9:28 PM
  • And now the problem grows!

    My goal was to restore a production database backup with TDE to a development instance.

    My process was as follows:

    1. Copy production TDE keys and certificates to the dev instance
    2. Backup production database\
    3. Copy backup to dev instance
    4. Backup dev database instance
    5. Disable TDE dev instance
    6. Dropped dev database
    7. Restart SQL Service
    8. Restore production TDE on dev instance
    9. Restore/reconfigure logins

    When I attempted to RESTORE MASTER KEY from the production backup, via T-SQL below:

    USE [master]
    GO

    RESTORE MASTER KEY
      FROM FILE = 'C:\Users\Me\Documents\PROD\MasterKey'
      DECRYPTION BY PASSWORD = 'password1'
      ENCRYPTION BY PASSWORD = 'password1'
    GO

    I received the following error:
    Msg 15317, Level 16, State 3, Line 2
    The master key file does not exist or has invalid format.

    Now I cannot restore the dev backup.

    What am I doing wrong?  How can I correct?

    Thank you!

    Wednesday, April 9, 2014 3:15 PM
  • Can you try to copy master key to different path and make sure that SQL Service account have enough rights to read the master key Also what is the DEV SQL instance version?

    The similar issue is discussed in the below post

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c487c0ad-9866-4c54-bbcc-110508032a8c/restoring-master-key-to-different-server?forum=sqlsecurity

    -Prashanth


    Wednesday, April 9, 2014 3:33 PM
  • Thank you Prashanth.  Both instances are SQL Server 2008 R2 Enterprise Edition 10.50.2811.

    I was able to restore the master key by updating the permissions, but now I've run into the following when I attempt to create the Certificate from the following T-SQL:

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password1'

    CREATE CERTIFICATE ProdCert
      FROM FILE = 'C:\Users\Me\Documents\PROD\\ProdCert'
      WITH PRIVATE KEY (
      FILE = 'C:\Users\Me\Documents\PROD\\ProdPrivKey'
      , DECRYPTION BY PASSWORD = 'Password2'
    )

    Msg 15208, Level 16, State 1, Line 3

    The certificate, asymmetric key, or private key file does not exist or has invalid format.

    Permissions seem set correct.  Ideas/Thoughts?

    Wednesday, April 9, 2014 6:41 PM
  • This is also same issue. Check the path once ,there is one extra '\'? also decryption password should by Password1. Check and make sure that you've enough rights to perform this operation.

    I hope you are following below link

    http://basitaalishan.com/2012/07/08/securing-database-contents-with-transparent-data-encryption-tde/

    -Prashanth

    Wednesday, April 9, 2014 8:00 PM
  • Prashanth,

    Path is correct; I fat-fingered the T-SQL so it could be displayed in public forum.  Private key protected by Password2

    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password1'

    CREATE CERTIFICATE ProdCert
      FROM FILE = 'C:\Users\Me\Documents\PROD\ProdCert'
      WITH PRIVATE KEY (
      FILE = 'C:\Users\Me\Documents\PROD\ProdPrivKey'
      , DECRYPTION BY PASSWORD = 'Password2'
    )

    Wednesday, April 9, 2014 8:06 PM
  • Do you get a different message if you try a path that you know is incorrect?`

    Keep in mind that the path must be from the server and not your workstation. (The C:\Users\Me\Documents looks a little funny to me in this context.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, April 9, 2014 10:03 PM
  • Erland,

    Thanks for responding.  I altered the path for public consumption.  The actual path is 'M:\Program Files\Microsoft SQL Server\MSSQL10_50.HRMDM\MSSQL\DATA\file_name'

    Thursday, April 10, 2014 1:41 PM
  • So did you try with a path that you know is incorrect? Did you get a different error message?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 10, 2014 9:52 PM