Encryption in SQL Server

Encryption

Encryption hierarchy is marked by three-level security. These three levels provide different mechanisms for securing data across networks and local servers. 

• Windows Level – Highest Level – Uses Windows DP API for encryption
• SQL Server Level – Moderate Level – Uses Services Master Key for encryption
• Database Level – Lower Level – Uses Database Master Key for encryption

There are two  kinds of keys used in encryption

Symmetric key encryption

In symmetric-key schemes,the encryption and decryption keys are the same. Communicating parties must have the same key before they can achieve secure communication.

Public key encryption

Illustration of how encryption is used within servers Public key encryption.
In public-key encryption schemes, the encryption key is published for anyone to use and encrypt messages. However, only the receiving party has access to the decryption key that enables messages to be read.

There are two different kinds of encryptions available in SQL Server

Database Level – This level secures all the data in a database. Performance is bad is not  and not a practical solution.


Column (or Row) Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted

/*** Create Master key **/

USE TESTDB

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD = 'AmlMigration098'

GO

/** Create encryption certificate **/

USE TESTDB

GO

CREATE CERTIFICATE TestCertificate

WITH SUBJECT = 'AmlMigration098'

GO

/** Create Symmetric Key */

USE TESTDB

GO

CREATE SYMMETRIC KEY TestKey

WITH ALGORITHM = TRIPLE_DES ENCRYPTION

BY CERTIFICATE TestCertificate

GO

/* Update binary column with encrypted data created by certificate and key */

USE TESTDB

GO

OPEN SYMMETRIC KEY TestKey DECRYPTION

BY CERTIFICATE TestCertificate

UPDATE TableA

SET EncryptedColumn= ENCRYPTBYKEY(KEY_GUID('TestKey'),ColumntoEncrypt)

GO

/* Decrypt to the data*/

USE TESTDB

GO

OPEN SYMMETRIC KEY TestKey DECRYPTION

BY CERTIFICATE TestCertificate

SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptedColumn)) AS DecryptColumn

FROM TableA

GO