SQL Encryption

Nowadays securing our valuable information becomes important. Our critical data faces a threat from viruses, hackers, malwares etc.

In order to secure our data from these threats, we have lots of methods and important among them is encryption.

Encryption will make our data or information to unreadable format until, we have right key to decrypt or read the data.

Encryption Algorithm

Its allows us to convert the plain text to an encrypted data. Encrypted data is called as the Cipher text and then it will be stored in the devices.

Encryption Algorithm uses keys to encrypt and decrypt the data. SQL Server has two kinds of keys symmetric and asymmetric.

Symmetric Key

It uses same password for encryption and decryption. For e.g. sender encrypt the information using the common password and encrypted data is transferred on the network. Once the receivers get information he will decrypt using the common password.

In this case both the sender and receiver should know that common password to decrypt.

Asymmetric Key

Unlike Symmetric, it uses distinct password for encryption and decryption. Asymmetric uses public key(password) for encryption and private key for decryption.

For e.g. Initially the receiver shares public key with the sender, then the sender encrypts the data using that public key and send it over the network. Once the target receives that data, it decrypts using the private key.

Asymmetric key is more secure because data are decrypted using the private key and it is not transferred on the network.

SQL Server supports several symmetric key encryption algorithms, including DES, Triple DES, RC2, RC4, 128-bit RC4, DESX, 128-bit AES, 192-bit AES, and 256-bit AES.

DPAPI

It is Windows level Encryption, Hence the SQL server mostly on the windows machine Service level master key is encrypted using this key.

SMK (Service Master Key)

Once the SQL Server is installed successfully, SMK will be automatically created. It takes care of encrypting the user logins, passwords etc.

DMK (Database Master Key)

It is symmetric key to encrypt all other encryption related object in the database. For e.g., certificate, symmetric keys and password.

Certificate

It is also used encrypt the symmetric key. Unlike Encrypting using password phrase, certificate has the expire date.

Symmetric key

This key is to encrypt the actual data.

Note: we request you to kindly know about below mentioned difference. It will be helpful on encryption.

Difference between the Key and password parse?

Difference between encrypt the data using certificate and symmetric key?

Below example show how to encrypt the particular column using the symmetric key.

Before going into the below steps, please create Database named Finance

Step 1: First we have to create the DMK

Use Finance

Create master key encryption by password = ‘login@123’;

Step 2: Then provide below command to create the certificate

Create certificate Geopits with subject = ‘Finance certificate’

Step 3: This command is to create the symmetric key

Create symmetric key Finance_key With

Algorithm = AES_256

Encryption by certificate geopits

Step 4: This command is to create a table for encryption

create table [Finance_details] (Emp_Name varchar(50),Pan_Details varchar(60), Pan_Details_Encrypt varbinary(max))

Step 5: This command is to insert the values on to the tables

Insert into [Finance_details] values (‘Arun’,’AKBE54889', Encryptbykey(key_GUID(‘Finance_key’),’AKBE54889'));

Note for encrypting any values the symmetric key need to be opened, by executing the below command we check whether symmetric key opened or not.

Use Finance_details

select * from sys.openkeys

For Decrypting the please execute the below command

Select * ,cast(decryptbykey(Pan_details_encrypt) as varchar(8000)) as decrypted_text from [finance_details]