• Eric Rouach

How To Encrypt Data In SQL Server

Author: Eric Rouach, Madeira Data Solutions.

Date: 08/05/2021

link to full-demo script

The attached scripts demonstrate two common use-cases of sensitive-data encryption:

Use-Case 1 – encrypt and keep the data decryptable

Using as an example the AdventureWorks2014 database, the first script describes the process of encrypting the “CardNumber” column from the Sales.CreditCard table while keeping the data decryptable.

Our pre-requisite is the creation of a Master Key, a Certificate and a Symmetric Key.

Once having those created, we may proceed to the addition of a new column called “CardNumberEnc” (where the suffix “Enc” stands for “Encrypted”). This column has a VARBINARY(250) Data Type and is nullable.

We can then populate the newly created column with an encryption of the existing data by following these steps:

-Open the symmetric key

-Update the table and set the new column values using the “EncryptByKey” function.

Optionally, we may use an authenticator for an additional security layer (e.g. the ID column) using the syntax as shown in the following screenshot:

EncryptByKey(Key_GUID('AW2014SymKey'), CardNumber, 1, CONVERT(VARBINARY, CreditCardID))

One the new column is populated, follow the following steps:

-Make the new column non-nullable

-Drop the old column

-Rename the new column to the old one’s name

-Close the symmetric key

-Create a nonclustered index on the new column

To decrypt the data, use the DecryptByKey function as shown is the script.

In case you have used an authenticator, use the following syntax:

CONVERT(NVARCHAR(25), DecryptByKey(CreditCard.[CardNumber], 1, CONVERT(varbinary, CreditCardID))) AS CardNumber

Use-Case 2 – encrypt and make the data non-decryptable

In this case, we do not need to create keys and/or certificates.

In a similar way as that of the first case, we will add a new column. The new column population process is the main difference here: we will use the HASHBYTES function as shown below:

HASHBYTES('SHA2_256', CardNumber)

You may use a salt as an extra security layer using the following syntax instead:

UPDATE [Sales].[CreditCard]


CardNumberEnc =

HASHBYTES('SHA2_256', CardNumber+CAST([CreditCardID] as NVARCHAR(250)))



Recent Posts

See All

HOW NUMA nodes might help you -

One day I received a message from one of my clients who said he is experiencing HIGH Duration of queries. Together we tried to look for queries which cause this issue, but without any luck. so, I chan


Get New posts delivered straight to your inbox

Thank you for subscribing!