But SQL Server Can Help

Did you know SQL Server has a lot of security features that can help make hardening your SQL Server environment easier?

It’s true!

I am a strong proponent of the concept that security in the database is nothing more than smoke and mirrors. The goal is to make it more difficult to get into your system, but in reality if somebody wants in, they’re getting in. This is not to say security is not important. It is very important. Especially now with more and more hacking attempts and data breaches occurring every single day. The purpose of this post is to cover a few of the options that you have to work towards a more in-depth coverage and point out that any one security feature in SQL Server is not enough.

The way I see it there are four levels of protection that are required to secure a SQL Server environment.

 

Level I: Connecting to SQL Server

With a default installation of SQL Server, the connections are not encrypted. This can lead to a false sense of security if you’ve apply data level encryption or TDE (later levels). When requests are sent to the SQL Server to decrypt this information and relay it back to you it is transmitted in plain text. Thankfully, after SQL 2005, at least user IDs and passwords are encrypted, but everything else streams as plaintext. (Check out my Hacking Exposé session on SSL if you get the chance!)

I highly recommend, at a minimum, turning on the “force encryption” setting in your server protocol settings. This gives a minimal level of protection using a self signed certificate generated by SQL Server and pushed to clients when they connect. This setting is just a simple drop-down in the properties of the server connection protocols found in the SQL Server configuration tool. This image shows you where that setting is:

 

[divider]

Of course there are options to create your own self-signed certificates, setup an in-house certificate authority or use third-party certificates. The use of third-party certificate or in-house certificate authority is recommended when you have sensitive data as they tend to be more secure than a self-signed certificate. There are a lot more steps involved with setting that up, and I will have a post out about that process coming soon! In the meantime, get some protection by turning “Force Encryption” on. Something is better than nothing right?

 

level II: Data at rest.

Data at rest is the data as it sits on the hard drive. This means the MDF, NDF, LDF and backup files for SQL Server.

SQL provides the option of transparent data encryption (TDE) which uses internal or external keys and certificates to encrypt the data while the rest, but presents the data to you, when connected as if it was still in plaintext. The goal of TDE was to allow quick and easy compliance to data at rest encryption requirements, while minimizing the impact on existing applications.

Unfortunately TDE is an enterprise only feature. There are other options if you’re using a different version of SQL Server. There are third party tools (outside of SQL Server) that can help encrypt your data at rest though, such as Windows EFS.

 

Level III: Data level encryption

It’s one thing to protect your connections and another to protect data at rest, but once you get connected to the database, the issue of protecting the actual data bubbles to the surface.

It is very important to identify any sensitive information you may have in your database and take appropriate steps to protect it.

This can be accomplished by hashing values if you don’t need to get them back, or by using encryption, either in the application or in SQL Server. Even better, do it in both, or look into new services such as ‘Always Encrypted’ coming in SQL 2016.

SQL Server has the ability to encrypt data with a variety of algorithms such as AES_256, triple_DES, AES_512, etc.

Protecting data in the database with encryption does have some overhead to it, just as protecting it with TDE does.

The thing is, if your server is consistently running CPU at a level high enough that the overhead of protecting the data comes to question, you have other issues to worry about.

 

level IV: Protecting the keys

OK, so let’s say you’ve got your SSL in place for your connections, TDE turned on to protect the data at rest, and data encrypted within the database.

That’s great! Don’t forget about protecting the keys and certificates that are used to protect those different levels.

As soon as you implement any of these types of protections, you need to make sure you backup the components and store them separately from the data itself. This includes storing them separately from the backups of the data. Things can get very hairy,very quickly, if you do not have backups of these components. You may not be able to get the data back at all.

And no, there is no back door (I have seriously been asked that). The goal of encryption is to protect the data. Lose the keys, lose the data. Sorry.

There are some options that SQL Server to take advantage of to make key and certificate management easier though.

Extensible key management (EKM) is a way to move the source of your keys outside of the SQL Server environment. There are even cloud versions available now, such as the Azure Key Vault. In general, these systems are managed by another team, such as the security team, with an organization. This doesn’t mean you can’t manage it, it just helps when separation of duties is required within your organization.  Unfortunately EKM is an Enterprise feature only, but it shouldn’t stop you from backing up and storing your keys and certificates appropriately.

 

It’s a start

Security and compliance are 2 major and evolving topics with SQL Server. There is no simple set-it-and-forget-it way to deal with them.

Making sure each one of these levels is covered within your SQL Server environment can greatly help reduce the risks encountered not if, but when, a data breach occurs.

Sure, some of these features are Enterprise level only.

If you are collecting data of a sensitive nature and are serious about protecting your data while running Standard Edition or lower, you need to seriously look at upgrading.

I hope this short post helps to spur you into looking more at protecting your data.

After all, isn’t just your data, it’s your customer’s and user’s data.