I've restored a database in my Dev environment (SQL version 14.0.1000.169) and I'm using the wizard to create a "Availability Groups", when I try to select the database, I get a pop up "This database is encrypted by database master key, you need to provide valid password when adding it to the availability group".

So I ran the script below, which returned 1 row.

SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

which I assume means that there is a master key created, and from what I gathered from the web, you can see what databases use the master key with the following script:

SELECT d.is_master_key_encrypted_by_server FROM sys.databases AS d where d.name = 'MyDB'

which returns 0 for my database.

So, of course I ran: drop master key;

The question is: What's the proper way to see if the master key is needed (if anything is using it)? How do test I if broke something.

  • 1
    If anything was protected by the master key, you'd get an error. – Sean Gallardy Jan 31 '18 at 13:19
  • the error would occur when I try dropping the master key? – Tomasz Jan 31 '18 at 13:26
  • 1
    Yes, if it protected anything. – Sean Gallardy Jan 31 '18 at 14:04

Your Answer

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

Browse other questions tagged or ask your own question.