Tuesday, October 22, 2013

SQL Server Encryption Part I

Encryption is the process of altering data in such a way that hackers cannot read it whereas authorized users can read it. In SQL Server we can encrypt data using a key or password, without having key or password the data cannot be decrypted. Companies that are maintaining sensitive data should meet various compliance requirements such as Gramm-Leach-Bliley Act (GLBA), European Union Data Protection Directive (EUDPD), Health Insurance Portability and Accountability Act (HIPAA), Payment Card Industry Data Security Standard (PCI DSS) and Sarbanes-Oxley (SOX) act. They require encryption of sensitive information like (account numbers, credit card numbers etc) at database levels as well as OS levels.

SQL Server provides various encryption options like database-level encryption, OS level encryption, column-level encryption, and transport-level encryption. 

Transparent Data Encryption (TDE) -- It introduced in SQL Server 2008 and available in Enterprise, Developer in 2008R2 and 2012 versions. It has ability to encrypt an entire database and it is completely transparent to application. It encrypts mdf and ldf files data using AES (Advanced Encryption Standard) and 3DES (Triple DES) encryption methods. This method encrypts the backup file so if we lost the backup media then this backup cannot be restored without a key.

Cell Level Encryption -- It is also known as column level encryption introduced in SQL Server 2005 and this feature is available in all editions including express edition. This methods needs application must be changed to use encryption and decryption operation, in addition it affects performance.

Encrypting and Decrypting Data with .NET Framework -- SQL Server stores encrypted data but encryption and decryption is performed thru application. Application need to perform encryption and decryption by calling specific methods.

SQL Server encrypts data with a hierarchical encryption and key management infrastructure. It is three layer hierarchies, OS Level, SQL Server Level and Database Level.

The service master key (SMK) is the top-level key and is the father of all the keys in SQL Server. The SMK is an asymmetric key that encrypt by the Windows Data Protection API (DPAPI). The SMK is automatically created when you encrypt something for the first time and tied to SQL Service account. The SMK is used to encrypt database master key (DMK). The second layer of encryption is DMK, It encrypts symmetric keys and asymmetric keys and certificates. Each database will have only one DMK.

Symmetric Key -- In this cryptography the sender and receiver will share a common key to encrypt or decrypt the message. This is easy to implement and the sender and receiver can encrypt and decrypt the messages.

Asymmetric Key -- This cryptography is also known as public-key cryptography in which sender and receiver will have a pair of cryptography keys known as public key and private key to encrypt and decrypt messages. In this method sender has to use his key to encrypt the message whereas he couldn’t decrypt the message and receiver has to use his key to decrypt the message whereas he couldn’t encrypt it. This is a resource intensive process. 

No comments: