Encryptions in SQL Server

                  Security is the biggest concern nowadays particularly for those who manage critical applications.Every database must have high secured features to ensure the data is always safe. In recent years the rate of ransom attack has increased a lot. Encryption feature protects data from a threat as well as hacking. In this article, we are going to discuss the various encryption techniques available in SQL Server. Encryption can be carried out by the two methods. The below are the two methods:

• Column-level Encryption(CLE)
• Transparent Data Encryption(TDE)

CLE:

Features:

• • Column level encryption supported by SQL server 2005 server & available for all editions.

• • It is a method of database encryption in which the information in every cell (or data field) in a particular column has the same password for access, reading, and writing purposes.

• • This method uses a master key & certificate method to encrypt a particular column of data which can be viewed

• The data in the particular column will be an encrypted format

Drawbacks

• • It has reduced or limited available query optimization functions

• Increased data file size, increased demands on system resource

TDE:

Features

• • Transparent data encryption feature is present only in the SQL Server Enterprise edition of 2008,2012.

• • It’s not available for the other SQL server editions such as the Business Intelligence, Standard or Express.

• • Protects data by encrypting the physical files of the database, rather than the data itself.

• • TDE protects data “at rest”, meaning the data and log files.

• • TDE performs real-time I/O encryption and decryption of the data and log files.

• • The process involves encryption of data using the key & certificate creation.

• Protect backup from unauthorized access.

Drawbacks

• • When enabling TDE, you should immediately back up the certificate and the private key associated with the certificate.

• • If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

• Increase in the performance cost if the encrypted data is an index.