Encryption: The Last Line of Database Defense

Alastair Aitchison
(The following is an excerpt from "Expert SQL Server 2008 Development" published by APress.)

Encryption is the process of encoding data in such a way as to render it unusable to anyone except those in possession of the appropriate secret knowledge (the key) required to decrypt that data again. The encryption capabilities provided by SQL Server 2008 are powerful features, and you should always consider using them as part of your overall security strategy. If all other security mechanisms fail, encryption can prove to be a very effective last line of defense in protecting confidential data.

Encryption should not be considered lightly, however; it is a complex subject, and the successful implementation of encryption almost invariably requires some degree of consideration at the architecture and design phase of an application. The physical act of encrypting data is relatively straightforward, but without the appropriate procedures in place to securely manage access to that data, and the keys required to decrypt it, any attempt to implement encryption is worthless. A failure to implement encryption properly can actually do more harm than good, as it leads you into a false sense of security believing that your data is safe, when in fact it is exposed to potential hackers.

Furthermore, the additional protection afforded by encryption has an associated performance cost. Typically, the database has to do more work to encrypt written data, and to decrypt it again when that data is required. This means that, particularly in large-scale environments, careful attention must be paid to ensure that encryption does not adversely affect application performance.

What follows is a practical guide to using the main encryption features of SQL Server 2008 to create a secure, scalable database application that is capable of working with confidential data. Such a solution clearly assumes that other aspects of security, including user access control and network protection, have already been adequately addressed. I will particularly focus on the two main issues identified in the preceding paragraphs-namely, how to securely store and manage access to encryption keys, and how to design a database application architecture that protects sensitive data while minimizing the negative impact on performance.

Do You Really Need Encryption?

Before addressing the question of how to implement encryption for a given data set, it is necessary to consider whether encryption is required at all. Encryption is a method of protecting data, so the key questions to ask are what data are you trying to protect, and who (or what) are you trying to protect it from.

What Should Be Protected?

All data is important. If it were not, then we wouldn't bother storing it in a database in the first place. But that doesn't necessarily mean that all data needs to be encrypted.

Most of the encryption methods in SQL Server 2008 provide cell-level encryption, which is applied to individual items of data, or columns of a table that contain sensitive information. In contrast, transparent data encryption, a new feature in SQL Server 2008, applies database-level encryption to an entire database by encrypting the underlying database files on the file system. Not only do these approaches operate at a different scope, but they have significantly different implications for the design of any application working with encrypted data.

Before deciding on an encryption strategy, it is very important to classify data in order to identify the risk of exposure, and the severity of consequences should that data become exposed. Some examples of the types of data that might require encryption are as follows:

  • Many organizations define one or more levels of sensitive data-that is, information that could negatively harm the business if it were allowed into the wrong hands. Such information might include pricing information, profit forecasts, or upcoming marketing plans. If a competitor were to get hold of such information, it could have severe consequences on future sales.
  • Increasingly, many governments are passing laws and regulatory requirements specifying certain minimum levels of security that must be put in place to protect confidential customer data, and this can include encryption. A failure to meet these standards may result in a company facing severe fines, or even being forced to cease trading.
  • Certain clients may specify minimum levels of protection required as part of a customer service level agreement. In some cases, detailed requirements specify exactly those data items that must be encrypted, the type of encryption algorithm used, the minimum length of encryption key, and the schedule by which keys must be rotated.
  • In many cases it may be beneficial to encrypt employee records, particularly those that contain payroll information or other personal information, in order to prevent casual snooping by systems administrators or other users who have access to such data.

Conducting a thorough analysis of business requirements to identify all data elements that require protection and determining how that data should be protected are crucial to ensure that any proposed encryption solution adequately addresses security requirements while balancing the need for performance. In addition, the required changes to application design and business processes associated with encryption generally come at a cost, and it is important to be able to justify that cost against the perceived benefits to be gained from encryption.

What Are You Protecting Against?

Perhaps the most basic protection that encryption can offer is against the risk posed to data at rest. In SQL Server, data at rest refers to the underlying database files stored on the filesystem, including transaction logs and backups. If an unauthorized third party could gain access to those files, it would be possible for them to copy and restore the database onto their own system, allowing them to browse through your data at their leisure. If sensitive data were stored in the database in an encrypted format, however, the attacker would also need to know the relevant keys or passwords to decrypt the stolen data, without which it would be worthless to them.

Besides physical theft of database files, hackers and external threats pose a continuing risk to database security. Hackers are often very knowledgeable, and well-equipped with a range of tools to try to sniff out and exploit any weaknesses in your security systems. By ensuring that all data transmitted over a network is securely encrypted, you can be sure that it can only be understood by its intended recipient, reducing the chance of it being successfully intercepted and used against you.

Many organizations also recognize the threat to data security arising from internal sources. Disgruntled DBAs and developers testing the limits of their authorization can access data they do not require in order to do their jobs. Such information could easily find its way out of the organization and into the wrong hands if the right price were offered. If properly designed and deployed, an encryption strategy can prevent this risk from occurring.

Remember that encryption is not a replacement for other security measures, such as appropriate authentication and authorization procedures. However, if implemented correctly, it can provide an additional level of security that might be the difference between your data becoming exposed and remaining secret as intended.

Add Comment      Leave a comment on this blog post

Post a comment





(Maximum characters: 1200). You have 1200 characters left.



Subscribe to our Newsletters

Sign up now and get the best business technology insights direct to your inbox.