Encryption Technique Using SQL Server 2014

Database Security encryption technique

Purpose:

Due to the increase in number of data crimes in the world, Database Security is a major concern in organizations today. Especially, organization who is handling sensitive data like finance, health and security.

They are keen to prevent data using encryption technique. SQL encryption technique is most popular today. SQL Server provides a variety of encryption approaches.

Symmetric encryption:

This algorithm either takes unencrypted data and return encrypted data, or they take encrypted data and return unencrypted data and work in both way. It can use key to encrypt data in SQL table. Symmetric algorithm is faster than the asymmetric encryption.

In symmetric key encryption technique both the sender and receiver have same key to encrypt the data in which both sender and receiver can encrypt the data. It is easy to implement.

Asymmetric key encryption: Asymmetric key technique is different from the symmetric key technique. It is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it.

Symmetric encryption algorithm types are the following:

• DES (DES)
• Triple DES with 128 bit key (TRIPLE_DES)
• Triple DES with 192 bit key (DESX)
• RC2 (RC2)
• RC4 (RC4)
• RC4 with 128 bit key (RC4_128)
• AES with 128 bit key (AES_128)
• AES with 192 bit key (AES_192)
• AES with 256 bit key (AES_256)

Des:

This function applies 48 bit keys and 32 Bits Ri to produce a 32 Bit output and is is made up to four operations,

1. an XOR
2. an expansion permutation
3. a group of S-boxes
4. a straight permutation

S EXPANSION PERMUTATION STRAIGHT PERMUTATION + S S S S S S S XOR 48 Bits 48 Bits 32 Bits 32 Bits Ri 32 Bits Ki 48 Bits S-boxes DISADVANTAGE: The key size is too small, it’s only 64 bits long DATA ENCRYPTION STANDARD (DES).

TripleDES: It is the improved version of des algorithm. DES uses 56 bits of a 64-bit key to encrypt messages in fixed-sized blocks of data and Triple DES use 3 DES.

ADVANTAGE: Key size of 3DES is larger than DES.
DISADVANTAGE: The process of 3DES is very slow.

RC2:

It is the replacement of the DES. RC2 encrypts data in 64-bit blocks and has a variable key size of 8 to 128 bits in 8-bit increments. Encryption algorithms strength lies in the length of its keys. If size of key is big then encryption algorithm is more strong.

Elaborate Triple Des with 192 bit in more details. This algorithm is considered more secure and faster.

Steps to create symmetric key and use in database encryption: Before creating the symmetric key we need to create master key and a certificate, which act as protectors of our symmetric key store.

Create a Database Master Key

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘myStrongPassword’

Create a certificate

CREATE CERTIFICATE MyCertificateName
WITH SUBJECT =’Label for Certificate’

After created master key and certificate now we add symmetric key to certificate.

Create a Symmetric Key

CREATE SYMMETRIC KEY MySymmetrickeyName
WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE MyCertificateName

After creating the symmetric key we use that key for encrypt/decrypt the data. Firstly, we need to open symmetric key to encrypt/decrypt the data.

Query to open Symmetric key

OPEN SYMMETRIC KEY MySymmetricKeyName
DECRYPTION BY CERTIFICATE MyCertificateName

Now, we can use encrypt function to encrypt the data using the following query.

Encrypting Data Query

DECLARE @Result varbinary(256)
SET @Result = ENcryptByKey(Key_GUID(‘MySymmetricKeyName’)),
    @ValueToEncrypt)
Decrypt Data Query
DECLARE @Result varbinary(max)
SET @Result = ENcryptByKey(Key_GUID(‘MySymmetricKeyName’)),
    @ValueToEncrypt)

Steps to implement the Encryption feature in database with Screenshots,

Step 1: Query to create user table for implementing encryption process.
Step 2: Query to insert data in user table.
Step 3: Create a master key in database using below query.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EncryptionUser@123'
Step 4: Now create certificate in same database using below query.
CREATE CERTIFICATE UserTestCert WITH SUBJECT = 'EncryptionUserCert'
Step 5: Create symmetric key.
CREATE SYMMETRIC KEY EncryptTableKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION
BY CERTIFICATE UserTestCert
Step 6: After creating symmetric key in database use that key to encrypt and decrypt the data.
Step 7: After executing the update query data is in encrypted mode.
Step 8: Query to decrypt the data.

Queries to drop the key and certificate from database,

CLOSE SYMMETRIC KEY EncryptTableKey
GO
DROP SYMMETRIC KEY EncryptTableKey
GO
DROP CERTIFICATE EncryptionUserCert
GO
DROP MASTER KEY
GO

Advantages of using Encryption technology,

1. Provide security to application from unauthorized user.
2. Identity Theft Protection
3. Used for highly sensitive data like defense, financial, health.

Disadvantages of Encryption

1. More damage if keys or certificate compromised.
2. Slow in performance

Conclusion

Encryption is a very important security feature of SQL Server. Asymmetric key has a stronger encryption approach. Stronger encryption is slower to process and take longer time to encrypt the data. When there is a huge amount of data to encrypt, it is suggested to encrypt it using a symmetric key. Also, it is faster than asymmetric key. It is also recommended to compress data before encryption, as encrypted data cannot be compressed.