Do I have a Database Master Key in a database? #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

How can I tell if I have a Database Master Key in a database? It’s actually easy. I query the sys.symmetric_keys DMV for data. If I get a result that has a name of ##MS_DatabaseMasterKey##, then I have a database master key.

You can see this below. I’ve queried this DMV in my Sandbox database.

2017-01-11 12_21_55-SQLQuery1.sql - (local)_SQL2014.Sandbox (PLATO_Steve (59))_ - Microsoft SQL Serv

Now, what if I go to another database, say the Finances database. I see nothing.

2017-01-11 12_22_34-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

Let’s add a master key here and then query. Note, I am not disclosing the real password here. Never do this, even in test systems.

2017-01-11 12_23_14-SQLQuery1.sql - (local)_SQL2014.Finances (PLATO_Steve (59))_ - Microsoft SQL Ser

This instance has been used with TDE, so if I go to master, I’ll get this:

2017-01-11 12_24_20-SQLQuery1.sql - (local)_SQL2014.master (PLATO_Steve (59))_ - Microsoft SQL Serve

You can see that I not only have a DMK, I have a Service Master Key (SMK), which protects the instance.

When I create my DMK, the only parameter I can provide is a password, after the optional “ENCRYPTION BY PASSWORD” keywords. I don’t name it, so I can count on the naming being fairly consistent. I don’t think that the name would change from version to version, but it could.

I’d prefer that MS not create magic numbers or names, and instead, add a column to the DMV that denotes this is a DMK.

References

sys.symmetric_keys – https://msdn.microsoft.com/en-us/library/ms189446.aspx

CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx

Connect Item to add a flag – https://connect.microsoft.com/SQLServer/feedback/details/3118588

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s