SQL Server provide encryption as one of the new feature to protect data against external hacker attacks.
Encryption hierarchy can be possible in a three-level security. These three levels provide different mechanisms for securing data across networks and local servers. Different levels of hierarchies allow multiple instances of services (e.g., SQL Server Services) to run on one physical server.
• 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 –Both the sender and the receiver of a communication share a single key that is used to encrypt and decrypt the message. This is relatively easy to implement.
• Asymmetric Key – It is also known as Public-key cryptography. In this the sender and the receiver of a communication have a pair of cryptographic keys. One is a public key and another is private key which are used to encrypt and decrypt the communication. This is a relatively complex to implement.
Two levels of Securing Data are:
• Database Level – This level secures all the data in a database. However, every time data is written or read from database, the whole database needs to be decrypted. This is a very resource-intensive process and not a practical solution.
• Column Level – This level of encryption is the most preferred method. Here, only columns containing important data should be encrypted; this will result in lower CPU load compared with the whole database level encryption.
In SQL Server 2008, Transparent Data Encryption (TDE) is introduced which very powerful Encryption with as little overhead for maintenance. Please read my post here for more information.