Crack that Encrypted Data

Ransomware appears to be gaining some traction as a new trend. For awhile in my career, it was virus programs designed to send to all your contacts. Then it was infections to use your computer as part of a bot net. Now it’s encrypting your files and demanding a payment to get the password.

I’m starting to think that a)I need to ensure I have solid, better backups on all devices, and b) I should pay attention and be aware of decryption programs. I’d love to say that I could build a decryption program, like someone did, but as much as I’m interesting in encryption and study it, that’s a little out of my skillset wheelhouse.

I’m actually starting to think that this might be a way that people in communities, like the SQL Server community, can help each other. We can be aware of potential ransomware threats, like the one that hit this hospital, and potentially share ways to recover from the incident, or even decrypt the drives. In fact, I suspect it might be worth keeping a system handy to practice decryption techniques, if you can determine the attack vector.

I’m sure many organizations wouldn’t want to share details of attacks and infections, but this is exactly the type of information that we, as data professionals, should be sharing. It’s incredibly difficult to keep up with all the threats and attacks, not to mention the techniques to recover systems. I’d urge all of you to ask your employers if you can at least help others, even if you can’t disclose how or where you gained the knowledge. If nothing else, the information needs to be shared more publicly to allow us to better protect our systems and be effective custodians of data.

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.

Getting Started with Encryption in SQL Server 2016

The release of SQL Server 2016 gives developers a number of ways in which they can securely encrypt and protect their data. In this introductory session, you will learn about the encryption options in SQL Server 2016, watching Always Encrypted, TDE, Row Level Security, Dynamic Data Masking, and server side column encryption can secure and protect your data from unauthorized users.

Length: 60-75 minutes

Code: Github

Powerpoint deck: Getting Started with Encryption in SQL 2016.pptx

End to End Always Encrypted in SQL Server 2016

Abstract: Protecting our data from unauthorized access becomes more and more important all the time, however it has been difficult to ensure sensitive data is encrypted in SQL Server. The new Always Encrypted feature in SQL Server 2016 makes this much simpler for developers and DBAs with a framework for protecting data from the client, across networks, and inside of the database. This new feature allows for limiting access to the data, even from the DBAs and sysadmins that may control the database instance itself. Learn how to implement and use Always Encrypted in your applications.

Length: 60-75 Minutes

  • Demos:
  • Always Encryption Setup
  • Working with Data in a client application
  • Certification creation and transfer
  • Certificate Rotation

Powerpoint Deck: End to End Always Encrypted.pptx

Code: Github

Quick Encryption with Always Encrypted

What do you need to do in order to access data in a SQL Server that’s encrypted with Always Encrypted? It’s not much, and it’s really simple.

  1. The certificate used for encryption
  2. A parameter in the connection string

That’s it. It’s a small list of things.

I was experimenting with this, and I set up encryption on a VM, then copied the certificate backup to another VM and installed it in the Certificate Store.

2016-03-28 17_56_00-Settings

This is all I changed on my C# application to enable encryption.

strConnstring += “; Column Encryption Setting = Enabled”

I had a connection string built, and I added this one little option to the end and when I queried my encrypted table, I could read the data.

There are certainly more caveats and more to learn about encryption, but this shows how easy it can be to change your application. Just alter the connection string.

Create a Database Master Key–#SQLNewBlogger

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

One of the first things you need in a SQL Server database in order to implement encryption is a database master key, DMK. This is simple to create, though you need one in each database that will support encryption.

The syntax is easy, with only really an option to specify a password. There is no name, as there’s a single DMK per database. Set your context to the correct database and end enter:


When you execute this, you’ll just get a result message. At least, if it works you will. The password must conform to the password requirements of your Windows OS, which is good.

Note: This is a securable code, like the password for a user account. Make sure you store this in a password manager for your organization.

By default, this is protected by your password as well as the Service Master Key (SMK) on your instance. In practice this doesn’t usually mean much for you, but be aware of this.

You do need CONTROL permission on the database, though usually I’d expect a db_owner or more permissions to actually create these keys.

And, of course, back up the key as soon as you can.


This was about 5 minutes work for me. I would guess most new bloggers could read, understand, and produce an explanation of this in 30 minutes.



Create a Database Master Key –

SQL Server 2016: The Data Protection Version

I’ve spent a few years working with SQL Server encryption and security, trying to educate people on the various capabilities involved. There are some nice features available in the platform that can help you run a more secure SQL Server, but there has been a lot of room for improvement across the last few versions. The openness of the platform is somewhat tempered by the need for each of us to write a fair amount of code to properly secure our databases. That is the hard part as many developers don’t write secure code.

That is changing a bit. I am really excited about the SQL Server 2016 release in that there are a number of data protection mechanisms that are going to greatly expand how we can protect our data moving forward. From Row Level Security to Always Encrypted to Dynamic Data Masking, the number of new features that will help make security a bit easier is growing dramatically.

I certainly think Microsoft is definitely encouraging and supporting better, more secure coding practices. If you look in Books Online at MSDN, there is now a Security Center that covers a number of topics and organizes information in a much better way. Security has seemed like an afterthought in past documentation. From Encryption to SQL Injection to Auditing to even Metadata Visibility, it seems like the next version of SQL Server on premise, and in Azure, really is taking security more seriously.

I look forward to watching the platform evolve and security increasing over time. It does seem as though SQL Server has the fewest security holes in the platform, but I hope that many of us can take advantage of the changes in SQL Server over time and build applications that will be viewed as being secure as well.

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.

Data for Ransom

Imagine you come into work and all of your database columns are encrypted. You have no idea what key was used, and your application can’t access the data. You receive a message that for a $100,000 payment, the decryption key will be sent to you. What do you do?

Well, depending on your business, and how quickly you can restore backups, maybe your organization pays. I read a story recently about a hospital that found a number of their files encrypted by a virus, with a demand to pay a ransom for the decryption key. They did, received the key, and opened their files. However that set a dangerous precedent.

Apparently attacks by ransomware are on the rise. Those of us with database software are not likely to be too affected as we (should) have regular backups we can restore. However the interruption to businesses can be costly, and this can result in more organizations paying the ransom, which then encourages more attacks.

The new Always Encrypted capability in SQL Server 2016 is great, and I’m excited by it. However, we want to be sure a malicious user doesn’t enable this feature. I could certainly see that as an attack vector for web based systems. An attacker gets sysadmin privileges, enables Always Encrypted and places the certificate on the client web server. Some weeks later, they remove the client certificate, and suddenly no one can decrypt the data. Potentially, depending on backup procedures, there might not even be a capture of the certificate from the web server in any backup files.

The world is becoming more dangerous, and more troublesome for our data. As with most things, vigilance and monitoring of security segments of our applications is important. While ransomware is unlikely to strike databases, one never knows, and you should ensure that you have good backups in the event that you are attacked.

Steve Jones

The Voice of the DBA Podcast

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

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.