SQL server column encryption using symmetric key

In this article we’ll learn about SQL server Column encryption using Symmetric key. In Financial organizations or banks, we have sensitive data including account numbers and credit card numbers. As the data is stored in the columns and one can read this using simple select statement from that table. So, here instead of encrypting the whole DB or table we can encrypt the given column data and it will become secure. One who will have the certificate can view this data in readable format and for others it will remain as encrypted. So, how to do this let’s check out below step by step.

Step 1 – Create a sample table.

USE Test_BankDB;
GO
-- Create Table
CREATE TABLE [dbo].[User](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[AccountNo] [nvarchar](50) NULL,
	[CreditCard] [nvarchar](50) NULL,
	[AccountNo_Encrypted] [varbinary](max) NULL,
	[CreditCard_Encrypted] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

--Now insert some data into this sample table 
GO
SET IDENTITY_INSERT [dbo].[User] ON 
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (1, N'Mukesh', N'1256734567', N'985645356432', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (2, N'Rakesh', N'4567239012', N'756460742257', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (3, N'Smith', N'5344843622', N'356801345657', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (4, N'John', N'2345345689', N'235476956701', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (5, N'Peter', N'4597941437', N'354903343577', NULL, NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [AccountNo], [CreditCard], [AccountNo_Encrypted], [CreditCard_Encrypted]) VALUES (6, N'James', N'3265768999', N'324789976762', NULL, NULL)
GO
SET IDENTITY_INSERT [dbo].[User] OFF
GO 

Step 2 – Create SQL Server’ Service Master Key‘

Service Master Key is very important for SQL server data encryption, it is by default created when we install MS SQL and create the instance. It must be preset in your DB and if it does not exist we need to manually create it.

USE master;
GO
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';

Step 3 – Create your ‘Database Master Key‘

In next step we will create Master key for the DB whose data we need to encrypt. The “encrypt by password” argument is required and it will accept password which will get used to encrypt the key. You need to keep the password safe for future usage and you can also take the backup of your master key for future reference.

-- Create database Key
 USE Test_BankDB;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Codeconfig@123';
GO

You can view your Database master key using below query that it is created successfully or not. Its output will be same as shown in below image.

USE Test_BankDB;
GO
SELECT * FROM sys.symmetric_keys

Step 4 – Create a Self-Signed SQL Server Certificate

Next, we will create a self-signed certificate that is protected by the database master key (which we create in step 3). Our certificate can be protected by password or by database master key. We’ll use database master key on our case.

-- Create self-signed certificate
USE Test_BankDB;
GO
CREATE CERTIFICATE CertBank1
WITH SUBJECT = 'Secure_Data';
GO

Run this certificate creation query again, it will say that it is already created which means that certificate is successfully created in your DB.

Step 5 – Create SQL Server Symmetric Key

Encryption and decryption by using a symmetric key is fast and same key will get used for both encryption and decryption process.

-- Create symmetric Key
USE Test_BankDB;
GO
CREATE SYMMETRIC KEY SymmetricKey1 
 WITH ALGORITHM = AES_256 
 ENCRYPTION BY CERTIFICATE CertBank1;
GO

You can run below query to check “SymetricKey” in DB. It output will be as shown in below image which indicate that your key is created successfully.

USE Test_BankDB;
GO
SELECT * FROM sys.symmetric_keys

Step 6 – Encrypting the SQL column data

Before running below query, our table will look like this.

You can run following query which will update your column with required encrypted data. Output will appear like this.

-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE CertBank1;
GO
UPDATE [dbo].[User]
SET [AccountNo_Encrypted] = EncryptByKey (Key_GUID('SymmetricKey1'),[AccountNo]),
    [CreditCard_Encrypted] = EncryptByKey (Key_GUID('SymmetricKey1'),[CreditCard])
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO
-- select The rows now
Select * from [dbo].[User]

Step 7 – How to Read SQL Server Encrypted Data

The data which you have encrypted can be read using “DecryptByKey” as shown in below query.

---How to Read SQL Server Encrypted Data
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE CertBank1;
GO
    select id, [name],
                 CONVERT(nvarchar(max),DecryptByKey([AccountNo_Encrypted])) as AccountNo_Encrypted ,
                 CONVERT(nvarchar(max),DecryptByKey([CreditCard_Encrypted])) as CreditCard_Encrypted
	 from [dbo].[User]
GO
-- Close the symmetric key once done
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Output of above query will be as shown in below image, and you will be able to read encrypted data only if you’ll pass correct name of Certificate and symmetric key. If you lost Certificate and symmetric key, then data will not be able to decrypt.

In this article you have learned about SQL server Column encryption using Symmetric key. We hope after reading this article you’ll able to use this in your daily development practices. For similar details you can also check out Microsoft Docs. For more such articles you can Visit our SQL Section.

You may also like:

• How to Update using Select statement in SQL Server.
• How is data physically stored in a SQL database
• SQL Server Interview Questions and Answers.
• Import One Database to another in SQL server.
• Generate database scripts in SQL server.
• Different ways to search SQL objects in database.
• SQL CASE statement
• SQL IIF function
• QUOTENAME function in SQL
• How to Take SQL Server Database Offline/Online
• How to use SQL ORDER BY in Your Queries
• SQL Paging using OFFSET and FETCH