7

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:

enter image description here

SSMS 2016 however tells completely different story:

enter image description here

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.

  • When you restore the 2012 DB backup to 2016, do you change the compatibility mode to 2016 or leave it at 2012? I've seen this when dealing with SSISDB on SQL 2014 with SSMS 2016. With SSISDB it's a known issue with SSMS 2016 that you can only use SSMS 2016 with SSIS 2016. Previous versions don't play nice. Item 3 in the following article: docs.microsoft.com/en-us/sql/ssms/… – Kris Gruttemeyer Apr 7 '17 at 19:39
  • I experienced the same issue and used tsql to set this up instead of GUI, worked fine. There was no master key but I was still getting the same error message. – SqlWorldWide Apr 7 '17 at 22:52
  • @Kris: I tried both, does not make any difference. – Eternum Apr 8 '17 at 20:22
  • @SqlWorldWide: True, T-SQL works, I tried that also. What is puzzling though is that after I add database to 2016 instance using T-SQL, SSMS 2016 shows newly added database as "Does not meet prerequisites" instead of "Already part of this availability group". Something is still horribly wrong. Question is -- what? – Eternum Apr 8 '17 at 20:27
  • @Eternum "Already part of this availability group" in primary or secondary? Does it show up in sys.databases? – SqlWorldWide Apr 8 '17 at 22:39
7

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...

enter image description here

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.

  • Thank you for the detailed comment. It turned out that database did have master key. I have to dig into this and figure out why it was there to begin with. I dropped master key and SSMS 2016 showed database as "Meets prerequisites". So, that part is solved. But what still makes me wonder is this. Both 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? This is what mainly confused me. And also SSMS 2016 did not have any edit box to enter password. – Eternum Apr 8 '17 at 20:58
  • 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? – Eternum Apr 8 '17 at 20:58
  • @Eternum updated to reflect your comments. – Sean Gallardy Apr 8 '17 at 21:29
0

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 :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.