Voice of the DBA

Tag: encryption

  • New Solutions for Old Problems–T-SQL Tuesday #87

    tsql2sday-300x300This is an interesting T-SQL Tuesday this month. For #87, we have an invitation from Matt Gordon. The topic is using new tools to solve old problems. The “new” cutoff is SQL Server 2014, so we’d looking at a way that the last two versions of SQL Server have helped solve an old problem.

    This is the monthly blog party that picks a topic and has everyone with a blog writing on the topic. You can do that, too. Just add an entry on your blog on Tuesday, February 14. Or start a blog and join in.

    Old Problems

    I’ve got no shortage of those, from my current and past jobs. However, in my current job as editor of SQLServerCentral, we use SQL Server 2008. I’ve got a few problems, but they’d be solved by SQL Server 2012, so those don’t qualify. Perhaps there’s some improvement in SQL Server 2016 with AGs that might work well for us, but I haven’t really looked since the pressing items are SQL Server 2012+ ones.

    However, there is an issue I’ve had in a previous job that was a problem. SQL Server 2016 provides a great solution that I wish would have been available in SQL Server 2005+.

    I once worked for a financial securities company where we had multiple clients in a single database. Each of these clients managed a portfolio of their own, and we stored the data and provided an application that limited their access to sensitive data. We did this with a series of views and procedures designed to check the clientID against the logged in user. The original person designing this has limited database experience, and ended up putting the client ID in almost every table. While that worked OK, it limited flexibility and we had issues when there were two clients from the same company that needed to manage the same portfolio. They’d end up sharing a login because we couldn’t handle flexible security.

    Enter SQL Server 2016 Row Level Security. This would have been a perfect solution as we could have limited the access to data based on the client login, as well as a predicate function that we wrote. Because of the flexibility of writing this function and having it follow the user around without requiring joins to the table being queried, we could have more easily implementing flexible security to rows of data without drastic alterations of our database design.

    Actually, these days I wouldn’t have recommended SQL Server 2016, but rather Azure SQL Database, using small, separate databases for each client, with RLS implemented for the various employees that needed to manage separate portfolios. A simple join table referenced in our security predicate would allow us to limit access without burdening developers to build new views or checks in stored procedures that correctly enforced our security model.

    I think RLS is the best security feature in SQL Server 2016, and while I wish it had been implemented in previous versions, I’m glad it’s been added to SQL Server 2016.

  • Using a DMK in TDE

    When you setup TDE, you need to create a DMK (Database Master Key) in the master database if one doesn’t exist. This key forms the basis of a chain of protection for your data inside of the SQL Server instance. The hierarchy is:

    • The DMK exists in the master database. This protects a certificate or asymmetric key by encrypting it.
    • The certificate in turn encrypts the DEK in a database using TDE. This certificate is required to open the DEK in the database, or open the DEK during a restore operation.
    • The DEK encrypts the data.

    There is only one DMK in each database, so the DMK in master for TDE could be used to protect multiple certificates, asymmetric keys, or symmetric keys that exist in master for use by TDE or any other encryption mechanism.

    A particular DMK is not needed to restore a TDE database on another instance. Rather the new instance of SQL Server should have a DMK created in the master database. Then the backup of the certificate is restored on the other instance, protected by the DMK there. Once this is complete, the TDE database can be restored.

    If you’d like to know more about what a DMK is, I’ve written about it in a previous post.

  • 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.

  • 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.