locked
Restoring Master Key to different server RRS feed

  • Question

  • Hi all,

    I'm trying to restore a master key created on a Sql Server 2008 R2 instance to my local Sql Server 2012 Dev instance using:

    RESTORE MASTER KEY
        FROM FILE = 'C:\MyLocation\Mkey'
        DECRYPTION BY PASSWORD = 'myPassword'
        ENCRYPTION BY PASSWORD = 'myPassword';
    GO

    I keep getting the following error message:
    The master key file does not exist or has invalid format.

    I know the file exists in the location, it's saved as Mkey.bak.

    Does anyone know what the problem is? could it be a permissions issue for Sql Server?

    Regards
    Melt


    • Edited by Meltdown61 Tuesday, July 17, 2012 11:02 AM
    Tuesday, July 17, 2012 10:59 AM

Answers

  • OK, it turns out the default installation of SS2012 creates an account 'NT Service\MSSQL$SQL2012DEV' that doesn't have permissions on a lot of files/folders, I set Sql Server to use a Local Host account and now I can run the RESTORE command. That's thrown up another issue but I'll make a new post for that.

    Thanks all.

    Melt



    • Marked as answer by Meltdown61 Tuesday, July 17, 2012 4:24 PM
    • Edited by Meltdown61 Tuesday, July 17, 2012 4:26 PM
    Tuesday, July 17, 2012 4:24 PM

All replies

  • Hi,

    Try changing your FROM FILE clause to read

    FROM FILE = 'C:\MyLocation\Mkey.bak'

    Pete


    Peter Carter http://sqlserverdownanddirty.blogspot.com/

    Tuesday, July 17, 2012 11:53 AM
  • Hi Peter, thanks for the reply.

    Tried that, still getting: "The master key file does not exist or has invalid format."

    Regards
    Melt

    Tuesday, July 17, 2012 11:58 AM
  • use below query

    USE database_name;

    GO

    RESTORE MASTER KEY
        FROM FILE = 'C:\MyLocation\Mkey.bak' 

    DECRYPTION BY PASSWORD = 'password'

    ENCRYPTION BY PASSWORD = 'password';

    GO


    Nareshkar Pakanati 

    MCTS, MCITP IT Specialist
    My Blog: www.mssqlfix.com
    Twitter: @mssqlfix
    -------------------------------------------------------
    Please mark the post as Answered if it answers your question.
    -------------------------------------------------------

    Tuesday, July 17, 2012 1:09 PM
  • Hi Nareshkar, thanks for the reply.

    Tried that, still getting: "The master key file does not exist or has invalid format."

    I've moved the master key to a number of different locations on my local machine, but always get the same error. I'm also logged in as Administrator

    Regards
    Melt

    Tuesday, July 17, 2012 1:47 PM
  • Obviously, there is reason to double- and triple-check the location. You also need to ensure that that the service account for SQL Server has permission to read the file.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, July 17, 2012 2:10 PM
  • OK, it turns out the default installation of SS2012 creates an account 'NT Service\MSSQL$SQL2012DEV' that doesn't have permissions on a lot of files/folders, I set Sql Server to use a Local Host account and now I can run the RESTORE command. That's thrown up another issue but I'll make a new post for that.

    Thanks all.

    Melt



    • Marked as answer by Meltdown61 Tuesday, July 17, 2012 4:24 PM
    • Edited by Meltdown61 Tuesday, July 17, 2012 4:26 PM
    Tuesday, July 17, 2012 4:24 PM
  • Hi, This happened to me when I move TDE encrypted database from SQL Server 2008 instance to SQL Server 2012. After investigation I came to know that account that SQL service account haven't got access to the location of the master key backup. I granted service accout NTFS permissions to read master key backup and then executed the command and this time it worked successfully.


    • Proposed as answer by Basit Farooq Wednesday, July 18, 2012 1:55 PM
    • Edited by Basit Farooq Wednesday, July 18, 2012 1:56 PM
    Wednesday, July 18, 2012 1:55 PM