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.
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:
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.