Sql-server – How to rotate the CMK of Always Encrypted using SSMS

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.

I have yet to come across a definitive "Best Practices" list regarding multiple encryption types for SQL Server, but in your situation I would probably recommend the following:

1. Encrypt necessary data using Always Encrypt
   • Its the new hotness. You're on 2016, enjoy it!
   • Better security - the DBA can't decrypt data since the key is stored outside the database.
   • More options for tuning individual queries and reducing the performance hit of the feature.
   • Easiest to implement on both the database and app side (unless TDE is an option) since you only have to configure driver parameters versus changing your queries as with Column Level Encryption.
   • The additional decryption load becomes the application's (specifically the driver's) problem (fine for me as a DBA).
   • This feature is likely to be improved and optimized for a while since it is a new (my opinion).
2. For columns you need to search on, create a hash value to do lookups on.
   • Maintains security of the data but is very efficient for lookups. It allows you to have the performance of a searching on a normal row without the overhead of decrypting every row or defaulting to a table scan. You can index it for further performance if desired.
     • Your app will take the user input and hash it, then use that hashed value to query the hashed version stored in the table. This lookup will be fast and will allow you to only decrypt the desired row(s) versus decrypting rows you don't actually want as part of your result set.
   • Design of this depends on how many columns you need to look up and if you're using multiple or individual ones in your WHERE clauses.
   • Extra space is worth it for performance as long as you can afford it.
3. Don't bother using Column Level Encryption
   • You'll have to decrypt the entire column to do searches, which will be less efficient than an un-encrypted hash column. This means the larger your table, the more inefficient this method becomes. 1 million rows means you have to spend the CPU to decrypt a million rows even if your filter is only returning 1 row. Just because you can perform a search doesn't mean it will be performant.
   • I can see long term maintenance of two encryption features within the same table being confusing and becoming at best tribal knowledge.

You can also implement #2 with Column Level Encryption as well, but without any constraints in place, I can't see why you'd choose CLE over Always Encrypted in general.

I joke about making encryption the application's problem, but I find that the database often gets saddled with a lot more business logic and wacky functionality than it should be in most cases. If there is an option to push some computation back to the application side, I've found that is generally the best option and helps prevent troubleshooting and general tuning from becoming a nightmare.

Of course you should fully test the alternatives. If you find CLE, or a combo, works best for your particular scenario, so be it.

Additional reading:

• Always Encrypted Performance - Aaron Bertrand
• Always Encrypted Performance - A follow Up - Aaron Bertrand
• Column Level Encryption Overhead - Dallas Snyder
• Always Encrypted (Database Engine) (official documentation landing page)