On the face of it, this is a very obvious question with a very obvious answer. We want to prevent data from falling into the wrong hands. In practice, it gets a little more complicated.

Exactly what types of attacks do you wish to be protected against? It’s good if we make sure our data is encrypted where it is stored on the disk, but that doesn’t help us if an attacker gains direct access to write queries against the database. We might encrypt data held in columns, but does that still protect us if the unencrypted data is being passed back across the network to our application and an attacker is intercepting our network traffic?

Another question is why are you considering encryption in the first place? Often projects consider encryption because relevant regulation, or client requirements, demand it. All too often in these cases, encryption is considered as a binary option, is data encrypted or not. Often what happens is that the bare minimum is done to tick the checkbox and move on. Data might be encrypted, but the protection offered is of value in only limited scenarios.

When we think about what scenarios we wish to be protected against it makes sense to consider where data exists and might therefore be vulnerable. By that I’m not talking about where specific data is held, but rather the types of locations:

• In memory on the database server
• In transit across the network
• In your application
• Files stored outside of the database, perhaps on a file share

In subsequent posts we’ll look at how the tools available in SQL Server can protect the first three. I won’t however look at it once it reaches your application; that’s for your application developers to consider. I also don’t talk about files stored outside of the database – but you should think about how you want to protect such items if you have them.

Encryption is only one line of defence and should go hand in hand with a well-defined and implemented approach to security. Your first line of defence is always going to be access controls, making sure that only the right users and applications can access your data and servers in the first place. Some might say that if you have access controls in place, then why do you need encryption at all. The answer is that there is always the potential for access controls to be breached. The best approaches to security are always multi-layered, and on top of access control and encryption, it is good to have auditing in place so you can see who is accessing your systems and what they are doing, as well as having alerting in place for suspicious activity.

Even though we have a good toolset for encryption available to us in SQL Server, it doesn’t come totally for free. Encrypting and decrypting data requires CPU, and so it does have some performance overhead; we’ll discuss that when talking about each tool, and in many cases, we’ll try to look at how you can quantify what level of overhead you might be looking at.

We also will generally have an increase in management overhead, for instance, where we need to manage encryption keys. When implementing encryption it is important to consider how you will manage it on an ongoing basis. One of the worst scenarios you can encounter with encryption is where an individual sets up encryption without telling anyone else where the keys are backed up and then that individual leaves the organization, and if we have a server failure, we may never be able to recover our encrypted data.

The last impact is on functionality; we’ll talk about this a lot when we look at column encryption: where we only store encrypted values in the database that limits how you can interact with them, for instance, searching against such columns or performing calculations. Due to this, an important part of your encryption strategy will be deciding what data to encrypt and how you’re going to work with it once it is encrypted.

When choosing what to encrypt we are usually most focused on personally identifiable information (PII) as well as items deemed particularly sensitive. In considering your approach I’d recommend that something is always better than nothing. If you have a security breach and your list of users is accessed, then that is bad, but it is much better if you are able to say that passwords, credit card information, social security numbers, and other information were not accessed due to the extra encryption on these items.

This post is part of a comprehensive series on SQL Server Encryption. Subscribe to my blog for updates as new posts are published or you can buy my book through the link in the sidebar to get it all in one go.

And if you’re embarking on an encryption project (or anything else to do with SQL Server) and want some help, I’m available for consulting – please get in touch or check out my services page to find out what I can do for you.