Voice of the DBA

Category: Blog

  • What is a Database Master Key?

    The encryption mechanisms in SQL Server are interesting, and they work well, but they are somewhat poorly named. I ran across a few people struggling to understand, so I decided to cover the concepts in a series of posts. This one looks at the Database Master Key (DMK).

    Not the Master Database Master Key

    This is one of the more poorly named objects in the SQL Server platform. Or perhaps the “master” database is the one that is not named well. In any case, the DMK has nothing to do with the master database. Instead, the DMK is the base encryption key inside of a database. This is the key that secures all other keys

    There can be a DMK in each database that you have, including master. For some features, such as TDE, you must create a DMK in the master database. For others, you would create a DMK inside of the user database.

    Protecting a DMK

    By default the DMK is encrypted and protected by the Service Master Key (SMK), which is the key that protects the instance. This means when a database is opened and used, the service account can decrypt the SMK and use that key to decrypt the DMK. You can optionally also protect the DMK with a password.

    Even more optionally, you can break the encryption link between the SMK and DMK. In this way, you could need a password on the DMK, which would have to be entered each time a user wanted to use a key protected by the DMK.

    You need to ensure the password for the DMK is protected and available, as you will need it if you restore the database to another instance.

    To use the DMK, an account needs the CONTROL permission on the database.

    The DMK is a symmetric key. It is uses the AES_256 algorithm in SQL 2012+. Prior to that it was with Triple DES.

  • Using Out-GridView To Pick Parameters–#SQLNewBlogger

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

    I was editing an article on PoSh and learned something cool. I can use Grid-View to surface an object in a pipeline and then change the items passed through to the next command.

    Here’s how it works. Suppose I have a simple comment, like Get-SqlDatabase. I can run this and get a series of database. (command and output shown here).

    Get-SqlDatabase –ServerInstance .\SQL2014

    2017-01-03 12_50_20-powershell

    I get a bunch of data, but I don’t want all the items. For example, maybe I just want the “SimpleTalk” databases.

    Get-SqlDatabase -ServerInstance .\SQL2014 | Where-Object { $_.Name -like ‘SimpleTalk*’}

    2017-01-03 12_51_42-powershell

    I don’t want to edit this Where-Object clause all the time. I want something simple to easily fix this.

    Enter Out-GridView

    Instead of changing my filter, I can do this graphically. I can pipe my output to Out-Gridview, and I’ll get this.

    2017-01-03 12_54_44-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

    All of my output data appears. No big deal, right? Is this useful? It sure is. Look at the filter item above. I can type in there, and my data is filtered.

    2017-01-03 12_55_29-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView

    How can I use this? Suppose my code is this:

    PS SQLSERVER:\SQL\Plato\SQL2016\Databases> Get-SqlDatabase -ServerInstance .\SQL2014 | Out-GridView -PassThru | Backup-SqlDatabase -CompressionOption Default –Script

    Here I’m getting a list of databases, passing them to Out-GridView, and then sending the results to Backup-SqlDatabase, which will return a script to back up all my databases.

    I’ll run this, and then enter a filter in the grid.

    2017-01-03 12_57_47-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

    I can now highlight all these rows (if that’s what I want).

    2017-01-03 12_59_11-Get-SqlDatabase -ServerInstance ._SQL2014 _ Out-GridView -PassThru _ Backup-SqlD

    This time I have an “OK” button in the lower right corner of the grid.

    2017-01-03 12_58_23-Movies & TV

    When I click “OK”, my filtered list is returned to the pipeline and send to the backup command.

    2017-01-03 12_59_32-powershell

    This is a quick way to work with the parameters in your pipeline in an ad hoc way. Use Out-GridView to filter and select the rows you want to return to the rest of your script.

  • Quick SQL CLone Support

    I have been playing with SQL Clone for a few weeks now and liking the product. I’m looking forward to using this more and more, especially for testing ideas in development. However, there are times I have questions or am unsure of how to use things.

    I work for Redgate Software, so I have an in. I can certainly email developers if I need to, but we also have this:

    2017-01-16 14_29_03-sqlclone _ Redgate Slack

    There’s a great Slack channel where I can quickly reach developers. This comes in handy as it seems various people at Redgate pay more attention to Slack than email or other communication methods.

    No, this post won’t get you on our Slack channel.

    However, when opening the SQL Clone beta, I see this dashboard.

    2017-01-16 14_26_55-SQL Clone

    Notice the little circle in the lower right? Click it and you get this:

    2017-01-16 14_32_14-SQL Clone

    Apparently there’s a semi-live chat system that our developers are using. Chris is actually one of the SQL Clone developers, and he helped me solve the registry issue a few weeks ago.

    That’s pretty cool. If you type something in there, it will get sent to the developers, and if they’re around, they will respond. I sent a test, with a semi-legitimate question, and see this below my text:

    2017-01-16 14_34_39-SQL Clone

    Given I sent the note late in the afternoon, I’m sure all developers are gone for the day. I can hide the popup, and then will see when someone responds.

    Update: in the am, I got an email from the system, at the same time, I got a reply in my SQL Clone dashboard. As you can see, my question was answered.

    2017-01-17 08_29_47-SQL Clone

    If I click the arrow in the upper left, I can see all my conversations, which is handy if I’m trying to report or work on a few issues.

    2017-01-17 08_29_53-SQL Clone

    That’s pretty cool, and it’s an neat way to get quick feedback. Assuming it doesn’t take too much time away from developers actually developing the product.

    I suspect we’ll get support involved here, with escalations to developers. At least, that’s what I’d do, but for now, it’s good that our developers are responding to beta users and helping them use and test the product.

  • Getting My Alias in #SQLPrompt

    I got a tweet after my SQL Prompt formatting piece that said a user would be interested in upgrading from v5 if “as ‘Alias’” was transformed into “’Alias’ = “.

    Well, in v7.3, this is in there, and I didn’t realize it. You can clearly see this in the formatting actions options, which is a place I didn’t think to look.

    2017-01-17-09_00_38-sql-prompt-options

    Does this work? It sure does. Let’s examine a query you might get from a VCS or a colleague. Suppose you’re working in the new WideWorldImporters database.

    2017-01-04-08_39_52-SQLQuery3.sql-local_SQL2016.WideWorldImporters-PLATO_Steve-74_-Microso.jpg

    You prefer to see the actual columns returned in the result set at the front of the query, with an alias = format. That’s what I prefer as well, and so I want to have the column list shown as:

    PrimaryContactFullName = p.FullName,
    PrimaryContactPreferredName = p.PreferredName

    A quick CTRL+K, Y and I see this.

    2017-01-17-09_11_08-sqlquery18-sql-local_sql2016-wideworldimporters-plato_steve-70_-micros

    My aliases have moved.

    This is a great feature for those of you that need consistent formatting. There are other alias options if you prefer the alias at the end.

    Update: I originally had this as single quoted aliases, but as pointed out, that format is deprecated, so I wouldn’t use it.

    And if you want to change this back, you can have a second formatting preference that you choose to reformat code before sending it back to someone else.

    Give SQL Prompt a try today.