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.

2022-01-25 12_08_37-SQLQuery2.sql - ARISTOTLE.EncryptionPrimer (ARISTOTLE_Steve (55))_ - Microsoft S

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.

Posted in Blog | Tagged , , , | 1 Comment

Defining DevOps

DevOps has become somewhat mainstream in that many companies, perhaps most, are talking about it or trying to implement some of the principles behind this software development approach. However, even as I talk with people that practice DevOps, I find the idea of what DevOps actually includes can vary widely among developers, even inside the same company.

There are lots of definitions, and this one caught my eye recently. It says, among other things, that there should be “increased collaboration among not only developers and operations staff but users.” I like that there is some emphasis on working together, which is often the hardest part of changing how we build software. I find that many customers struggle because they end up adopting the technology tools, but fail to change how they communicate with others. Culture change is hard, and DevOps requires a culture change.

The article is high level, but it does highlight some of the complexities of DevOps, mainly that lots of stuff is happening at once. Good reporting, tracking, and management are important, and unfortunately, lots of managers don’t want to change their approaches to managing teams, which further complicates the process of building better software faster. Certainly, the need to have developers work with ops people, including supporting production systems, is important, but it never happens without management requiring and supporting this collaboration.

The drawbacks to DevOps are many. Plenty of teams slap together code, getting the “faster” part but forget the “better” part, which means increasing and higher quality code over time. They embrace new tools, but they sometimes forget to run small experiments and change their approaches across time when they find better ways of building and operating code. Or they refuse to stick with current techniques or software because they crave change. Developers also find that some groups, like security and compliance departments, aren’t ready for the pace of change, which can be frustrating.

Ultimately, DevOps is better collaboration, getting all teams on board working together. Like many people rowing a boat, every group needs to align their work with others to ensure a smooth and successful journey. Actually creating that alignment requires strong leadership and culture, two of the hardest, and rarest, things to build inside many organizations.

Steve Jones

Listen to the podcast at Libsyn, Stitcher, Spotify, or iTunes.

Posted in Editorial | Tagged | Comments Off on Defining DevOps

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.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 26 Jan 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.

Posted in Blog | Tagged , , | Comments Off on Daily Coping 25 Jan 2022