Data is a Secure Asset, so Secure It.

Good day! This will be the last post in this series before I make a pivot to speaking on Graph Databases in SQL Server for a few. We will certainly return to the topic of data management in the future though, so stay tuned. On a personal note, I was recently let go without reason or notice from my position so, as of today, I am contemplating where I want to go next in my career, but I can assure you that it will be fun and exciting! I pray for success for my former employers, and that with time, they cultivate wisdom and peace in their endeavors.

On to the matter at hand: Data security. Securing our data is a matter of utmost importance. Every day, it seems, we hear about a recent data breach, and over a few months, we find out the reasons for it. We always shake our heads and ask how any company could let their data be so vulnerable, and then we look at where we are and ignore the evidence of the same sort of vulnerabilities. I have been a staunch advocate for data security throughout my career and in this, perhaps I can share a bit of what I know on the subject, so that it can help you to become the same.

Firstly, let us talk about the reasons. There exists, of course, the obvious reasons that we want to protect our data, as it is an asset to our company. That being said we can also look at a larger picture of data security. We have an obligation, in most cases, to the laws of the land to keep our data safe and secure, whether it be through GDPR, FOIP, HIA, HIPA or some other data protection act that has been passed in our jurisdiction. We also need the peace of mind that we are doing everything we possibly can, to ensure our data is safe for our clients, and pass that along to them.

I’ve said it in a previous post, but it bears repeating: data is an asset, and should be given the same organizational gravitas as cash. It is not an afterthought, and not something that should be left lying around. It also should be treated with the same diligence for chain of custody as cash and, luckily, we live in an era where the tools are available to us to do so.

Data security, like network security, is a matter of layers. A wise man once told me that obfuscation is no security, so we will step aside from that as a means of securing our data. We cannot just hide our databases and hope they are not found by bad actors; we must fortify them with as many layers of security as possible. For those of you coming from a background in network security and the OSI model, the majority of this will be dealing with layers 5 through 7, as we are going to assume that network security is doing their job well in layers 1 through 4.

Securing your data in layers involved a fairly easy stepped list:

1.    Know your zone!

2.    Understand your security!

3.    Use the tools!

4.    Avoid defaults!

Seems simple, so let’s dive into the details on these.

Knowing your zone involves being aware of your organization’s architecture, network security, and data needs. Know that your data should always be housed exclusively in the most secure zones for network security, usually layer 5. Find out what your application and data using structures need access to. Vertically align three-layer applications and avoid replication where possible. If you are housing data in layer 6, and it is mission critical or personally identifiable information, you should address that, and find ways to move that data to a more secure location. If you need to have layer 7 applications using that data, find a way to proxy it in a secure method. With the release of SQL Server 2017 we saw some new and interesting ways to manage proxy data without replication, which is the ideal way to manage these vertically aligned applications.

Understand your security! Don’t just know the layers of network security, but find out about what you are doing with your instances and databases in your zone. Dig in and find out what solutions are being used to address data at rest and data in motion. Find out how many linked servers are in place and find out why they are. Do what you can to address the biggest vulnerability of any data structures: surface area. Reduce it as much as possible. Think about what would happen if a bad actor got into a specific database. What could they do from there? What data could they access? Could they redirect, copy, export, or otherwise take data that would affect the organization? The customers? The staff?

Within SQL Server there exist many tools to secure our data, both at rest and in motion. If we have a database or instance that isn’t using a database master key, we have immediately failed at securing our data. This fundamental step is not optional, and should be activated on all databases, including our master, before any data goes into it. As a side note, I prefer 21-character generated passwords from an enterprise password manager to accomplish this and I will say, I do not recommend anything less than 14 characters. Ensure that you have a valid and current certificate for your instance connections, and they are encrypted connections. If you get a warning in SSMS or VS or ADS about an insecure connection to a database, that should be addressed immediately. Ensure you have implemented transparent disk encryption (TDE) on your database file systems, so the data files remain encrypted at rest. Use “Always On Encrypted” if possible, to ensure that your data in motion is encrypted, and make sure they are keyed differently all the way down each layer. Encrypt your file backups of certificates and keys from SQL server with a separate password, which I have begun to refer to as a Back-Up Key (BUK) after the infamous Buck Woody.

Avoid defaults like the plague. Every single person in the world, who has ever touched SQL Server in any way, knows about the SA account. Disable it, use your enterprise domain controller, and only use windows auth wherever possible. Everyone knows the default schema of [dbo]. Do not use it, where possible. Replace the dbo schema with one of your own and secure the schema. Always make sure your logins are secured to the schema for access where needed, and remove them when not. Remove any BUILTIN\ domain accounts so that if your network security is breached, your databases remain secured. Utilize separate accounts for file systems, I prefer gMSA accounts, but the method is up to you. And always, always, encrypt the instance, the databases and the data movement.

As a final note, I do not like linked servers. I will make no qualms about that, and will be happy to argue the reasons until the cows come home. Do not use them. Manage data movement through SSIS, which allows an encrypted data movement, and avoids increasing the surface area of your enterprise data. I cannot say it with more emphasis. Do not use linked servers in an enterprise.