TDE after restoring to a Different Instance

  • Hi Experts,

    I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.

    The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.

    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

    I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.

    Msg 15151, Level 16, State 1, Line 2
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

  • Apparently the master key does not exists in the database  which you are trying to alert.

  • VastSQL - Thursday, February 2, 2017 12:27 AM

    Hi Experts,

    I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.

    The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.

    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

    I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.

    Msg 15151, Level 16, State 1, Line 2
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

    No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle - Friday, February 3, 2017 10:32 AM

    VastSQL - Thursday, February 2, 2017 12:27 AM

    Hi Experts,

    I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.

    The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.

    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

    I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.

    Msg 15151, Level 16, State 1, Line 2
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

    No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate

    Thanks Perry,

    But when running select name from sys.symmetric_keys

    getting below results.

    ##MS_DatabaseMasterKey##
    ##MS_ServiceMasterKey##

  • I think you are referencing two different things. For TDE there needs to be a database master key in the master database of the instance. Your query is looking for a database master key in user database Encrypt_DiffCert.

    The cert will have to exist in the new instance, otherwise the instance would not be able to access the DEK to decrypt the data files, making the database useless on the new instance.

    Joie Andrew
    "Since 1982"

  • here is a video TDE demo, that i created while ago.

    https://www.youtube.com/watch?v=8jJV08p0FwU

  • Joie Andrew - Sunday, February 5, 2017 2:00 PM

    I think you are referencing two different things. For TDE there needs to be a database master key in the master database of the instance. Your query is looking for a database master key in user database Encrypt_DiffCert.

    The cert will have to exist in the new instance, otherwise the instance would not be able to access the DEK to decrypt the data files, making the database useless on the new instance.

    Thanks  Joie,

    The Certificate do exists and the example is taken from msdn. Below they use AdventureWorks2012 DB as example.

    USE AdventureWorks2012;  ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'dsjdkflJ435907NnmM#sX003';  GO  

  • What example from MSDN? Can you provide the link?

    Joie Andrew
    "Since 1982"

  • VastSQL - Saturday, February 4, 2017 10:26 PM

    Perry Whittle - Friday, February 3, 2017 10:32 AM

    VastSQL - Thursday, February 2, 2017 12:27 AM

    Hi Experts,

    I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.

    The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.

    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

    I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.

    Msg 15151, Level 16, State 1, Line 2
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

    No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate

    Thanks Perry,

    But when running select name from sys.symmetric_keys

    getting below results.

    ##MS_DatabaseMasterKey##
    ##MS_ServiceMasterKey##

    what do the results of these show

    USE master
    GO

    select * from sys.symmetric_keys

    select * from sys.certificates

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle - Monday, February 6, 2017 8:24 AM

    VastSQL - Saturday, February 4, 2017 10:26 PM

    Perry Whittle - Friday, February 3, 2017 10:32 AM

    VastSQL - Thursday, February 2, 2017 12:27 AM

    Hi Experts,

    I have a TDE enabled database and i successfully restored it to another instance of SQL after restoring the certificate along with private key.

    The certificate I restored is from another SQL Server and the certificate is encrypted by DMK of other server so now there is no relation between DMK and certificate in new server right,Please correct me if I am wrong.

    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

    I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.

    Msg 15151, Level 16, State 1, Line 2
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

    No, it's telling you you haven't created a DMK on your new instance. You must create a DMK in master on your new server before attempting to restore the TDE certificate

    Thanks Perry,

    But when running select name from sys.symmetric_keys

    getting below results.

    ##MS_DatabaseMasterKey##
    ##MS_ServiceMasterKey##

    what do the results of these show

    USE master
    GO

    select * from sys.symmetric_keys

    select * from sys.certificates

    Thanks Perry.

    name
    ##MS_DatabaseMasterKey##
    ##MS_ServiceMasterKey##

    Name
    ##MS_SQLResourceSigningCertificate##
    ##MS_SQLReplicationSigningCertificate##
    ##MS_SQLAuthenticatorCertificate##
    ##MS_AgentSigningCertificate##
    ##MS_PolicySigningCertificate##
    ##MS_SmoExtendedSigningCertificate##
    ##MS_SchemaSigningCertificate990F36EF1B3577FE5687C7465F0A5135DE9E6834##
    MyServerCertificate
    My_TDE_Certificate

    Please let know if full result set is required.

  • so have you actually managed to restore the TDE protected database to the instance?
    Are you using the My_TDE_Certificate to encrypt the DEK in your TDE protected database.

    Post the output of the following if the database has been restored and is online

    USE [TDE_Database]

    go

    SELECT dek.encryption_state, c.name, c.subject

    FROM master.sys.dm_database_encryption_keys dek

    INNER JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint

    WHERE dek.database_id = DB_ID()

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle - Tuesday, February 7, 2017 10:13 AM

    so have you actually managed to restore the TDE protected database to the instance?
    Are you using the My_TDE_Certificate to encrypt the DEK in your TDE protected database.

    Post the output of the following if the database has been restored and is online

    USE [TDE_Database]

    go

    SELECT dek.encryption_state, c.name, c.subject

    FROM master.sys.dm_database_encryption_keys dek

    INNER JOIN master.sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint

    WHERE dek.database_id = DB_ID()

    Thanks Perry.

    encryption_state    name    subject
    3    MyServerCertificate    My TDE Certificate

  • So, why are you trying to use the following, what are you hoping it to do, given there is no database master key in the database [Encrypt_DiffCert]

    VastSQL



    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

    I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.

    Msg 15151, Level 16, State 1, Line 2
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.  

    Did you maybe want the alter database encryption key command as below
    -- Syntax for SQL Server 
     
    ALTER DATABASE ENCRYPTION KEY 
          REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } 
       | 
       ENCRYPTION BY SERVER  
        { 
            CERTIFICATE Encryptor_Name | 
            ASYMMETRIC KEY Encryptor_Name 
        } 
    [ ; ] 

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Perry Whittle - Wednesday, February 8, 2017 6:23 AM

    So, why are you trying to use the following, what are you hoping it to do, given there is no database master key in the database [Encrypt_DiffCert]

    VastSQL



    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

    I tried to do the above but got below error. I believe the above TSQL regenerate the certificate from other server using this servers DMK.

    Msg 15151, Level 16, State 1, Line 2
    Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.  

    Did you maybe want the alter database encryption key command as below
    -- Syntax for SQL Server 
     
    ALTER DATABASE ENCRYPTION KEY 
          REGENERATE WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY } 
       | 
       ENCRYPTION BY SERVER  
        { 
            CERTIFICATE Encryptor_Name | 
            ASYMMETRIC KEY Encryptor_Name 
        }  
    [ ; ] 

    Thanks Perry,

    DMK is only for the master database right,correct me if I am wrong. Also what is the relation between an encrypted database from another server restored to a new  server,we are only restoring the certificate right, there is  no connection between DMK and the restored certificate right or am I missing  something here?

  • VastSQLThanks Perry,

    DMK is only for the master database right,correct me if I am wrong.

    Wrong, that's why it's important to create a DMK in the master database when setting up TDE, it's the DMK in master that's used for decrpyting the private key of a certificate stored in master used to protect a TDE enabled database.

    VastSQLAlso what is the relation between an encrypted database from another server restored to a new server,we are only restoring the certificate right, there is no connection between DMK and the restored certificate right or am I missing something here?

    When restoring a TDE enabled database from server A to server B, you only need to transfer the backup of the database and the certificate used to encrypt the Database Encryption Key in the TDE enabled database.

    However, to create a certificate on server B from a backup, you will first need to create a DMK in master on server B as these are not created automatically whereas the SMK  is created automatically upon first usage.

    You still haven't answered my question, why are you using the following command, what did you expect it to do?

    USE Encrypt_DiffCert;
    ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'Niranjana123';
    GO

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

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

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