Tuesday, January 26, 2016

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

No comments:

Post a Comment