It is useful to have a ‘big picture’ understanding of the possible layers of encryption available for data-at-rest in a data-centric application. A visual that conveys those layers is challenging due to the number of dimensions that must be considered. Here is my best shot at the visual – for today anyway. If you find it easy to understand the diagram at a glance there is probably nothing of interest in what follows (and there is a jar in the Smithsonian with your brain’s name on it):
On the other hand, if your are like me it may look confusing and way too busy. Makes me want another cup of coffee. Nonetheless, I happen to know it will make perfect sense to all once broken down into 3 coherent parts.
I will begin with the middle part. You may notice that a user database with a data file group and a FILESTREAM file group – dangling precariously off to the side of the database -is depicted. Along side the user database are the four system databases. The only piece of this picture that may be unfamiliar to some is the FILESTREAM file group that is part of the database but actually lives outside of the database. This is intended to represent a typical FILESTREAM enabled configuration. Nothing special.
generic db (select to view full size)
The databases are enveloped in various cryptographic building blocks:
Behind, architecturally underlying the databases are SQL Server’s Transparent Data Encryption (TDE), a file encryption container that is less than the physical volume in size (e.g. an encrypted file that is able to present a VHD) and also a fully encrypted system (e.g a drive, storage array, or complete file system). These encryption layers are designed to encrypt everything placed in storage in the background. No application change is supposed to be required. Also, the encryption protection cannot be avoided or forgotten in development. In fact, implementation is a configuration issue and should be done by the database’s Administrator.
container scoped encryption layers
The main thing to notice here is that I have stretched and skewed the building blocks to cover as many scenarios as possible in the diagram. With 3 blocks 9 dimensional states can be represented: state 1 being no encryption and at the other end state 9 – the darkest pinkest rectangle toward the center in the image above – being TDE of database pages into an encrypted file exposed as a VHD on top of an encrypted file system. In between are all the combinations visible as overlap of the blocks in the image.
Perhaps of interest, these container scoped layers are not available to a SQL Azure database.
In front of, or structurally within the database are several useful database object and column level cryptographic layers. These layers scramble data, verify that nothing has changed, protect intellectual property, protect personally identifiable information, determine authenticity and extend the usability of data – primarily by creating HASHBYTES() or other checksum lookup surrogates. These layers are generally implemented by the application developer. They are also the most important layers in terms of the strength of data protection and therefore deserve consideration for integration at the the application layer. In almost all cases, if there is an option, cell value encryption at the application with key storage external to the database server will yield a more secure and scalable implementation (all other things being equal).
cell scoped encryption layers
With the exception of HASHBYTES(), SQL Server cryptographic functions are not available in SQL Azure. Certainly designs that rely upon equivalent client side methods are able to store encrypted values in a properly typed column in SQL Azure, but good to keep in mind that the server-side approach to protecting data in SQL Azure inadequate at this time.
There are an undetermined number of ways to successfully implement cell or column encryption. To my way of thinking this is good. If everyone does it a bit different, all the more difficult to get past for the malicious attacker: a little obscurity actually can improve a good layered defense but cannot be a replacement. There are a wide assortment of cell and column encryption schemes in the introductory Encryption Hierarchy Administration template post and even more in the hardened Encryption Hierarchy Administration 2.0 template.
In this post the goal is to create a visual that shows the possibilities. For that purpose I overlay the three explained images to arrive at that diagram that started this post. The difference is that now the many ways that the cryptographic elements can be used together to build various depths of encryption should jump right out at you. Completely demystified as promised. Toward the center of the diagram are solutions where all encryption layers might be in play. Toward the periphery of the diagram, the layers fall away leaving only clear text and unprotected storage. Performance and usability are examples of the hard to control centrifugal forces pushing application designs to the periphery, while security, authenticity and compliance are core drivers to concentrate applications toward the center. The size, shape and location of the encryption dimensions allow a two dimensional view of the architectural possibilities.
This is not a diagram of a dynamic and configurable run time environment mind you. Instead, this is a visual aid to explore adequately robust cryptographic solutions when designing and developing a data application with encryption requirements. The divisions and overlaps in the diagram indicate what is possible to create in all the combinations and permutations not what must be created in all scenarios. The application defines the requirements not the encryption technology. This is a tool to help define the environment needed to support the requirements.
Spend a moment contemplating the diagram now that you have some background of the diagrams structure and purpose. I expect that you will find your mind exploring some highly secure possibilities. Better yet, the next time you are working on a project with high data protection requirements take a few minutes to contemplate the diagram at that time in the context of your requirements. I am equally confident the diagram can help identify strong defense-in-depth strategies quickly and with ease when used for that purpose.
YABVE 