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]
Further reading
If you found this article helpful, you might want to check out these related resources,
