Voice of the DBA

Tag: encryption

Do I have a Database Master Key in a database? #SQLNewBlogger

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

How can I tell if I have a Database Master Key in a database? It’s actually easy. I query the sys.symmetric_keys DMV for data. If I get a result that has a name of ##MS_DatabaseMasterKey##, then I have a database master key.

You can see this below. I’ve queried this DMV in my Sandbox database.

Now, what if I go to another database, say the Finances database. I see nothing.

Let’s add a master key here and then query. Note, I am not disclosing the real password here. Never do this, even in test systems.

This instance has been used with TDE, so if I go to master, I’ll get this:

You can see that I not only have a DMK, I have a Service Master Key (SMK), which protects the instance.

When I create my DMK, the only parameter I can provide is a password, after the optional “ENCRYPTION BY PASSWORD” keywords. I don’t name it, so I can count on the naming being fairly consistent. I don’t think that the name would change from version to version, but it could.

I’d prefer that MS not create magic numbers or names, and instead, add a column to the DMV that denotes this is a DMK.

References

sys.symmetric_keys – https://msdn.microsoft.com/en-us/library/ms189446.aspx

CREATE MASTER KEY – https://msdn.microsoft.com/en-us/library/ms174382.aspx

Connect Item to add a flag – https://connect.microsoft.com/SQLServer/feedback/details/3118588

January 13, 2017
Checking Permissions for Keys–#SQLNewBlogger
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I got a call from someone wanted to check how permissions were stored for encryption objects. I ran a quick double check for them and decided to write this short post.

Let’s say that you create a few encryption keys. In my case, I’ll use this code to create a symmetric key, an asymmetric key, and a certificate.
```
CREATE SYMMETRIC KEY MySalaryProtector
WITH ALGORITHM = AES_256,
    IDENTITY_VALUE = 'Salary Protection Key',
    KEY_SOURCE = N'Keep this phrase a secr#t'
ENCRYPTION BY PASSWORD='Us#aStrongP2ssword';
GO

CREATE ASYMMETRIC KEY HRProtection
WITH ALGORITHM = RSA_2048
ENCRYPTION BY PASSWORD = 'Use4SomeStr0ngP@ssword%^';

GO

CREATE CERTIFICATE MySalaryCert
ENCRYPTION BY PASSWORD = N'UCan!tBreakThis1'
WITH SUBJECT = 'Sammamish Shipping Records',
    EXPIRY_DATE = '20161231';
GO
```
I do this, I have these objects.

Let’s now grant rights to these objects. I’ll use this code to grant CONTROL to a user.
```
GRANT CONTROL ON SYMMETRIC KEY::MySalaryProtector TO JoeDBA

GRANT CONTROL ON ASYMMETRIC KEY::hrprotection TO JoeDBA

GRANT CONTROL ON CERTIFICATE::MySalaryCert TO JoeDBA
```
Once I do this, I should see permissions, right? Let’s check.

I don’t see any permissions in the dialog above. That’s not exactly what I’d want to see. After all, if I’m trying to determine why a user can’t access a certificate, I’d want to know if they had rights here.

Instead of this, I need to use T-SQL, and check for specific classes in sys.database_permissions. Here’s the query looking for class 24 (symmetric keys), 25 (certificates) and 26 (asymmetric keys).

You can see that I have permissions in here, and if I check the principal_id, I’ll find these are for my user. I could also join to database_principals and get specific information for my user.

#SQLNewBlogger

This took a bit longer as someone asked me a question and I didn’t know the answer. I had to dig and read some documentation, but I found some answers and documented things myself.

Learned something, showed it, and hopefully will remember it from now on.
December 29, 2016
Post TDE–Getting Unencrypted Backups

I saw a question posted recently about someone that had disabled TDE and was still having issues restoring a backup. This doesn’t seem like that should be an issue, but it can be. A little testing shows how.

Let’s assume I have an encrypted TDE database. If I run a query, I can see the status as 3, which is encrypted.

If I take a backup at this point, the backup will be encrypted, and to restore this on another instance, I’d need to first restore the certificate. I don’t want to do that, so let’s remove encryption. This is a simple command

ALTER DATABASE TDE_Primer

SET ENCRYPTION OFF;

This runs quickly.

If I now query for encryption, I see this.

A one means that this is an unencrypted database, but a DEK (Database Encryption Key) exists. If I were to detach and examine this database file with a hex editor, the pages would be decrypted.

I’ll now take a backup and move that to another instance. Once I’ve copied that over, I’ll try to restore the backup. In T-SQL, I’ll see this:

Why is this? The database was decrypted, as was the backup. In fact, if I open my backup file in a hex editor, I can see row data.

The Problem

When SQL Server goes to restore the file, it reads part of the header. In here, the process must detect the DEK and try to decrypt that key. However, since this new instance does not have the certificate, this doesn’t work and an error is thrown, despite not needing the key since the data isn’t encrypted.

The issue here is the DEK still exists in the source database.

The Solution

Let’s fix this. I’ll return to my first instance and the original database that was TDE encrypted and now is not. I can issue this:

DROP DATABASE ENCRYPTION KEY

Once I do this, it completes quickly. This is a standard DDL command, but one that’s not often used.

Once I do this, I’ll take another backup and return to the second instance. Now when I try the restore, I see this:

If you’re having issues restoring a database that used to be TDE encrypted, try removing the DEK and then backing it up.

December 28, 2016
Encryption Keys Matter

Perhaps the importance of protecting encryption keys is even greater than we realize. It appears that the NSA and the US Government have been able to read encrypted traffic for some time on the Internet, perhaps for most of the last decade. There was an exploit in Cisco PIX routers, which I’ve used in many companies, that allowed the NSA to gain the encryption keys used by VPN traffic. In fact, it is likely the NSA could actually penetrate the networks on either end of the VPN with those keys.

Some of you may be against back doors for governments in encryption products, and some of you may be for allowing governments access with legal protections. However, the most disturbing part of this for me is that no organization knew their communications, supposedly secure, were being intercepted and read. The nature of the digital world is that exploits can copy information without the holders of that information being aware of the effort. This is much harder with physical items, where the movement of an object from one person to the next is easily noticed.

One of the very difficult things with protecting the data in our databases is trying to understand when someone has actually retreived information they shouldn’t. This is much more difficult than just tracking changes to data, which is more straightforward. We have auditing mechanisms that easily track changes to data, though most of us don’t have this set up or configured to catch all changes. In practice, that might be good enough to prevent data quality issues, but it doesn’t necessarily protect data from read disclosure.

Monitoring what information is accessed is far harder than tracking changes. Do you know if someone in Sales is accessing a single row with customer details or the sensitive information for all customers? Can you tell when a request is legitimate for an application or if there’s an unusual query that might be seeking massive amount of data for download? Those are hard questions, and ones that I think can only be handled by a large amount of activity monitoring along with machine learning assistance to look for patterns in user activity. Other features like Row Level Security can help limit the inadvertent mistakes made by developers or users, but not necessarily prevent a single user, especially a malicious user, from querying information.

Apart from the activity issues, we should ensure that where it is possible, we should be using encryption to prevent accidental disclosures outside of our applications. I think Always Encrypted has possibilities for the database, but the key management for this, as well as key management for VPNs, disk encryption, and other protection mechanisms, needs both more maturity and the open disclosure to prevent back doors from being included in products. We also need more maturity in our software development that takes the implementation and protection of encryption mechanisms seriously.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.7MB) podcast or subscribe to the feed at iTunes and Mevio .

September 13, 2016