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.

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

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.

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

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

Filed under: Blog Tagged: encryption, security, SQLNewBlogger, syndicated