none
Symetric Key issue if you Replace one DB with different Different Symetric Key name with another db

    Question

  • Hello All,

    Here is the scenario :

    Production DB name = DB , Symmetric key name = DB_ SK

    Test Database name = DB_Test ,  Symmetric key name = DB_Test_SK

    Symmetric key will NOT work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key names are different.

    However the following scenario works (Same Symmetric key name but different password) :

    Production DB name = DB , Symmetric key name = DB_SK

    Test Database name = DB_Test ,  Symmetric key name = DB_SK


    Symmetric key will work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key name are the same.

    So I would like to know why this is happening when you are using the restore with overwrite option? What is the solution?

    Is that possible to rename the Symmetric key and Certificate name and MASTER KEY ENCRYPTION PASSWORD to whatever you need after the DB Restored without any impact on data?  So far I didn’t find any resolution in Microsoft website and forums for this.

    Regards,

    Nadia

    Friday, October 18, 2013 3:01 PM

Answers

  • Hello All,

    Here is the scenario :

    Production DB name = DB , Symmetric key name = DB_ SK

    Test Database name = DB_Test ,  Symmetric key name = DB_Test_SK

    Symmetric key will NOT work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key names are different.

    However the following scenario works (Same Symmetric key name but different password) :

    Production DB name = DB , Symmetric key name = DB_SK

    Test Database name = DB_Test ,  Symmetric key name = DB_SK


    Symmetric key will work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key name are the same.

    So I would like to know why this is happening when you are using the restore with overwrite option? What is the solution?

    Is that possible to rename the Symmetric key and Certificate name and MASTER KEY ENCRYPTION PASSWORD to whatever you need after the DB Restored without any impact on data?  So far I didn’t find any resolution in Microsoft website and forums for this.

    ...

    I am not sure if I read this all correctly.

    A Restore of a database essentially replaces the whole database – not just contents of it.
    And since the symmetric key is simply content, it will be based on the backup-files content, which in your scenario is “DB” or “DB_SK”

    Renaming a key or certificate is not possible.

    What you can do, is change the “encrypting entity” for a symmetric key or the private key used to encrypt a certificate, if that is what you are asking for in the second part, which I do not completely understand.

    What are you using the key for?

    You can “transfer” certificates by backing them up and re-creating them from the file, but not keys unfortunately.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com


    Monday, October 21, 2013 1:17 PM
  • Production DB name = DB , Symmetric key name = DB_ SK

    Test Database name = DB_Test ,  Symmetric key name = DB_Test_SK

    Symmetric key will NOT work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key names are different.

    However the following scenario works (Same Symmetric key name but different password) :

    Production DB name = DB , Symmetric key name = DB_SK

    Test Database name = DB_Test ,  Symmetric key name = DB_SK
    Symmetric key will work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key name are the same.

    Hi Nadia,

    Based on my test, I create a symmetric key encrypted with a password on a database. When I restore the database with overwrite option to another database which host on different server, the symmetric key retains and work well.
    If you create the symmetric key from database master key, you should OPEN MASTER KEY using the password and then run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY enable the automatic DBMK access

    As Andreas post above, we can not rename the Symmetric key and Certificate name, but re-creates the database master key with new password.

    Reference:
    ALTER CERTIFICATE (Transact-SQL)
    ALTER SYMMETRIC KEY
    ALTER MASTER KEY
    OPEN MASTER KEY

    Regards,
    Fanny Liu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Friday, October 25, 2013 8:51 AM

All replies

  • Hello All,

    Here is the scenario :

    Production DB name = DB , Symmetric key name = DB_ SK

    Test Database name = DB_Test ,  Symmetric key name = DB_Test_SK

    Symmetric key will NOT work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key names are different.

    However the following scenario works (Same Symmetric key name but different password) :

    Production DB name = DB , Symmetric key name = DB_SK

    Test Database name = DB_Test ,  Symmetric key name = DB_SK


    Symmetric key will work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key name are the same.

    So I would like to know why this is happening when you are using the restore with overwrite option? What is the solution?

    Is that possible to rename the Symmetric key and Certificate name and MASTER KEY ENCRYPTION PASSWORD to whatever you need after the DB Restored without any impact on data?  So far I didn’t find any resolution in Microsoft website and forums for this.

    ...

    I am not sure if I read this all correctly.

    A Restore of a database essentially replaces the whole database – not just contents of it.
    And since the symmetric key is simply content, it will be based on the backup-files content, which in your scenario is “DB” or “DB_SK”

    Renaming a key or certificate is not possible.

    What you can do, is change the “encrypting entity” for a symmetric key or the private key used to encrypt a certificate, if that is what you are asking for in the second part, which I do not completely understand.

    What are you using the key for?

    You can “transfer” certificates by backing them up and re-creating them from the file, but not keys unfortunately.


    Andreas Wolter | Microsoft Certified Master SQL Server

    Blog: www.insidesql.org/blogs/andreaswolter
    Web: www.andreas-wolter.com | www.SarpedonQualityLab.com


    Monday, October 21, 2013 1:17 PM
  • Production DB name = DB , Symmetric key name = DB_ SK

    Test Database name = DB_Test ,  Symmetric key name = DB_Test_SK

    Symmetric key will NOT work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key names are different.

    However the following scenario works (Same Symmetric key name but different password) :

    Production DB name = DB , Symmetric key name = DB_SK

    Test Database name = DB_Test ,  Symmetric key name = DB_SK
    Symmetric key will work if you restore a copy of Prod DB to Test DB with overwrite option since their Symmetric key name are the same.

    Hi Nadia,

    Based on my test, I create a symmetric key encrypted with a password on a database. When I restore the database with overwrite option to another database which host on different server, the symmetric key retains and work well.
    If you create the symmetric key from database master key, you should OPEN MASTER KEY using the password and then run ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY enable the automatic DBMK access

    As Andreas post above, we can not rename the Symmetric key and Certificate name, but re-creates the database master key with new password.

    Reference:
    ALTER CERTIFICATE (Transact-SQL)
    ALTER SYMMETRIC KEY
    ALTER MASTER KEY
    OPEN MASTER KEY

    Regards,
    Fanny Liu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Friday, October 25, 2013 8:51 AM