none
Restoring prod database to QC encryption dilemma

    Question

  • Hello,

    I know very little about SQL Server encryption but I've been trying to read and trying hard (and long) to find an answer to this problem:

    I inherited a production server that has encryption.  I want to restore a backup copy of one of the databases to a new database on the QC server.  I'm going to setup replication between the two and want to have a "base" on QC that will be a subscriber.  My problem is that if I try to run this command (to get the logical names so that i can write my restore database move statements):

     RESTORE FILELISTONLY FROM DISK='e:\Bkps\EncryptedDB_backup_2013_03_21_020003_4738772.bak'

    I see this error: 

    Msg 33111, Level 16, State 3, Line 1
    Cannot find server certificate with thumbprint '0x047DB151491215E42EEFB3774F64AAE888891D65'.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE FILELIST is terminating abnormally.

    I have been researching for many days/hours and I'm not making any progress.  I've tried to take the two files (one cert and one key file) I find here on the production server:

    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA

    and put them in the same place on the QC server but it doesn't help. I tried this on the QC server (information faked):

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '!ABCD1234';
    go
    create certificate QCserver_Cert
    from file = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DBProd1Cert'
    with private key (file = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\DB1Prod1Key',
    decryption by password ='!ABCD1234');
    go

    (using a password I created because something I read gave me the idea that this password could be different from the original) and see this error:

    Msg 15208, Level 16, State 6, Line 1
    The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

    Question:  do I need to know the password used to create the key originally?  The DBA I replaced didn't leave that information.  If I can find someone here who knows it, will that help or isn't it necessary?  

    I have sa rights in SQL Server and am a local administrator of both servers so if someone tells me something to try, hopefully I will have rights to do it.

    Thanks in advance.

    Monday, March 25, 2013 4:06 PM

Answers

  • Hope it helps

    1) Create a database master key on the destination instance of SQL Server. The password does not have to be the same as the source server.

    2) Create the server certificate by using the original server certificate backup file. The password must be the same as the password that was used when the backup was created.

    http://msdn.microsoft.com/en-IN/library/ff773063(v=sql.105).aspx


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, March 25, 2013 8:22 PM
  • Hallo ThorNotFromNorway,

    if you don't have a backup of the certificate and you will not be able to get the password you have to disable the database encryption. Afterwards you can do a backup and restore it on a diferent server. If the database has been restored on the new server you can once again encrypt it with your own certificate.

    -- Disable database encryption
    ALTER DATABASE YourDB SET ENCRYPTION OFF;
    DROP DATABASE ENCRYPTION KEY
    
    -- Create Backup of database
    BACKUP DATABASE YourDB TO DISK = 'Path\transfer.bak' WITH COPY_ONLY, STATS, INIT;

    On the source system you can restore the database successfully

    After you've restored the database to the new system you can encrypt the database again.

    Get details concerning encryption of database and transfer of encrypted databases here:

    http://msdn.microsoft.com/en-us/library/bb934049(v=sql.105).aspx


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Tuesday, March 26, 2013 7:54 AM

All replies

  • Follow the below, it may help you :-

    http://msdn.microsoft.com/en-IN/library/ff773063.aspx


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, March 25, 2013 7:46 PM
  • Thank you Rohit, I had read that one but do you happen to know if I need the password from the original creation of the cert and key on the production server or is copying the cert and key files sufficient?  I checked the "thumbprint" and they are the same between the backup file I'm trying to restore and the server.  



    • Edited by ThorNotFromNorway Monday, March 25, 2013 7:59 PM changed fingerprint to thumbprint
    Monday, March 25, 2013 7:57 PM
  • Hope it helps

    1) Create a database master key on the destination instance of SQL Server. The password does not have to be the same as the source server.

    2) Create the server certificate by using the original server certificate backup file. The password must be the same as the password that was used when the backup was created.

    http://msdn.microsoft.com/en-IN/library/ff773063(v=sql.105).aspx


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Monday, March 25, 2013 8:22 PM
  • Thanks for sticking with me Rohit.  So, the backup file I made cannot be used?  It took 29 hours (!) to copy it from the out-of-state server to the local server that I want to restore it to so I'm really dreading backing it up and having to copy it again.  The file is 62 GB and the network provider might be throttling copies because the rate stayed at 591 kb/sec regardless of the time of day or night.

    I just want to verify that I have to do a special backup and then copy it to my local server again (29 hours).  I was using a cert and key file that were sitting on the production server and putting it on the local server.  I guess that won't work because I don't know the password . is that it?  If so, maybe I can ask around and see if someone knows it (I'm new on my job and the old DBA was gone before I started).

    Thanks.


    Tuesday, March 26, 2013 12:29 AM
  • Hallo ThorNotFromNorway,

    if you don't have a backup of the certificate and you will not be able to get the password you have to disable the database encryption. Afterwards you can do a backup and restore it on a diferent server. If the database has been restored on the new server you can once again encrypt it with your own certificate.

    -- Disable database encryption
    ALTER DATABASE YourDB SET ENCRYPTION OFF;
    DROP DATABASE ENCRYPTION KEY
    
    -- Create Backup of database
    BACKUP DATABASE YourDB TO DISK = 'Path\transfer.bak' WITH COPY_ONLY, STATS, INIT;

    On the source system you can restore the database successfully

    After you've restored the database to the new system you can encrypt the database again.

    Get details concerning encryption of database and transfer of encrypted databases here:

    http://msdn.microsoft.com/en-us/library/bb934049(v=sql.105).aspx


    Uwe Ricken

    MCSE - SQL Server 2012
    MCSA - SQL Server 2012
    MCITP Database Administrator 2005
    MCITP Database Administrator 2008
    MCITP Microsoft SQL Server 2008, Database Development

    db Berater GmbH
    http://www-db-berater.de
    SQL Server Blog (german only)

    Tuesday, March 26, 2013 7:54 AM