SQL SERVER – Cryptography in SQL Server 2008

SQL Server, particularly the 2005 and 2008 versions, offers the functionality of cryptography. In the following, this functionality is briefly explained.

Introduction

Any database professional will support the encryption of data. However, the encryption of data has to be carried out at the database engine level. This is quite tricky as there the database performance can be affected by the process of decryption, data manipulation, and then re-encryption when data is being updated. SQL Server offers robust data security. Further, it is important to have strong knowledge of cryptography in SQL Server in order to avoid many problems that are encountered by DBAs while implementing cryptography.

SQL Server Authentication

When users log in to the database, there are two major steps of identifying the user: authentication and authorization (permissions).

• Authentication implies the verification of user’s identity or the security principal. Encryption and signature play an important role in the authentication process.
• Authorizations (also known as permissions) are the rights granted to a particular user. Permissions to a signature in SQL Server.

Protection of SQL Server Data by Encryption

At times, one can adequately protect data by controlling access to a table by using SQL Server robust permissions architecture. Therefore, what is the necessity of encrypting data on the server.

In general, if the storage medium such as SAN and backup tapes is completely secure, it is not necessary to encrypt data. If an attacker can break the permissions architecture, then he/she can possibly access the encryption keys of your database with very little effort. In such case, encryption cannot help.

However, if an attacker wants to access the file system, encryption defends the system to a greater depth.

Encryption is necessary for the data that fall in the following categories:

• For vulnerable storage media, wherein there are chances of loosing the backup data from PC or tapes.
• In some cases, the data has to be protected from sysadmins. In such an instance, it is advisable to use the middle tier to perform encryption and decryption, rather than SQL server internal encryption. However, if the keys are stored in the SQL Server, it is almost impossible to protect the data from a sysadmin who is determined to find the keys.
• If a sets of records (rows) within the same table needs to be protected, then users with cryptographic keys can be allowed to access the specific records, but not the other records within the same table. Encryption makes this process of hiding rows easy. In the case of regulating the permissions to columns, permissions on views can be enforced.
• Highly sensitive data requires encryption for storage. Such data include information on credit cards and military projects.

Third-party backup options that enable the encryption of backup files can be considered in situations wherein offsite tape storage is less secure and the disk storage access is secure.

Conclusion

The cryptographic functionality of SQL Sever has to deal with problems that were not taken into consideration during its design. Some common examples are as follows:

• The use of asymmetric key pairs in cases where key communication is not a crucial issue; for example, when data is stored in the SQL Server database.
• SQL Server permissions are sufficient to control access to data, except in situations where the access to the storage media is compromised. In such case, the security via encryption stands unnecessary.

Importantly, by using the cryptographic functionality of SQL Server, it is easy to create robust security. The native functionality offers access control and key storage, and the built-in encryption functionality offers robust security (including random salt and authenticator values). The ease in implementation of this functionality makes it an attractive choice for all types of users.

Reference: Pinal Dave (https://blog.sqlauthority.com), White paper by John Hicks, Microsoft Industry Solutions Group