Protecting Data in SQL Server Blog Post

Introduction

Keeping your and your client’s data secure is more important than ever. Security threats today cannot be ignored, with data breaches being a nearly monthly affair. While the focus of this post is on SQL Server 2017 or later, these concepts apply to protecting data in other data storage systems as well. The three segments of security that we'll investigate are for data at rest, data in transit, and "physical" security.

Defense at Rest

When the topic of keeping data secure at rest arises, the stock answer will always be to encrypt it. That’s not always the full story, though. Yes, you will absolutely want to have your data encrypted at rest, but there are other aspects to ensure your data is protected. With SQL Server, there are several options for further data protection. Using Transparent Data Encryption (TDE) and Always Encrypted (AE) features within current versions of SQL Server are both great ways of protecting your data.

Transparent Data Encryption (TDE)

TDE does a great job at protecting data at rest because the entire database and database backups are protected. Although it does do a good job of securing your data, the data can still be read by anyone with access to the database. That means if a user has access to read a table with secure data, they can read it. There are a few ways to avoid this scenario though, mainly through data masking (ie. “**** 4032”) or encrypting the data itself using a key that the database engine doesn’t know about.

Always Encrypted (AE)

AE takes a different approach. It manages the encryption of specific data from within the application. By doing so, the encryption keys that are used are never exposed to the database engine or database users on the server. There are some caveats to using AE that you must evaluate if you are going to use it. For example, Full-Text Indexes and Range Scan queries are both not supported. That being said, if you are trying to protect credit card data or social security numbers, you shouldn’t be using either of those features on that data anyways.

If the feature support using AE isn’t as robust as needed within the organization, using encryption that is managed outside of the database and coupled with TDE makes a lot of sense. To do this, you want to manage the encrypted data externally, sending only the encrypted data to the database for storage.

Protection in Transit

Once the data is protected while it’s at rest, you can now focus attention on how it is being transmitted. Of course, the first measure that should be put in place should be Secure Sockets Layer (SSL) certificates. Using SSL protects the data on the wire, preventing snooping and packet sniffing. Once SSL has been enabled in SQL Server using a trusted key, it should be configured to force compliance. By forcing compliance via the settings, non-secure connections will fail and be denied access to the server.

Taking transit security a bit further, the data should be encrypted within the client application until the moment it is needed. Then and only then, the data should be accessible, with the encryption being removed or re-encrypted after access. We have seen cases where an intruder will gain access to a memory dump of a client application and get access to secure data that is stored unencrypted in memory.

“Physical” Security

In the past, physical security has been important, ensuring that on-premises servers are secure from physical intruders. With most companies moving to the cloud, this is no longer a concern as the web services platform or choice handles physical security. That being said, the way the application environment is structured in a cloud is still very important. Keeping data isolated and maintaining control over access to that data is of the utmost importance.

Examples of using a layout like this could be as simple as using a private network for database servers and isolating the access to those servers. With this model, the only machines that have access to the data are those which have been approved. Taking that a step further, you can use a proxy type model, allowing data to be stored in a private network with an API layer placed in front of the database. This only allows API application access to the data, further reducing you attack vector. In some cases this can be cumbersome, especially in the age of Object-Relational Mapping (ORM) layers. Proxy models are not a one-size-fits-all solution and may not be reasonable, depending on your circumstances.

It is better to set up from the start so that no issues arise later from not having it. In the event that a breach does take place, having effective audits and being able to prove the security of your systems will become a very real concern. Having a robust monitoring / alerting system is also key. Your organization needs to know when a breach has occurred in order to take action immediately. Ideally, you want to identify and correct issues ahead of time but worst case, you need to be notified immediately.

Conclusion

Protecting data comes down to several key areas: encryption, isolation, and access control. Within those key areas, your security plan must protect your data in all phases of the application lifecycle. Oftentimes, these areas are not a focus, leading to shoehorning them in later. Keeping security as a primary focus from the onset will help to avoid potential pitfalls later.