Voice of the DBA

Category: Blog

Checking if a database has a master key–#SQLNewBlogger
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.
January 26, 2022
Daily Coping 26 Jan 2022

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to focus on what’s good on a tough day.

Most of my days are pretty good, but there are some difficult days. As I tell my kids, every job is a job some days, no matter how much you love it.

Last week I had a hard day. I wasn’t feeling well, fighting a cold (or maybe breakthrough COVID, but I don’t think so). I needed to get a few things done, I wanted to work out, and I needed rest.

I decided to focus on the fact that I felt well enough to do something, and cut back where I could. I postponed some work, did a very light yoga workout in my bedroom, and took care of the one critical thing that needed to be done.

By the end of the day, I felt better. I shortened the day and went to bed early, but I did enough to appreciate that it turned out to be a good one.

January 26, 2022
Daily Coping 25 Jan 2022

I started to add a daily coping tip to the SQLServerCentral newsletter and to the Community Circle, which is helping me deal with the issues in the world. I’m adding my responses for each day here. All my coping tips are under this tag.

Today’s tip is to get back in touch with an old friend.

Actually, I had an old friend get in touch with me. Someone I worked with years ago reached out and I responded. We actually scheduled lunch together and it was great. A very nice chance to catch up with each other’s lives and careers.

Worth the time and effort.

January 25, 2022
A Quick Data Backup

The other day I was driving in the Tesla and clicked and Spotify application and chose a playlist. In this case, it was the “This is Stevie Wonder” playlist, which my wife and I had listened to the night before. I got a “load error” on a song.

I tried another and got the same thing. I tried a third in this playlist, thinking this was some network error, but nothing worked. I switched to a different playlist and got a song to play, but when I went back to the Stevie Wonder one, it showed all the songs, but wouldn’t play.

I finally connected my phone and opened the playlist in Spotify only to see it was empty. Somehow the Spotify generated list was deleted and the app in the Tesla didn’t update to see the issue.

That got me thinking. I don’t have a lot of playlists, but I do sometimes follow and set up system playlists that I like. In the modern world where I’m sharing this data with others, I’m dependent that someone else doesn’t break things.

I looked around to see if I could export and import playlists, or copy them, but I didn’t see anything, so I decided to make my own backup. There are really only 5 or 6 of these I follow, so I created my own new playlist.

I renamed it to the band name, in this case, The Beatles. Next, I opened the Spotify list, selected the songs, and then dragged them over.

I did this for the 5 or 6 “This is xxx” lists and then removed those Spotify ones from my list. If those get updated, I won’t know, but that’s not critical here. If I find something missing, or want to add something, I can. In fact, my “John Mayer” list now has “83”, which was missing from the Spotify list.

Another case where a quick manual task works well and likely is better than a lot of code work. Here, that would probably be a lot of research to find something that might not even exist.

January 24, 2022