locked
Restoring Master Key to different server SQL 2012 RRS feed

  • Question

  • Hi all,

    I'm trying to restore a master key created on a Sql Server 2012 EE instance to another SQL 2012 EE instance using

    use master
    RESTORE MASTER KEY
        FROM FILE = 'A\SQLBackup\TDE_Master_Key.key'

    -- A Is the drive letter i copied the master key from Source server to this drive

        DECRYPTION BY PASSWORD = '1Password'
        ENCRYPTION BY PASSWORD = '1Password';
    GO

    I am getting this error

    Msg 15317, Level 16, State 2, Line 2
    The master key file does not exist or has invalid format.


    Thanks, Ahmer


    • Edited by IAhmer Thursday, January 29, 2015 1:21 PM corrected query
    Thursday, January 29, 2015 1:16 PM

Answers

  • Hi IAhmer,

    Please run the following script to restore master key. I note that you don't specify colon (:) after "A".

    use master
    
     RESTORE MASTER KEY
    
         FROM FILE = 'A:\SQLBackup\TDE_Master_Key.key'
    
    -- A Is the drive letter i copied the master key from Source server to this drive
    
        DECRYPTION BY PASSWORD = '1Password'
    
         ENCRYPTION BY PASSWORD = '1Password';
    
     GO

    Besides, please ensure that the name of master key  backup file is correct, maybe you have defined it as “TDE_Master_Key” but not “TDE_Master_Key.key ”?  Also as Vishal’s post, please make sure that the destination SQL Server Service account has NTFS permissions to read master key backup in 'A:\SQLBackup’.



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support




    Friday, January 30, 2015 8:58 AM
  • >>How many Databases can be encrypted on same instance ? 

    You can encrypt as many database as you want.

    >>can we have more then 1 Service master key \DB instance ?

    No, Service master Key is unique and only one per instance.

    >> am looking to enable the TDE for 2 DB , right now i am using the 1 SMK but i'm not sure who to create Database encryption key for 2'nd database.

    You can enable TDE n 2 DB's.

    You just need to create a Master Key and certificate under MASTER database and use that certificate while creating Database master key. For example,

    ---Database 1
    USE DB1;
    GO
    CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE MasterCertificate;
    GO
    USE DB1;
    GO
    ALTER DATABASE DB1 SET ENCRYPTION ON;
    GO
    ---Database 2
    USE DB2
    CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE MasterCertificate;
    GO
    USE DB2
    GO
    ALTER DATABASE DB1 SET ENCRYPTION ON;
    
    


    https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/

    --Prashanth

    • Marked as answer by IAhmer Thursday, February 12, 2015 8:37 AM
    Friday, January 30, 2015 7:19 PM

All replies

  • I also tried to restore Master key from Source server , We are using service delegate accounts under which sql services are running.

    I also provided read permission to destination service account on source directory where my key was originally placed.

    still no luck


    Thanks, Ahmer

    Thursday, January 29, 2015 1:20 PM
  • Does the SQL Server Service Account has permissions to read this file?

    - Vishal

    SqlAndMe.com

    Thursday, January 29, 2015 1:37 PM
  • Hi IAhmer,

    Please run the following script to restore master key. I note that you don't specify colon (:) after "A".

    use master
    
     RESTORE MASTER KEY
    
         FROM FILE = 'A:\SQLBackup\TDE_Master_Key.key'
    
    -- A Is the drive letter i copied the master key from Source server to this drive
    
        DECRYPTION BY PASSWORD = '1Password'
    
         ENCRYPTION BY PASSWORD = '1Password';
    
     GO

    Besides, please ensure that the name of master key  backup file is correct, maybe you have defined it as “TDE_Master_Key” but not “TDE_Master_Key.key ”?  Also as Vishal’s post, please make sure that the destination SQL Server Service account has NTFS permissions to read master key backup in 'A:\SQLBackup’.



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support




    Friday, January 30, 2015 8:58 AM
  • Thanks Lydia,

    Is there an help link available on msdn where i can get information about enable TDE for multiple DB's (SQL 2012 EE) on same instance?

    here are my questions :

    1. How many Databases can be encrypted on same instance ? 

    2. can we have more then 1 Service master key \DB instance ?

    I am looking to enable the TDE for 2 DB , right now i am using the 1 SMK but i'm not sure who to create Database encryption key for 2'nd database.

    Appreciate your help 


    Thanks, Ahmer

    Friday, January 30, 2015 11:54 AM
  • >>How many Databases can be encrypted on same instance ? 

    You can encrypt as many database as you want.

    >>can we have more then 1 Service master key \DB instance ?

    No, Service master Key is unique and only one per instance.

    >> am looking to enable the TDE for 2 DB , right now i am using the 1 SMK but i'm not sure who to create Database encryption key for 2'nd database.

    You can enable TDE n 2 DB's.

    You just need to create a Master Key and certificate under MASTER database and use that certificate while creating Database master key. For example,

    ---Database 1
    USE DB1;
    GO
    CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE MasterCertificate;
    GO
    USE DB1;
    GO
    ALTER DATABASE DB1 SET ENCRYPTION ON;
    GO
    ---Database 2
    USE DB2
    CREATE DATABASE ENCRYPTION KEY
        WITH ALGORITHM = AES_128
        ENCRYPTION BY SERVER CERTIFICATE MasterCertificate;
    GO
    USE DB2
    GO
    ALTER DATABASE DB1 SET ENCRYPTION ON;
    
    


    https://www.simple-talk.com/sql/database-administration/transparent-data-encryption/

    --Prashanth

    • Marked as answer by IAhmer Thursday, February 12, 2015 8:37 AM
    Friday, January 30, 2015 7:19 PM