locked
Master key no more encrypted - cannot restore TDE database RRS feed

  • Question

  • Hi,

    all of a sudden, the TDE encrypted databases lost synchronization in availability group.

    While attempting to add databases back after cleanup, the restore operation fails with

    System.Data.SqlClient.SqlError: Please create a master key in the database or open the master key in the session before performing this operation. (Microsoft.SqlServer.SmoExtended)

    Question: how can we possible achieve to run

    alter master key add encryption by service master key
    

    without opening master key for which I do not have password

    This results in

    Msg 15581, Level 16, State 7, Line 1
    Please create a master key in the database or open the master key in the session before performing this operation.

    On the faulty server I ran 

    select is_master_key_encrypted_by_server from sys.databases where name = 'master'
    

    as per <https://support.microsoft.com/en-us/kb/2666213>

    and got

    is_master_key_encrypted_by_server

    0

    Questions:

    Is there a workaround available to re-encrypt master key without it's password?

    What could cause such loss - excluding 'alter master key drop encryption by service master key' that nobody performed on this server?

    Thanks & Regards


    /Patrice

    Wednesday, November 9, 2016 9:06 AM

All replies

  • Hi PmNet-CH-FR,

    >>Is there a workaround available to re-encrypt master key without it's password?

    I'm afraid there's no workaround here, if you don’t have the password and it’s not encrypted by SMK I suppose you would have to drop and recreate it.

    >>What could cause such loss - excluding 'alter master key drop encryption by service master key' that nobody performed on this server?

    I can only think of two reasons:
    1. Someone issued ‘alter master key drop encryption by service master key’.
    2. The database is restored from a previous backup which you haven’t created database master key. Since you are talking about TDE I’m assuming it’s master database.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Thursday, November 10, 2016 10:09 AM
  • Hi Li,

    Many thanks for your answer.

    Regarding root cause, database has not been restored from old backup. All of the TDE databases suddenly stopped synchronization, while restoring logs as past of the availability group. There was no restore operation from an admin.

    If someone issued ‘alter master key drop encryption by service master key’, then I have to find that in SQL logs.

    The day I discovered problem, I took earlier a backup of all TDE databases on the active server without COPY_ONLY option. Could this be linked?

    Regards,


    /Patrice

    Thursday, November 10, 2016 10:26 AM
  • Hi PmNet-CH-FR,

    Did you see anything abnormal from AlwaysOn Extended Events? And also, I don’t any connection between the issue and your backup action. The issue seem to be only with your master database.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, November 13, 2016 8:39 AM
  • Hi Lin,

    We managed to open the safe and with appropriate password re-encrypt master key.

    Then TDE databases restored as expected and databases are back in AG.

    According to engineer who handled support incident, the backup is not the cause of the key chain loss. So we have to cross fingers as root cause is unknown.

    Thanks for you help,

    Regards,


    /Patrice

    Monday, November 14, 2016 9:56 AM