SQL SERVER – SQL Server Encryption and Decryption with Script

Steps for encryption and decryption cell values in SQL Server.


Step 1: Create table and populate data.
Create a Master key(DMK-Database Master Key)

CREATE TABLE TestTable (Id INT, TextValue VARCHAR(50))
GO

INSERT INTO TestTable (Id, TextValue)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Teciners#123'
GO

The master key (DMK) in a database is encrypted by SQL server using its service master key (SMK).

SMK is created by a SQL Server instance when it starts up the very first time after installation. For any set of instances of SQL server, the SMK will always be different. So, the DMK in the database will be encrypted differently on each server.

Step 2: Create Certificate

CREATE CERTIFICATE EncryptTechinersCert
WITH SUBJECT = 'Techiners#123'
GO

Step 3: Create Symmetric Key and Secure the Symmetric Key with the certificate

CREATE SYMMETRIC KEY TechinersKey
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE EncryptTechinersCert

Step 4: Open the Symmetric Key and Select DB/COLUMN/CELL on which we want to implement encryption.

Before the key is used, it needs to be decrypted using the same method that was used for encrypting it. In our example, we have used a certificate for encrypting the key. Because of the same reason, we are using the same certificate for opening the key and making it available for use. Subsequent to opening it and making it available for use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptTextValue column.

--ALTER TABLE TestTable
--ADD EncryptTextValue VARBINARY(256)

OPEN SYMMETRIC KEY TechinersKey DECRYPTION
BY CERTIFICATE EncryptTechinersCert
UPDATE TestTable
SET EncryptTextValue = ENCRYPTBYKEY(KEY_GUID('TechinersKey'),TextValue)
GO

The above query will encrypt entire column. But, in some scenarios we require only particular cell values to be encrypted, then we can use below mentioned query.

--OPEN SYMMETRIC KEY TechinersKey DECRYPTION
--BY CERTIFICATE EncryptTechinersCert
--UPDATE TestTable
--SET EncryptTextValue = ENCRYPTBYKEY(KEY_GUID('TechinersKey'),TextValue)
--where Id in (2,5)
--GO

Step 5: Check the data whether it is safe or not i.e., encrypted or not.

SELECT *
FROM TestTable
GO

Step 6: Decrypt the data

Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column.

If Symmetric key is not open for decryption, it has to be decrypted using the same certificate that was used to encrypt it.

An important point to bear in mind here is that the original column and the decrypted column should have the same data types.

If their data types differ, incorrect values could be reproduced.

In our case, we have used a VARCHAR data type for TextValue and EncryptTextValue.

OPEN SYMMETRIC KEY TechinersKey DECRYPTION

BY CERTIFICATE EncryptTechinersCert
SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptTextValue)) AS DecryptTextValue
FROM TestTable
GO

Step 7: Close Symmetric Key. (Can aslo drop all keys)

CLOSE SYMMETRIC KEY TechinersKey
GO

You can drop keys that you have created above. Dropping entire database will drop all the associated keys.

DROP SYMMETRIC KEY TechinersKey
GO
DROP CERTIFICATE EncryptTechinersCert
GO
DROP MASTER KEY
GO