T-SQL Tuesday #69–Encryption

TSQL2sDay150x150This is a good T-SQL Tuesday topic for me. This month Ken Wilson asks everyone to write on encryption, which is a topic I’ve presented on quite a few times.

You can participate, too. Take a few hours, learn something, and tell us what you learned. Let everyone know how you view this topic and grow your blog with a little new knowledge.

T-SQL Tuesday is a great chance to force you to write a post on a specific topic. Your post needs to publish on the second Tuesday of the month, Aug 11 this month, to be included in the roundup from the host. However feel free to write about this topic anytime, and even include the T-SQL Tuesday title.

CASTing Results

A short post this month, as I’m a bit buried in a few things, but this is one of those encryption notes that I didn’t see well documented when I started working with the subject, and I’m hoping I can save you a few minutes of frustration.

If you encrypt your data, it will be stored as a binary type. This is because encrypted data is supposed to be random, and not easily decrypted.

Let’s imagine I have some simple setup like the code below. I’ll create a key, open it, and use it to encrypt some data that I’ll insert into a table.

CREATE TABLE MyEncryptionTest( intsource INT, charsource VARCHAR(50), intencrypt VARBINARY(max), charencrypt VARBINARY(max));

INSERT dbo.MyEncryptionTest
        ( intsource ,
          charsource ,
          intencrypt ,
VALUES  ( 7, 
          'Spike' , 
          ENCRYPTBYKEY(KEY_GUID('MyKey'), 'Spike')

SELECT top 20
 * FROM dbo.MyEncryptionTest;

The results of this are that I get binary data:

2015-08-04 22_10_22-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (76))_ - Microsoft SQL Server

Now, the decryption routine for T-SQL doesn’t need to specify the key. That means instead of a *, I can use the DECRYPTBYKEY function and pass in the column.

        intdecrypt = DECRYPTBYKEY(intencrypt),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
FROM    dbo.MyEncryptionTest;

This gives me this:

2015-08-04 22_12_22-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (76))_ - Microsoft SQL Server

Not quite what I want. What if I cast this back to an integer? After all, the output of the function is listed as an nvarchar.

        intdecrypt = CAST(DECRYPTBYKEY(intencrypt) AS INT),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
FROM    dbo.MyEncryptionTest;

I see:

2015-08-04 22_18_10-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (76))_ - Microsoft SQL Server

Again, not what I wanted. However, since I know something about conversions, I realize the output is close to what I want. In fact, what I need to do is perform a different CAST before I perform my final one. Here I’ll decrypt the results as NVARCHAR first, then as an INT.

        intdecrypt = CAST(CAST(DECRYPTBYKEY(intencrypt) AS NVARCHAR(30)) AS INT),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
FROM    dbo.MyEncryptionTest;

Now I see:

2015-08-04 22_15_29-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (76))_ - Microsoft SQL Server

If I do the same for the character column:

        intdecrypt = CAST(CAST(DECRYPTBYKEY(intencrypt) AS NVARCHAR(30)) AS INT),
        chardecrypt = CAST( DECRYPTBYKEY(charencrypt) AS VARCHAR(50)) ,
        intencrypt ,
FROM    dbo.MyEncryptionTest;

I’ll get the correct results.

2015-08-04 22_17_11-SQLQuery1.sql - ARISTOTLE.sandbox (ARISTOTLE_Steve (76))_ - Microsoft SQL Server

Note that if I take the character column and cast to nvarchar, I’ll get something different. Try it and see.

And don’t forget to close your key.


Source Code Security

I’m not surprised, and I had expected to read about something like this much sooner. Apparently someone at the ride sharing company, Uber, posted a security key online in a GitHub distribution. I assume this was some sort of code repository for Uber that may or may not have been supposed to be shared publicly, but having used GitHub, I could see someone making a mistake and accidentally putting private code in the public space.

Uber is worried as the key is a security authorization key used to access their databases. Someone apparently downloaded Uber database files and now Uber is attempting to track them down. The whole situation is a mess, but there are a number of problems here that we could learn from.

I give a talk on encryption options in SQL Server and one of the main problems I see with symmetric keys in SQL Server is that they can be reproduced with the same parameters passed to CREATE SYMMETRIC KEY. This means that your source code is now a security hole, at least if the production values are stored in a VCS.

However that’s a no-no. Developers shouldn’t have access to account information or keys that are used in production. There should be separate credentials used in development, precisely for this reason. If someone gets your code, or hacks a dev machine, they shouldn’t be able to jump to production.

There’s also the issue of using a service like GitHub, or any online VCS. The distinction between private code and public code shouldn’t count on a human checking or un-checking the right box. I’d like to see some better separation, perhaps requiring separate accounts and connections for public and private distributions.

As we see here, convenience can easily result in poor security. That’s not a trade-off we should be making with our security and applications. Especially not for software developers.

Steve Jones

The Voice of the DBA Podcast

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

Detecting Encryption

I ran across an article recently from MSSQLTips by my friend, Brian Kelley. It talks about the ways you can detect encryption in use in your database. Brian’s approach, which is one I agree with, is that you can look for symmetric keys, asymmetric keys, and certificates in the system tables. The tables you query are:

  • sys.symmetric_keys
  • sys.asymmetric_keys
  • sys.certificates

That’s a good way to detect SQL Server encryption in use, but not encryption in general. One of the things I’ve advocated for applications that contain sensitive data and need to be protected from the DBA is to have the application create temporary keys or use .NET libraries to encrypt data. In that case, SQL Server just sees data, and doesn’t detect encryption.

Brian offers a solution that is to examine any columns containing these data types

  • binary
  • varbinary
  • image

That’s a good start, but how do you detect that this string is encrypted?

504b 0304 1400 0000 0800 1a86 4640 0d41 …

That’s actually not encrypted; it’s the start of a zip file. However it could be a jpg, a tiff or some other binary format. The only way I thought of was mirrored in this Stack Overflow note: you’d have to compare known file types and look for a pattern in a header of some sort that doesn’t match. It wouldn’t be sure you didn’t have encryption, but you might make some educated guess if no file type that might fit the data matches.

There was also a link in the comments to a Stack Exchange discussion on the same topic. It’s similar, though I saw the use of the KEY_NAME() function in there. I hadn’t used it, perhaps because of the poor documentation of encryption in SQL Server. I also found a KEY_ID() function that works similarly, returning the ID for the name of a key.

Test Your Situation

I gave a talk on Transparent Data Encryption (TDE) recently and a number of people in the audience were using the feature. However when I asked how many of them had restored a TDE database, not all hands remained up. When I asked how many people had restored their TDE encrypted backup  to a different server, one that didn’t have TDE enabled, very few hands remained in the air.

That’s not good, and I certainly hope those people don’t experience a disaster from which they cannot recover. I’m sure they are not alone. I suspect that many of the people managing a TDE database have restored a database this year, and are confident they can do so. However what they don’t know is if they can restore those TDE databases on any other instance, including a newly installed one.

They’re not alone. I see many, many people implement features they don’t really understand. Microsoft has made it easy to set up replication, clustering, and more in your environment, but without providing some of the robustness and reliability that many people need. The ease of setting up a feature is one thing. The ease of ongoing management and recovery when issues occur is something else entirely.

I really wish that Microsoft would go further than making implementation easier and include direction for ongoing tasks. When databases are created, ask the user to set up backups and help them create the jobs. When encryption is implemented, do more than display a warning message. Help adminstrators prepare for recovery with templates or jobs that automatically build certificate or key backups. When replication is set up, include a script to rebuild the environment for when it breaks.

I doubt we’ll get this, and many companies and employees will continue to implement features they don’t understand. You can only help your own situation, and you should be ensuring you understand and can rebuild all the extra features you’ve installed in the event of a disaster.

Steve Jones

The Voice of the DBA Podcast

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

SQL Injection Issues–Password Hashing

I’ve got a demo for one of my talks that really highlights some issues we have with SQL Injection. It’s part of my encryption talk, and it goes like this.

NOTE: I am showing a simple example here, not one that I would deploy into production. The concepts are similar, but this specific code is not designed or applicable for cut/paste into a production system.

Imagine I have a simple table of users and passwords.

create table UserTest
( firstname varchar(50)
, passwordhash varbinary(max)
-- insert passwords
insert usertest select 'Steve', HASHBYTES('SHA2_512', 'AP@sswordUCan!tGuess');
insert usertest select 'Andy', HASHBYTES('SHA2_512', 'ADiffP@sswordUCan!tGuess');

I’ve got two users and a fairly strong hash of their passwords. I’m using the SHA2 algorithm, at 512 bits, and complex passwords. I’m showing this in T-SQL, though you could easily hash these passwords in the application layer and just store the values in the database.

I create a simple proc that takes a username and a password as parameters.

create procedure CheckPassword
   @user varchar(200)
 , @password varchar(200)
if hashbytes('SHA2_512', @password) = (select passwordhash 
                                 from UserTest
                                 where firstname = @user
  select 'Password Match'
  select 'Password Fail'

NOTE: This is shown at the DB layer for simplicity, but having a user’s password transit the network in plaintext and be passed to a proc is a poor practice. It would be better to hash this and only send the hash to SQL Server.

If I want to verity a user, I can do this:

declare @p varchar(200);
select @p = 'AP@sswordUCan!tGuess';
exec CheckPassword 'Steve', @p;

The result of this call is the password matches.


If I try a different password, say the one for the other user, it will fail.


That’s good. This is very similar to how many applications, including AD and SQL Server, validate users. However, here’s one problem with a simplistic implementation like this.

Imagine that through SQLInjection, someone learns the structure of the table. Not hard to do. Now the data in the table is hashed, and there are lots of hashing algorithms. Certainly it’s a lot of work to try all different combinations of possible passwords, and algorithms to find a match. It’s possible and it’s a brute force attack.

Here’s the data in the table.


However, the hacker, Andy,  doesn’t need to decode the password. Imagine that the hacker creates his own account, which is probably a low level account. However the hacker runs code like this, substituting different accounts for “Steve” until a privileged account is found.


Now the attacker does this. They use my (Steve’s) privileged account, with their password:


The hacker (Andy), can now log in as Steve using his password. Any rights that are assigned to Steve are available for Andy.

We have an attack without decryption.

This is one reason that SQL Injection is a big problem in applications, especially those that implement some type of their own security. Solving this is slightly tricky, and I’ll talk about it in another post.

One side note, the only way this is usually detected is if Steve logs in with his password. He’ll see this:


Even then, unless Steve suspects an attack, he might write this off to a mistyped password, try multiple times and eventually reset his password without a second thought.

Encryption in Colorado Springs – Encrypting in the Application?

Last night was my annual presentation at the Colorado Springs SQL Server User Group. I try to make sure I get down there at least once a year, and it’s been only once a year for the last few years. Far too busy, and I’m sorry for that, but I am glad I get invited to go down.

I presented The Encryption Primer, and there were a few interesting questions asked. Always good to see people debating and asking questions.

One interesting one from a developer – If I can perform encryption in the application, why would I do use something like TDE or column level encryption?

To me, I prefer to do encryption as close to the source as possible. If I can do the encryption in the application front end, I’d do it there. It reduces the chances of having the data accidentally disclosed. I don’t have to worry about having data read across the wire, or in a backup tape, or anywhere else.

However that takes time and effort. Developers are expensive, and they have to write good, solid, secure code in the application. They also have to write this encryption code in every application that accesses the database (reports, ETL, etc.).

Something like TDE is much easier to setup and use. Column level encryption, while still coding, is centralized.

It’s a balance, and one you need to consider carefully and thoroughly. It also helps to debate and discuss the decisions about what you protect, why, and what it costs.

DevConnections–High Performance Encryption

The talk page is updated, but in case you attended my talk, the downloads are here:

Slides: EncryptionPerformance.pptx

Code: EncryptionPerformance.zip

There are also a few great questions in the talk. One that I certainly need an answer to is how do clients deal with the change of SSL certificates in communications. If someone knows send me a note.

For those of you that attended, the Asymmetric Encryption query was still running at 24 minutes when I killed it.

DevConnections 2014

I live on a small ranch in Colorado. It’s a working ranch where we board horses, and my wife trains both horses and people. There’s a lot of work to be done at the ranch, and both me and the kids help out.


We have a variety of tools we use to deal with the various chores and infrastructure we have to manage. Depending on the task, we might choose a specific tool.


We also like to use the right sized tool for the job, especially when the job involves heavy lifting.


I often think about my career in technology, and how similar it can be to my life now. I’ve often worked with a variety of technologies, from networking routers, to Active Directory to Exchange in my career as a DBA. I’ve had to understand development languages and techniques to collaborate effectively with developers. Knowing a little bit about a lot of things has helped me often.

That’s one of the reasons I like the DevConnections conference. With so many different technologies being discussed a the same event, I can walk from a SQL Server session on execution plans to a development talk on Fiddler. You can learn about testing software and SharePoint queries along with Trace Flags without leaving the building.

Most of us work with a variety of technologies, and if we don’t, we often do work with a variety of IT pros. Learning a bit more about how others do their jobs, how their technologies might interact with yours, or even picking up a little jargon, can help you to be a better IT worker overall.

I’ll be in Las Vegas this September for DevConnections and I hope you’ll join me. It’s a good time at the Aria resort with plenty of ways to relax in the evening after a packed, educational schedule each day. And if you’re interested in Continuous Integration for Databases or High Performance Encryption, stop by one of my sessions.

I hope to see you Sept 15-19 in Las Vegas. Register today!

If you’d like to see a video promo, click below and enjoy.

High Performance Encryption

It is becoming more and more important to protect your data from anyone that should not access it, including the DBA. Learn how you can use column level encryption as well as TDE to protect your data files, with minimal performance impact to your SQL Server instance.

This session will look at securing data

  • in flight
  • at rest
  • inside the database

We do this with

  • IPSec/SSL Communications
  • Transparent Data Encryption
  • Partial Hash Bucket values with symmetric encryption.



Maintaining High Performance When Using Encryption

Summary: Encryption is becoming required in more and more environments, but implementing encryption can dramatically affect performance. Learn how you can maintain high performance while using encryption in your database.

Abstract: Encryption is becoming required in more and more environments, but implementing encryption can dramatically affect performance. Learn how you can maintain high performance while still protecting your data with encryption. This session will examine communications, Transparent Data Encryption and a technique for using Symmetric Key encryption without a high performance penalty.

Length: 75 minutes

Slides: (coming soon)

Code: (coming soon)