Restoring Server Certificate to new instance

  • winston Smith

    SSCoach

    Points: 19484

    I have two instances, InstanceA running under accountA, and InstanceB running under accountB.

    Both instances are on the same machine.

    I have a cert on InstanceA. It and its Private key are backed up:

    use master;

    go

    BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\SQLServerFiles\myServerCert.cer'

    WITH PRIVATE KEY ( FILE = 'C:\SQLServerFiles\privateKey.pvk' ,

    ENCRYPTION BY PASSWORD = 'Password1' );

    GO

    I now try to restore this to InstanceB. I create a database master key and then run code to restore the cert:

    USE master;

    GO

    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';

    go

    --this runs successfully

    use master;

    go

    CREATE CERTIFICATE MyServerCert

    FROM FILE = 'C:\SQLServerFiles\myserverCert.cer'

    WITH PRIVATE KEY

    (

    FILE = 'C:\SQLServerFiles\privatekey.pvk'

    ,DECRYPTION BY PASSWORD = 'Password1'

    );

    GO

    I now get the error:

    Msg 15208, Level 16, State 1, Line 1

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

    If i change the service account of InstanceB to accountA, i dont get any issues. Can anyone explain why this is?

    Update: I granted accountB admin rights on the server ( its only a local dev machine) but this still did not help, although it rules out any question of if the account has access to the directory that contains the cert and private key

  • winston Smith

    SSCoach

    Points: 19484

    Success!

    After adding the accountB to the administrators group, i still could not restore the cert.

    I had an idea that perhaps sql service was still operating under the credentials of the account BEFORE i added it to the admin group.

    I restarted the sql service and then i could restore the cert.

    Next question: If i change the permissions of the sql service account, is there any way to ensure those new permissions take effect, without restarting the sql service?

  • Perry Whittle

    SSC Guru

    Points: 233794

    So instance A and B were running under separate user accounts, correct?

    Are you moving a TDE protected database from one instance to another?

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] ๐Ÿ˜‰

  • winston Smith

    SSCoach

    Points: 19484

    Yea,I was taking a backup of an encrypted db on one instance and restoring it to a second instance.

    And yes, each instance SQL srvc is running under a different account.

  • Perry Whittle

    SSC Guru

    Points: 233794

    As I pointed out on your last post you made on this topic, you only need the backup of the database and the backup of the certificate. Books online details the steps required to restore a TDE protected database to a new instance.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] ๐Ÿ˜‰

  • Perry Whittle

    SSC Guru

    Points: 233794

    See my reply to your last post on this subject

    http://www.sqlservercentral.com/Forums/Topic1205594-1526-2.aspx

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] ๐Ÿ˜‰

  • winston Smith

    SSCoach

    Points: 19484

    That procedure relies on a detatch andreattach of the db, so it's not really a backup and restore of a tde database. It's moving of a tde database.

    I was trying to do this via backup and restore.

  • Perry Whittle

    SSC Guru

    Points: 233794

    winston Smith (1/8/2012)


    That procedure relies on a detatch andreattach of the db, so it's not really a backup and restore of a tde database. It's moving of a tde database.

    I was trying to do this via backup and restore.

    Is there a difference, are you sure?????

    An attach or a restore both run a create database statement, there is no difference ๐Ÿ˜‰

    Now to the problem itself

    winston Smith (12/29/2011)


    I now get the error:

    Msg 15208, Level 16, State 1, Line 1

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

    it's 99.9% likely that accountb does not have permissions to read the cert backup files in C:\SQLServerFiles, the following is an indication of this

    winston Smith (12/29/2011)


    If i change the service account of InstanceB to accountA, i dont get any issues. Can anyone explain why this is?

    This means nothing

    winston Smith (12/29/2011)


    Update: I granted accountB admin rights on the server ( its only a local dev machine) but this still did not help, although it rules out any question of if the account has access to the directory that contains the cert and private key

    Actually go to the NTFS permissions for the folder in question and check the permissions, grant accountb read access it'll work ๐Ÿ˜‰

    I can re produce your exact error message at will by adjusting permissions ๐Ÿ˜Ž

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] ๐Ÿ˜‰

  • freeman99z

    Grasshopper

    Points: 13

    Its a file permssions issue. Select proerpties on the cert and pvk files.

  • To fix, I clicked advanced and changed the file owner to mw\globalitsqladmins (my group).( I could haveselected myself as owner instead).  This is where I typedglobalitsqladmins:
  • Then clicked โ€˜enableinheritanceโ€™ on next screen (which applies all the permissions the cert folderhas to the file(s):
  •  Then was able to create the cert.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply