none
Add database to availability group: Password required RRS feed

  • Question

  • Hello,

    Password required when adding database to availability group, take.ms/QC9FA

    This database is encrypted by database master key, you need to provide valid password when adding it to the availability group.

    But "SELECT * FROM sys.dm_database_encryption_keys" shows nothing about it.

    How is that possible?

    Thanks.


    Любовь долготерпит, ...


    • Edited by Aleksey.T Friday, August 12, 2016 4:19 PM
    Friday, August 12, 2016 4:17 PM

Answers

  • Was able to add database to availability group using t-sql

    ALTER AVAILABILITY GROUP MyAG ADD DATABASE MyDb3;  
    GO 

    Bug in UI ?


    Любовь долготерпит, ...

    • Proposed as answer by Martin.CairneyMVP Wednesday, August 17, 2016 5:44 AM
    • Marked as answer by Aleksey.T Wednesday, August 24, 2016 6:29 AM
    Wednesday, August 17, 2016 5:21 AM

All replies

  • Check what sys.databases reports:

    SELECT name, is_encrypted
    FROM sys.databases ;

     

    Martin Cairney SQL Server MVP

    Monday, August 15, 2016 1:49 AM
  • Hi Martin,

    is_encrypted is 0

    By the way, we recently upgraded from SQL 2014 to SQL Server 2016.

    Thanks.


    Любовь долготерпит, ...


    • Edited by Aleksey.T Monday, August 15, 2016 9:28 AM
    Monday, August 15, 2016 9:26 AM
  • Was able to add database to availability group using t-sql

    ALTER AVAILABILITY GROUP MyAG ADD DATABASE MyDb3;  
    GO 

    Bug in UI ?


    Любовь долготерпит, ...

    • Proposed as answer by Martin.CairneyMVP Wednesday, August 17, 2016 5:44 AM
    • Marked as answer by Aleksey.T Wednesday, August 24, 2016 6:29 AM
    Wednesday, August 17, 2016 5:21 AM
  • see the below links does it give information's-

    How to enable TDE Encryption on a database in an Availability Group
    https://blogs.msdn.microsoft.com/alwaysonpro/2014/01/28/how-to-enable-tde-encryption-on-a-database-in-an-availability-group/

    How to add a TDE encrypted database to an Availability Group
    https://blogs.msdn.microsoft.com/alwaysonpro/2015/01/07/how-to-add-a-tde-encrypted-database-to-an-availability-group/


    How to configure Always On for a TDE database
    https://blogs.msdn.microsoft.com/sqlserverfaq/2013/11/22/how-to-configure-always-on-for-a-tde-database/

    Wednesday, August 17, 2016 5:27 AM
  • Hi Aleksey.T,

    Based on your description, it seems more like a SSMS issue to me. Could you please tell me your SQL Server version/edition as well as SSMS version? In this case, I would suggest you upgrade your SSMS see if the issue persists.

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

    Regards,
    Lin
    Wednesday, August 17, 2016 6:31 AM
    Moderator
  • Hi Lin,

    Updated to latest CU1, same issue:

    Microsoft SQL Server 2016 (RTM-CU1) (KB3164674) - 13.0.2149.0 (X64)   Jul 11 2016 22:05:22   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)

    Microsoft SQL Server Management Studio: 13.0.15700.28

    Thanks.


    Любовь долготерпит, ...

    Thursday, August 18, 2016 4:15 PM
  • it says two things.

    This database is already belongs to this AG.

    Also it is asking for the valid password when adding so did you try enter the valid password for the database master key in the  password coulmn.


    Friday, August 19, 2016 3:05 AM
  • Hi,

    I have same issue as this ticket mentions.

    None of DBs are encrypted so I was able to set up one db for AG which is small DB(around 1GB)

    But the bigger DB that is around 600GB says it requires the password.

    Please see the screenshot.

    I might be able to set up with TSQL but with wizard?

    Thank you a lot in advance.




    • Edited by sue0141 Thursday, September 8, 2016 9:24 PM
    Thursday, September 8, 2016 9:22 PM
  • I am also having this issue, on databases with no encryption around 100GB
    Tuesday, July 18, 2017 1:31 PM
  • The database master key needs to be password protected when the database is added to an Availability Group whenever it protects secrets within the database. This is not related to TDE.

    Although the SSMS error message calls out encryption, any secret protected with the DMK (e.g. certificate) will result in the SSMS error. See  this blog post from Jonathan Kehayias: https://www.sqlskills.com/blogs/jonathan/database-master-keys-and-availability-groups/ for instructions on how to supply the DMK password when adding the database to the AG.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Saturday, July 28, 2018 11:47 AM
    Moderator
  •    One definite cause was using SSMS 2016 on a SQL 2012 AG. Once I found a copy of SSMS 2012, the 'password required' issue went away and I was able to add the database using the GUI.

    Thursday, October 18, 2018 12:48 AM
  • The problem and solution remain the same for SQL Server 2017 (14.0.323.3) and SSMS 17.9.1.  This occurred after an initial load from backup of the database failed, as the NO RECOVERY option had been inadvertently included on the primary, and after the database had been restored a second time.  While SSMS failed, the SQL method worked without issue.
    Saturday, December 7, 2019 4:27 PM
  • I encountered this issue with accessing the 'Add Database' function from the Availability Group context while signed in to the Listener Name in SSMS.

    If you create the new database while logged into the Primary node and initiate the 'Add Database' function from the Availability Group on the Primary node, not the Listener name, it should work for you - it did for me.  Good luck!

    Friday, January 3, 2020 7:59 PM