In today’s digital era all things got digitalized. So the information is a very confidential thing in today's era to protect that from unauthorize and unauthenticated access. We need to store that data in a confidential form for this we have to encrypt and decrypt data.
Today we are going to discuss one of the ways for the encryption and decryption at the column level in SQL Server.
There are three major factors to encrypt data at the column level :
Master key: A key which is used to protect the data of certificates and symmetric keys in database.
Certificates: Used to encrypt the data in database.
Symmetric Key: It can be encrypted by using many options like certificate, password, symmetric key.
The supported algorithms are DES, TRIPLE_DES ,RC2 ,RC4 ,RC4_128 ,DESX ,AES_128 , AES_192 and AES_256.
So let’s start step by step to achieve encryption and decryption.
Step 1: Create a Master key first with the help of below command
Create master key encryption by password = 'test12345';
The encryption of SQL server table starts with master key for the encryption of symmetric key. This can be done using above command here we have to define password for the encryption of symmetric key.
Step 2: Create a certificate to secure symmetric key
Once the Master key is created, now we have to create a certificate to secure symmetric key using a digitally signed certificate we can create the certificate using the below command which will be protected using database master key.
Create certificate C1 with subject = 'Confidential data';
Step 3: Now with the help of master key and certificate let’s create SYMMETRIC KEY
Syntax for creating symmetric key is as follows.
Create symmetric key Key_name with [[key_options] | ENCRYPTION BY encrypting_mechanism]
Key_name: Specifies the unique name by which the symmetric key is known in database.
Key_option: In key option, we are providing algorithm.
Create symmetric key SK1 with algorithm = AES_256 encryption by certificate C1
Note: For creating symmetric key it must be encrypted using at least one of the following:
Password, Certificate, Symmetric key, Asymmetric key or Provider. The key can have more than one encryption of each type. It means that a single symmetric key can be encrypted by using multiple certificates, passwords, symmetric keys and asymmetric keys at the same time.
Once all these KEYs are created in database, we can use those for encrypting and decrypting data.
Now by using these keys we encrypt and decrypt data in database.
First, we create a table with name TestEncryption. And the data type of column which we encrypt or decrypt must be VARBINARY.
Now we add three columns in TestEncryption table:
ID - INT
CreditCardNo - NUMERIC (18,0)
EncryptedCreditCardNo - varbinary(256)
Let’s insert data into this table
First, we open symmetric key SK1 and then we have to do Decryption by certificate C1 using below command.
DECRYPTION BY CERTIFICATE C1;
For Insert operation, we have to use the below command.
INSERT INTO TestEncryption(Id, CreditCardNo,EncryptedCreditCardNo) VALUES (1, '5000',ENCRYPTBYKEY(key_guid('SK1'),'5000'))
Now we closed the open symmetric key using the below command.
Now it's time for checking encrypted data.
SELECT * FROM TestEncryption
Now let's check the original data using the below command.
First, we open a symmetric key which got used for encryption of data.
DECRYPTION BY CERTIFICATE C1;
SELECT convert(varchar, DECRYPTBYKEY(EncryptedCreditCardNo)) AS 'CreditCard Number' FROM TestEncryption
Now we close the symmetric key SK1
Now we got one column name as CreditCard Number.
Once we have closed the Symmetric key, we will get null values in encrypted column.
Hence we have to ensure that we always open Symmetric key before starting encryption/decryption using query.
To achieve the main purpose of this process we can now drop the CreditCardNo column.
For professional paid support, you may contact us at