SSMS 2016, Availability Groups, database is encrypted error

Question

While evaluating possibility for rolling upgrade of SQL Server 2012 instances hosting AGs to SQL Server 2016 we ran into strange issue that does not quite make sense. One of the easier way to demonstrate is as follows:

Let’s say you want to add database to AG (2012 instance is being used). SSMS 2012 or SSMS 2014 wizard shows no surprises:

SSMS 2016 however tells completely different story:

For databases that are already part of AG it says “Does not meet prerequisites” instead of “Already part…” and for those not in AG it says “Password required” with the following explanation:

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

Problem with this message is that none of the databases use any encryption.

Same issue manifests itself if you restore database from 2012 AG backup on 2016 instance and try to add it to 2016 AG using SSMS 2016 wizard.

Answer 1

Problem with this message is that none of the databases use any encryption.

That is probably true :) However, that's not what the error is saying...

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

The error is saying, in plain English: You have a databases that has, inside of it, a database master key. That database master key is encrypted using a password. I need you to give me that password so that I can decrypt any objects that might be encrypted using it.

Here is a super quick repro to give you the "message":

CREATE DATABASE PasswordTest;
GO

USE PasswordTest;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$up3r$tr0ng’
GO

SMSS isn't saying it can't use the database, it's asking for the password to open the master key.

What's the fix?

You have a few options, but here are the best two:

1. Click to the right of the message, you can actually put in the password and the wizard will let you continue without issue (since it can now open the master key).
2. If you're not using the master key... Drop It.

---

It turned out that database did have master key.

Yes, as the error specified. This was not in doubt :)

screenshots in my initial question are from the very same 2012 instance. Why SSMS 2012 and 2014 wizard did not mention anything about master key, but SSMS 2016 wizard did?

It was added in SSMS 2016... Not sure what you were looking for here as items are added to new versions.

And also SSMS 2016 did not have any edit box to enter password.

I dunno - works in mine...

I know exactly what you refer to, but there is nothing there in my case, just empty space under "Password" column, which added to confusion. Do you think there might be a bug in SSMS 2016 or you have some explanation for this behavior?

See the screenshot above - I think it's working as intended, at least for me... It might not be intuitive, but it works.

The end result is the same. If you're not a fan of the GUI giving you grief then there is always PowerShell and T-SQL :) If you feel that this isn't working as intended or you don't like it, I'd ask you to post feedback on Connect.

Answer 2

Complementing the answer by Sean Gallardy:

Click to the right of the message, you can actually put in the password and the wizard will let you continue without issue (since it can now open the master key).

(face-palm) ... Yes, you can click on the invisible space to the right and enter the password... BUT WAIT... you must also click the refresh button at this point! (usability fail :)