The $90,000 Laptop

A hospital got the opportunity to pay $90k for a lost laptop.

There’s no excuse for this. If you have a Windows laptop, enable bitlocker today. If you have OSX, setup FileVault. If you’re on Linux, choose dm_crypt or something else. Go ahead, get that setup, save off your keys as a backup, and come back. I’ll wait.

Now, don’t you feel better? I’m sure you do, and you are somewhat more protected from the random theft, misplacement, or loss of your laptop. All of those things happen regularly. Not to each of us, or many of us, but as the collective set of developers and DBAs around the world, we lose laptops regularly.

Certainly some of us have precautions like never carrying data around. That’s good, and I’d recommend that. For those that need to develop on the go, they might have a curated set of test data we can use for development. That’s fine. We should make an investment in building test data and have that data used for unit, integration, and system testing.

We should have investments in ensuring that our systems can not only be encrypted, including encryption of backups and networks, but that we can restore those systems in disasters. Make the investment in ensuring recovery, and everyone is more likely to accept encryption. We need to invest in a process for managing keys, revoking them, and re-issuing them. We need to be sure we can upgrade encryption algorithms over time. What is secure today might be easily broken tomorrow.

I’d urge you to make all those investments, but until you do, at the very least, encrypt your laptops and desktops. While any random theft could result in a lost laptop, it’s not unheard of for anyone walking through your office to pilfer a drive lying around, or even one inside a desktop.

Steve Jones

The Voice of the DBA Podcast

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

The Auditor Attack Vector

The phone on the desk buzzed. The CEO picked it up, expecting his assistant to let him know his next appointment had arrived. Instead he was told a person had called and wanted to discuss why his managers were paid less than some of their direct reports.

The CEO was puzzled, and worried, so he accepted the call.

“Did you know that you have programmers making more than some of their managers? ” the caller asked, quoting the specific people and their salaries.

The CEO did know, acknowledged this, but declined to discuss the matter. Instead he asked who was on the phone, and how did they know the salaries of his employees.

The caller declined to give their name, but told him that they had found a USB thumb drive outside on the street and had plugged it into a computer. A number of spreadsheets were on the drive, with one containing the salaries and organizational structure of the company. The called left the story there, promising to mail the drive back to the CEO.

The CEO was upset, and worried, but waited a few days to get a package in the mail. He had been planning to terminate someone for carelessness. However when he opened the package, he realized none of his employees was to blame. Instead, this was a device given to an auditor who was verifying the accounting practices of the company.

I don’t know the rest of the story, but it was given to me by someone that runs a decent sized company. It’s a scary story and shows a concern one that has nothing to do with most of us that work in technology departments. However this does show that there are always holes in our processes and practices. We need to consider the fact that many of the businesspeople we work with value convenience much more than security. We need to be sure we take precautions where possible, such as encrypting all data at rest, and in transit, wherever possible.

It might not be our fault, and it might not be something we’re blamed for, but I certainly would feel some guilt if I had copied the data onto the USB drive without providing additional security, such as encryption or at least a password.



Steve Jones

The Voice of the DBA Podcast

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

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


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.