SQL Server Column Encryption and Decryption​ with Code Examples

By: Rick Dobson   |   Updated: 2024-12-18   |   Comments   |   Related: > Encryption

Problem

I was recently assigned to work on a team who will implement column-level encryption and decryption solutions in SQL Server. Please introduce the basics of SQL Server encryption and decryption features. Also, provide a series of T-SQL samples for encrypting and decrypting data within the columns of a SQL Server table.

Solution

SQL Server encryption can be implemented with cryptographic random numbers. This tip introduces the crypt_gen_random function for generating cryptographic random numbers in SQL Server. Cryptographic random numbers typically have the highest level of sequential unpredictability but are also not generated as quickly as other random number types. T-SQL can generate other kinds of random numbers with the rand and newid functions. Each SQL Server random number generator is well suited to at least one kind of application.

Keys can be used in SQL Server for data encryption and decryption. There are two types of keys for encryption and decryption – a symmetric key and an asymmetric key. Both key types can be used to change plain text, which is readable by humans, into cipher text, which is not readable by humans. The process of going from plain text to cipher text is known as encryption. The process of transforming cipher text into its original plain text is called decryption.

A single symmetric key can facilitate both encryption and decryption. Symmetric keys encrypt and decrypt data faster than asymmetric keys. An asymmetric key is comprised of a key pair. The public asymmetric key is used for encryption and it can be shared with anyone who needs to send content to you, like a symmetric key. The private asymmetric key is used for decryption. Because an asymmetric key exists as a matched pair of public and private keys, only the person with the private key of an asymmetric key pair can decrypt content encrypted by the public key of that key pair.

Column-level encryption can encrypt a whole column of a table within a SQL Server database. Examples of columns suitable for this kind of encryption are those holding any kind of sensitive data for personal identity through to national security secrets. You can encrypt and decrypt columnar data to transform plain text to cipher text and back again to plain text.

This tip includes basic T-SQL examples for the crypt_gen_random function and symmetric keys within a SQL Server database. You will also discover T-SQL code samples for encrypting and decrypting a column of data in a table.

Using and Comparing Newid Versus Crypt_gen_random Numbers

This section shows syntax and output examples for numbers generated by the newid and crypt_gen_random functions. The crypt_gen_random and newid functions can both return 16-byte values in SQL Server. However, the crypt_gen_random function is more versatile in that it can return random numbers with a length of 1 byte through 8000 bytes, while the newid function can only return 16-byte numbers.

The following code sample shows how to generate a 16-byte random number with the crypt_gen_random function:

• The @crypt_gen_random_number local variable is declared as a varbinary datatype.
• The select statement in the second line of the script assigns a 16-byte varbinary data value to the local variable.
• The third line of the script displays the value returned by the crypt_gen_random function in the second line of the script.

-- Generate bytes using CRYPT_GEN_RANDOM
declare @crypt_gen_random_number varbinary(16)
select @crypt_gen_random_number = (select CRYPT_GEN_RANDOM(16))
select @crypt_gen_random_number [@crypt_gen_random_number with varbinary(16 type)];

Here is a view of the sample output from the preceding script.

• The 0x prefix denotes the following characters as hexadecimal values. These characters are for hexadecimal numbers, which can assume values from 0 through 15.
  • The characters of 0 through 9 are for values of 0 through 9.
  • The letters A through F represent values of 10 through 15.
  • A hexadecimal value can be represented by 4 bits (from 0000 through 1111), and a byte contains 8 bits. Therefore, a 16-byte array is comprised of 128 bits (8x16). Because there are 128 bits in 16 bytes, there are 32 hexadecimal characters in a byte array comprised of 16 bytes.

Here is a second script for the crypt_gen_random function for generating a byte array of 32 bytes – twice the length of the byte array in the preceding script. There are 64 hexadecimal characters in the byte array because there are two hexadecimal characters per byte. The output appears below the script.

-- Generate a 32-byte random number
declare @32_length_byte_array varbinary(32) = CRYPT_GEN_RANDOM(32);
select @32_length_byte_array [32 length byte array from crypt_gen_random(32)];

As a point of reference, the following example shows the code for a 16-byte GUID from the newid function. The GUID returned by the newid function is a fixed-length set of 16 bytes with a uniqueidentifier data type. The uniqueidentifier format is based on 16 bytes, but the uniqueidentifier data type is different than the varbinary data type. The newid function aims to generate random values from different computers created at separate times that do not collide with one another. A prior tip titled "Create, Use, and Analyze GUIDs with T-SQL" goes into more depth to demonstrate the newid function and GUID values.

-- Generate a byte array in a uniqueidentifier format using NEWID
declare @newid_number uniqueidentifier;
set @newid_number = newid(); 
select @newid_number [@newid_number];

Here is the output from the preceding script. Notice that it contains 32 hexadecimal characters in the following format:

• 8 initial characters followed by a hyphen.
• 3 sets of 4 characters, each followed by a hyphen.
• A final set of 12 characters.

This format corresponds to the uniqueidentifier data value format.

A Simple Demonstration of Encrypting and Decrypting a Plain Text String in SQL Server

The following script introduces the basics of transforming plain text to cipher text and back to plain text with T-SQL code. This demonstration illustrates how to create and apply a database master key and a symmetric key. Both keys are enabled for encryption or decryption by a password originating from random numbers derived by the crypt_gen_random function. There are three parts to the script.

Part 1

Part 1 starts with a use statement for the Cryptography database. You can use any database as your default database, but the database master key is only for use within the database you designate. The following steps demonstrate how to create two varbinary values with strong passwords for the database master key and a symmetric key in the database.

Step 1: Create a random varbinary value with the crypt_gen_random function. This random number is for the database master key.

Step 2: Create an associated password for the random number from Step 1. The password is derived from a cast function that returns a varchar (max) data type value.

Step 3: Repeat Steps 1 and 2 for a symmetric key.

You should save the passwords based on the random numbers generated in Steps 1-3 for re-use with more encryption and decryption transformations in the database. See the image below for the passwords used for this demonstration. The next section re-uses the passwords for column-level encryption and decryption.

Part 2

Part 2 of the following script creates the database master key and a symmetric key named YourSymmetricKey.

• Use the create master key statement to create a database master key.
• Use the create symmetric key statement to create a symmetric key.
• Each create statement references one of the strong passwords created in part 1.

Part 3

Part 3 of the script defines some plain text ('This is a confidential email from the CEO') in a local variable named @OriginalText.

• The code illustrates how to use the encryptbykey function to encrypt the plain text.
• The code demonstrates how to use the decryptbykey function embedded in a convert statement to decrypt the encrypted text and return it as plain text in a nvarchar(100) format.
• After encryption, a select statement displays the encrypted text. After decryption, another select statement displays the decrypted text.

-- Part 1
 
use Cryptography
go
 
/*
-- Script to generate a random password and display it 
-- as a BASE64 encoded string password for the Database Master Key
-- Step 1: Generate a random password as a 32-byte array
declare @RandomBinary varbinary(32);
set @RandomBinary = crypt_gen_random(32);
 
-- Step 2: Convert the random varbinary to a BASE64 encoded string
declare @Base64Password varchar(max);
set @Base64Password = cast('' AS XML).value('xs:base64Binary(sql:variable("@RandomBinary"))', 'varchar(max)');
 
-- Display the BASE64 encoded password
-- save @Base64Password for future reuse in the database
select @Base64Password AS StrongPassword;
 
-- Step 3:
-- repeat steps 1 and 2 for the Symmetric key password */
 
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
-- Part 2
 
/*
-- 1. Create the Database Master Key and Encrypt Data (run once)
-- Step 1: Create a Master Key (if not already created)
-- drop master key if database master key already exists
-- create a Master Key
create master key encryption by password = 'YFbm8vYd1Cu2E5IQxdo199YhibhUK7hsbWUoZ3id3OQ=';
 
 
-- Step 2: Create a Symmetric Key named YourSymmetricKeyName (if not already created)
-- drop symmetric key named YourSymmetricKeyName if already exists
-- Create a Symmetric Key
create symmetric key YourSymmetricKeyName
 with algorithm = AES_256
 encryption by password = '+ews06+qA6sGf/qzLECHWG/8LtI3ekSlaFTdA6N4WZw='; */
 
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
-- Part 3
 
-- Step 1: Encrypt the String
-- Open the Symmetric Key
open symmetric key YourSymmetricKeyName
    decryption by password = '+ews06+qA6sGf/qzLECHWG/8LtI3ekSlaFTdA6N4WZw=';
 
-- Encrypt the string
declare @OriginalText nvarchar(100) = 'This is a confidential email from the CEO';
declare @EncryptedText varbinary(max);
 
select @OriginalText [originaltext];
 
set @EncryptedText = encryptbykey(key_guid('YourSymmetricKeyName'), @OriginalText);
 
-- Display the encrypted text
select @EncryptedText [encryptedtext];
 
-- Step 2: Decrypt the Encrypted String
-- Decrypt the string
declare @DecryptedText nvarchar(100);
set @DecryptedText = convert(NVARCHAR(100), decryptbykey(@EncryptedText));
 
-- Display the decrypted text
select @DecryptedText [decryptedtext];
 
-- Close the Symmetric Key
close symmetric key YourSymmetricKeyName;

Here's the output from the preceding script. The first results set shows the original plain text ('This is a confidential email from the CEO'). The second results set displays the encrypted text as a byte array filled with hexadecimal characters (0x00929503C1CC8C458FFB…A301006707BDC7E89A37). The third results set shows the decrypted text, which matches the plain text in the first results set.

Column-level Encryption and Decryption

Column-level encryption is primarily about protecting one or more table columns from being accessed by unauthorized individuals. Instead of encrypting a single message as in the preceding section, you can restrict one or more columns of a table so that column contents are encrypted and secure from database intruders. Examples of columns that you may care to secure include credit card numbers, passwords, or diseases that can impact the ability of an individual to obtain medical insurance. It is good to have sensitive data encrypted on corporate computers. Even if bad actors obtain data with sensitive information, the contents of an encrypted column will not be able to be read and interpreted by those actors.

While it is not unusual to have sensitive data encrypted from a table at rest, there will also be times when encrypted sensitive data needs to be decrypted. Instances include performing analytics on sensitive data, modifying a surname for a newly married person, examining sensitive data by auditors to ensure compliance with regulations and policies, and updating a password or passcode to avoid use beyond an expiration date.

This section presents and describes two scripts -- one for encrypting the contents of a column, and another for decrypting the contents of a column.

Script 1

The following script demonstrates an approach to implementing column-level encryption. It starts by assigning Cryptography as the default database just as in the script from the preceding section. There are four steps in the following script:

Step 1: Creates, populates, and displays a SQL Server table with an ID column and a SensitiveInfo column. Notice that the SensitiveInfo column is defined with a varbinary data type, but not encrypted in Step 1. Three insert into statements populate three rows in the IDs_and_SensitiveInfo table. The values clause in each insert into statement includes a cast function for representing a plain text string in a varbinary format. This is because SQL Server encryption and decryption algorithms are designed to process varbinary values rather than varchar or nvarchar values.

Step 2: Opens both the database master key and a symmetric key named YourSymmetricKeyName. This script assumes that both keys were created in a prior script, such as the script in the previous section.

Step 3: Updates the varbinary values in the SensitiveInfo column of the IDs_and_SensitiveInfo table. The update is achieved with the help of an encryptbykey function that changes the values in the SensitiveInfo column from varbinary values to encrypted varbinary values. The select statement at the end displays the encrypted values in the SensitiveInfo column of the IDs_and_SensitiveInfo table.

Step 4: Closes the symmetric key and the database master key.

use Cryptography
go
 
-- Step 1
-- Drop table if it exists
drop table if exists IDs_and_SensitiveInfo
 
-- Create table with IDs and sensitive info
create table IDs_and_SensitiveInfo(
    ID varchar(100),
    SensitiveInfo varbinary(max)
)
 
-- Populate IDs_and_SensitiveInfo
-- SensitiveInfo column is plain text formatted as varbinary with a cast function
insert into IDs_and_SensitiveInfo (ID, SensitiveInfo) 
values ('ID1', cast('SensitiveInfo_for_ID1' AS varbinary(max)))
insert into IDs_and_SensitiveInfo (ID, SensitiveInfo) 
values ('ID2', cast('SensitiveInfo_for_ID2' AS varbinary(max)))
insert into IDs_and_SensitiveInfo (ID, SensitiveInfo) 
values ('ID3', cast('SensitiveInfo_for_ID3' AS varbinary(max)))
 
-- Display unencrypted contents of IDs_and_SensitiveInfo
select * FROM IDs_and_SensitiveInfo
 
-- Step 2
-- Open the Master Key using the password
open master key decryption by password = 'YFbm8vYd1Cu2E5IQxdo199YhibhUK7hsbWUoZ3id3OQ=';
 
 
-- Open the Symmetric Key
open symmetric key YourSymmetricKeyName
    decryption by password = '+ews06+qA6sGf/qzLECHWG/8LtI3ekSlaFTdA6N4WZw=';
 
 
-- Step 3
-- Encrypt the data in the SensitiveInfo column
update IDs_and_SensitiveInfo
set SensitiveInfo = encryptbykey(key_guid('YourSymmetricKeyName'), SensitiveInfo);
 
-- Display encrypted contents for SensitiveInfo with ID
select * FROM IDs_and_SensitiveInfo
 
-- Step 4
-- Close the Symmetric Key
close symmetric key YourSymmetricKeyName;
 
 
-- Close the Master Key
close master key;   

Here is the output from the preceding script.

• The top results set is for the unencrypted data at the end of Step 1.
• The bottom results set is for the encrypted data at the end of Step 3.

Script 2

The next script shows an approach to decrypting the SensitiveInfo column values that are encrypted in the preceding script. Also, the following script transforms the decrypted varbinary values to their underlying plain text values.

Step 1: Opens the database master key and the symmetric key named YourSymmetricKeyName.

Step 2: Performs two decryption-related operations for transforming the encrypted values in SensitiveInfo column at the end of Step 3 in the preceding script.

• The decryptbykey function decrypts the encrypted SensitiveInfo varbinary values saved in the column by the preceding script. These decrypted varbinary values are saved in the #DecryptedData temporary table.
• Next, a cast function transforms the varbinary values to a varchar format.
• The step concludes with a select statement that displays the decrypted varbinary values along a varchar transformation of the varbinary values.

Step 3: Concludes the script first by closing the symmetric key followed by the database master key.

use Cryptography
go
 
-- Step 1: Open the Master Key and the Symmetric Key
 
open master key decryption by password = 'YFbm8vYd1Cu2E5IQxdo199YhibhUK7hsbWUoZ3id3OQ=';
 
open symmetric key YourSymmetricKeyName
    decryption by password = '+ews06+qA6sGf/qzLECHWG/8LtI3ekSlaFTdA6N4WZw=';
 
-- Step 2: Decrypt the data in the SensitiveInfo column 
-- and store it in a temporary variable
 
-- create a fresh version #DecryptedData
-- with DecryptedSensitiveInfo column in varbinary format
 
-- drop any prior version of temporary table (#DecryptedData)
drop table if exists #DecryptedData;
 
-- decrypt and display SensitiveInfo column from #DecryptedData
select 
 ID
,convert(varbinary(max), decryptbykey(SensitiveInfo)) AS DecryptedSensitiveColumn
into #DecryptedData
from IDs_and_SensitiveInfo;
 
-- display decrypted SensitiveInfo column in both varbinary(max) and varchar(max) formats
select
 ID
,DecryptedSensitiveColumn [DecryptedSensitiveInfo_in_varbinary_format]
,cast(DecryptedSensitiveColumn as varchar(max)) [DecryptedSensitiveInfo_in_varchar_format]
from #DecryptedData
 
-- Step 3: Close the Master Key and the Symmetric Key
 
close symmetric key YourSymmetricKeyName;
 
close master key;

Here is the output from the preceding script. It contains three columns:

1. ID values.
2. Plain text SensitiveInfo values with a varbinary format.
3. Plain text SensitiveInfo values with a varchar format.

Next Steps

This tip targets the needs of a beginning T-SQL developer with no prior experience to encrypt and decrypt columns in SQL Server. You can run the encryption and decryption examples exactly as described in this tip to get a first-hand appreciation for core SQL Server cryptography techniques. These examples create their own sample data so you cannot inadvertently encrypt any business data sources. Feel free to tweak elements of the examples until you feel comfortable about how SQL Server encryption works.

After you become comfortable encrypting and decrypting resources described in this tip, you will be equipped to process the data sources in your company. The next step is to acquire permission to encrypt and decrypt columns in an existing business table, then you can adapt the code in this tip to the development database. Consider making a copy of a target table. This allows you to work independently developing an encryption/decryption solution and not interfere with other developers with the unencrypted target tables. After testing in a development database has been completed, you will be ready to coordinate with senior team members on applying the SQL Server encryption and decryption techniques to secure production data sources.

About the author

Rick Dobson is an author and an individual trader. He is also a SQL Server professional with decades of T-SQL experience that includes authoring books, running a national seminar practice, working for businesses on finance and healthcare development projects, and serving as a regular contributor to MSSQLTips.com. He has been growing his Python skills for more than the past half decade -- especially for data visualization and ETL tasks with JSON and CSV files. His most recent professional passions include financial time series data and analyses, AI models, and statistics. He believes the proper application of these skills can help traders and investors to make more profitable decisions.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips