Voice of the DBA

Tag: encryption

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.

December 14, 2015
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.

December 1, 2015
T-SQL Tuesday #69–Encryption
This 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));
CREATE SYMMETRIC KEY Mykey WITH    ALGORITHM = AES_128 ENCRYPTION BY PASSWORD = 'M$test78';
GO
OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD = 'M$test78';

INSERT dbo.MyEncryptionTest
        ( intsource ,
          charsource ,
          intencrypt ,
          charencrypt
        )
VALUES  ( 7, 
          'Spike' , 
          ENCRYPTBYKEY(KEY_GUID('MyKey'), CAST(7 AS VARCHAR(10))) ,
          ENCRYPTBYKEY(KEY_GUID('MyKey'), 'Spike')
        );

SELECT top 20
 * FROM dbo.MyEncryptionTest;
```
The results of this are that I get binary data:

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.
```
SELECT TOP 20
        intdecrypt = DECRYPTBYKEY(intencrypt),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;
```
This gives me this:

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.
```
SELECT TOP 20
        intdecrypt = CAST(DECRYPTBYKEY(intencrypt) AS INT),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;
```
I see:

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.
```
SELECT TOP 20
        intdecrypt = CAST(CAST(DECRYPTBYKEY(intencrypt) AS NVARCHAR(30)) AS INT),
        chardecrypt = DECRYPTBYKEY(charencrypt) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;
```
Now I see:

If I do the same for the character column:
```
SELECT TOP 20
        intdecrypt = CAST(CAST(DECRYPTBYKEY(intencrypt) AS NVARCHAR(30)) AS INT),
        chardecrypt = CAST( DECRYPTBYKEY(charencrypt) AS VARCHAR(50)) ,
        intencrypt ,
        charencrypt
FROM    dbo.MyEncryptionTest;
```
I’ll get the correct results.

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.
```
CLOSE SYMMETRIC KEY mykey;
```
August 11, 2015
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.

March 31, 2015