Voice of the DBA

Category: Blog

  • What’s a Database Encryption Key (DEK) in TDE

    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 Encryption Key (DEK).

    The Purpose of the DEK

    The DEK is designed to actually encrypt and decrypt the data in your mdf/ldf/ndf/backup files when you use TDE. This key is passed to the AES algorithm and allows SQL Server read a block of encrypted text from disk and decrypt it before placing it in memory. This same process is reversed, with data from memory being encrypted before being written to disk.

    The DEK is created in a database, specifically for use with TDE and isn’t used in any other encryption process. The DEK also cannot be backed up, or restored, except with a database restore.

    Protecting the DEK

    The DEK is protected with a certificate. This can be a purchased or self-signed certificate, but in either case, the certificate must reside in the master database of the instance hosting the TDE encrypted database.

    When a DEK is created, the DDL requires that either a certificate or asymmetric key is specified as protecting the DEK. SQL Server will use this certificate (or asymmetric key) to decrypt the DEK when the database is first opened and then uses the DEK to encrypt and decrypt all data.

    Without the certificate that has encrypted the DEK, the SQL Server instance cannot access any data encrypted by the DEK.

    That’s about it. The DEK simply provides encryption for TDE, and exists only inside of a particular database that will use TDE.

  • Querying My Named Instance in PoSh–#SQLNewBlogger

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

    I was looking at some sample code the other day and it looked like this.

    cd sqlserver:\sql\localhost\default\databases

    This allows you to browse the list of databases on your local instance. However, this is for a default instance, which I don’t have on this host. How can I get to a named instance? Usually I connect as .\SQL2016, so where does that fit in PowerShell?

    The format you see above is for the SQLServer provider, which is provided as part of the SQLServer PoSh module (or SQLPS if you haven’t adopted the new cmdlets).

    If I start at the SQLServer:\ node, I see this:

    2017-01-03 15_31_37-powershell

    Let’s drop into the SQL node and see that.

    2017-01-03 15_32_32-powershell

    At this point, I see my localhost and my local computer name. These are really the same thing. We can see that by querying each of them.

    2017-01-03 15_33_12-powershell

    In my case, I like to type fewer characters, so I’ll look at the Plato node. If I change here, I can also change to an instance, as shown here.

    2017-01-03 15_34_21-powershell

    Now I can see the databases by changing to that path and getting the directory.

    2017-01-03 15_34_31-powershell

    Thus, we can see that to query an instance, we use this path:

    SQLServer:\SQL\Host\instance

    If you want to use the default instance, then use “default”.

    A simple query path, but one that some people might wonder about, substituting the named instance for localhost, and not realizing that “Default” means just the default instance.

  • Useful #SQLPrompt Tips

    One of the tools that Redgate write is SQL Prompt. This might be my favorite product, and I’m constantly impressed by the improvements that the dev team releases. In fact, I’m also impressed by the rate at which they produce changes, with updates happening every week or two.

    At this point, the product has a lot of features, some of which I’ve forgotten about, and a few that slipped by me. At SQL in the City streamed last year, I watched a short piece from Carly Meichen that taught me a couple things. I’ll write about a few of them, but take a look at this video and see if any of these will help you become more productive.

  • Installing dbatools on WS2012 R2

    This is actually a simple, short post, but writing helps me remember things, and since I had to learn this, I decided to write something short.

    I have a number of Windows Server 2012 R2 VMs. I need to get rid of these and upgrade to WS2016, since installing SQL 2016 is a pain on these VMs. However, for the time being, I’m stuck with them.

    I was looking to try some of the dbatools modules. I popped open an elevated command prompt and

    2017-01-20 09_30_50-Atlas Home Lab .201 - VMware Workstation

    This is because that module isn’t in this version of PoSh. I checked, and I could see this is v4, and Install-Module was added in v5.

    2017-01-20 09_31_06-Atlas Home Lab .201 - VMware Workstation

    I had to go to the download page to see what my options were. One of these is to download the modules myself, but that seems like a pain. Instead, I used this command:

    Invoke-Expression (Invoke-WebRequest -UseBasicParsing https://dbatools.io/in)

    That worked great, and I had the modules installed.

    2017-01-20 09_44_05-Atlas Home Lab .201 - VMware Workstation

    Nothing much new in this post that isn’t in the dbatools download page, but I did learn a bit more about PoSh and its versions. Since I’ve gotten used to working on Windows 10, it’s good to remember that previous versions of the OS will have different capabilities.

    In this case, the pain of using WS2012R2 means I’ll try to find time and upgrade to WS2016 as soon as I can.