Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
I’ve been working with encryption in SQL Server for a long time, and have delivered quite a few presentations on the topic. Recently I was updating some code and wanted to check if a database had a master key created in it. This post shows how to do that.
The DMK (Database Master Key) is a construct that lives inside a database and provides the basis for encrypting other keys. It is a symmetric key, but created with the CREATE MASTER KEY DDL.
Information about this key is stored in a couple of places. First, it appears in sys.symmetric_keys, with the name “##MS_DatabaseMasterKey##”. You hsould see this with the AES_256 algorithm.
You can also query the sys.databases DMV for the is_master_key_encrypted_by_server c0lumn, if you keep the defaults. If you run this
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY
then the sys.databases DMV will show 0, even though you still have a master key, as shown below.
SQLNewBlogger
A quick post. I was updating code to make it cleaner and realized I needed to add a check for the key. In the past, I’ve just ignored the error, but I took the chance here to refactor things and also produce a quick post.