Echoes from the field 9: Encryption in SQL Server (Part 2)

Echoes from the field 9: Encryption in SQL Server (Part 2)

During a recent consulting engagement, I was asked about how to use column-based encryption in SQL Server. In this article, which is the second part of a two-part series, let’s explore how to get started using column-based encryption.

In the previous post , I provided background information about encryption and certificates.

If you’re not very familiar with these technologies, you should read that previous post before this one.

Encryption Technologies in SQL Server

Way back in SQL Server 2005, Microsoft introduced an amazing array of technologies. I suspect that many SQL Server professionals aren’t aware of just how many technologies were introduced in that release. It’s not surprising that professionals are still learning about a number of these technologies, even 20 years later. In particular, questions regarding certificates, keys, and column-based encryption still draw blank responses from most SQL Server professionals.

Previously, I mentioned that there are two basic forms of encryption used today: symmetric encryption and asymmetric (or public key) encryption.

Symmetric encryption uses the same key for both encrypting and decrypting the data. The keys that are used in symmetric encryption are smaller than those used for asymmetric encryption, so symmetric encryption is faster. However, symmetric encryption presents a big challenge: how to exchange the key. Asymmetric encryption gets around this problem by using one key for encrypting the data and another key for decrypting the data. The two keys are mathematically related, but the combination of the complexity of the algorithm that’s used and the length of the keys (measured in bits) means that it’s computationally infeasible to derive one key from the other key, today. One key (the private key) is kept secret, and the other key (the public key) can be sent in an unencrypted form to the other party. SQL Server supports symmetric keys, asymmetric keys, and certificates as database (and server) objects.

Symmetric keys are typically used for encrypting most data, and asymmetric keys are used for protecting other keys. Let’s take a look at a specific example of using column-based encryption in SQL Server.

Column-Based Encryption Example

The code below creates a test database that I’ll use for this example:

USE master;
GO

IF EXISTS(SELECT 1 FROM sys.databases WHERE name = N'EncryptTest')
    DROP DATABASE EncryptTest;
GO

CREATE DATABASE EncryptTest;
GO

USE EncryptTest;
GO

Before you can encrypt some data, you need to create an encryption key:

CREATE SYMMETRIC KEY GregsKey
    WITH ALGORITHM = TRIPLE_DES
    ENCRYPTION BY
    PASSWORD = 'VerySecretStuff';
GO

In this code, I first specified that I want to create a symmetric key named GregsKey. Next, I specified which encryption algorithm to use. SQL Server supports encryption algorithms that are, in turn, supported by the Cryptographic Application Programming Interface (CAPI) in the operating system. These algorithms have names such as DES, Triple DES, DESX, RC2, RC4, 128-bit RC4, 128-bit AES, 192-bit AES, and 256-bit AES, etc. You should expect the list of algorithms to constantly evolve, and for older ones to fall out of favor.

Each operating system supports a specific list of encryption algorithms. For example, Windows XP and Windows 2000 didn’t support the AES algorithm. In this example, I selected the TRIPLE_DES option. It’s an older one but will do for demonstration purposes.

Finally, the key itself needs to be protected somehow. For simplicity, I chose to use a password. This means that a user who wants to decrypt data using this key needs to know this password to open the key. I’ll discuss some other options for protecting the key another time.

Open keys are associated with user sessions. A user opens the key within a session. Once the key is open, it can be used for encrypting and decrypting data:

OPEN SYMMETRIC KEY GregsKey
    DECRYPTION BY PASSWORD = 'VerySecretStuff';
GO

You can query the symmetric keys within the database:

SELECT * FROM sys.symmetric_keys;
GO

Note that the key has both an ID (symmetric_key_id) and a globally unique identifier (GUID) value (key_guid). I’ll discuss how important the key_guid value is shortly, but first I want to show you how to use this key to encrypt data:

DECLARE @CipherData varbinary(max);

SET @CipherData = EncryptByKey(Key_GUID('GregsKey'),
                   'Text that I don''t want anyone to read');

SELECT @CipherData;

SELECT CONVERT(varchar(200),DecryptByKey(@CipherData));
GO

In this code, I declared a varbinary(max) variable, @CipherData, that will be used to hold the encrypted text. Note that this could just as easily be a database column of the same data type. Next, I used the EncryptByKey() function to encrypt some text. In addition to providing the text to be encrypted, I identified which encryption key to use, as there might be multiple keys open at the same time. The key is identified by its key_guid. In this example, I retrieved the GUID by passing the key’s name to the Key_GUID function.

When the code is executed, the results are received:

As this result set shows, when you encrypt data by executing the SELECT @CipherData; command, a binary value is returned. Notice that this value is much larger than the original text. This is important, as you don’t want anyone to be able to estimate the data based on the length of the encrypted values.

Salt

This is part of the reason why encryption algorithms add additional data known as an initialization vector (IV), which is commonly called salt, and often add some amount of padding to the data they’re encrypting. Another important use of an IV is to avoid a situation where encrypting the same clear text would always lead to the same cipher text.

Here is the second result set, which contains the output from the SELECT CONVERT(varchar(200),DecryptByKey(@CipherData)); command:

This output shows how the encrypted data has been decrypted. The DecryptByKey() function has been used to perform the decryption. Note that only the encrypted text was passed to the function. So how does SQL Server know which key to use to decrypt the data?

The answer lies in the data itself. In this case, the key’s GUID was 6183B200-4F22-4235-8682-408551F9CF2F. GUID values can be written in a number of styles. Listing 6 shows this same key being retrieved as a binary string:

SELECT CAST(key_guid AS varbinary) FROM sys.symmetric_keys;

If you take a close look at the output, you’ll notice that this same value is the first component of the encrypted data. SQL Server stores the GUID of the encryption key as the first component of the encrypted data so it already knows which encryption key to use to decrypt the data.

The user just needs to have the encryption key open. If you run the code below, you’ll see what happens when a key isn’t open:

DECLARE @CipherData varbinary(max);

SET @CipherData = EncryptByKey(Key_GUID('GregsKey'),
                   'Text that I don''t want anyone to read');

SELECT @CipherData;

SELECT CONVERT(varchar(200),DecryptByKey(@CipherData));

CLOSE SYMMETRIC KEY GregsKey;

SELECT DecryptByKey(@CipherData);
GO

This shows the output from the attempt to decrypt the data with the closed key and the message that appears on the Messages tab.

What is particularly powerful about this design is that multiple users can execute the same SELECT statement but the data that each user will see depends on which encryption key he or she has open at the time—and this all occurs without an error being thrown.

Summary

In this post, I described the basic process for using symmetric keys to encrypt and decrypt data in SQL Server.

Encryption training

You should learn about how encryption and secrets work in SQL Server, as well as more about general security. You can do that with our course SQL Server Encryption for Developers and DBAs

2025-11-22