The topic for this month’s T-SQL Tuesday #167 hosted by Matthew McGiffen (Blog / Twitter) is about encryption and data protection in SQL Server.

Security is a Layered Approach

Security and data protection is best thought of as a layered approach. There is no “silver bullet” or single thing that you can implement to keep your data protected. You must examine and understand your risk and take steps consistent with that to protect your data.

Take for example home defense. Locking the doors of your home, especially while you sleep, is universally acknowledged as a smart security move to keep you and your home safe from people who shouldn’t be there.

But it isn’t enough. Maybe you also have automatic lights, a security system, cameras, a dog, weapons, or some kind of Home Alone defense (which apparently would be legal in your jurisdiction).

From a SQL Server point of view, this means there isn’t a SQL Server feature that you can use solely to keep your data safe. Many factors should be considered including physical security, OS security, network security, application layer security, instance and database level security.

TDE, Always Encrypted, and Dynamic data masking all have their pros and cons. None is going to provide you with all the security you require.

The Human Factor is The Greatest Threat

We think because encryption is so strong and reliable that it is a panacea; however, that’s not the part most attacker’s exploit.

https://xkcd.com/538/

Phishing is a crude and scalable method for exploiting users to give you info that you want without them knowing they have been compromised. On a technical level, it is a much dumber than learning about the intricacies of encryption, hashing, certificates, etc.

Security Through Obscurity Is Never a Good Idea

This kind of misdirection can buy some time at best but not really stop or detect an intrusion. It’s not a great idea nor a good idea, but sometimes may be an ok idea that can be implemented alongside others in a layered approach. For example, changing the port your SQL instance is listening on isn’t going to stymie an attacker – but it might slow them down by adding extra steps.

Passwords and Identity Management are the Low Hanging Fruit

People are terrible at passwords. We can’t remember sufficiently long / complex strings. We like to re-use and we don’t like to change periodically unless coerced to do so by IT policy.

Why You Should Use a Password Manager – The Pros and Cons of Password Management Systems

The solution for this can be a password / identity management application. For home use this can be something like 1Password or KeePass. For enterprises maybe it is CyberArk or Delinea.

Conclusion

Protecting your data inside SQL Server requires a layered approach. There is no silver bullet or approach that will satisfy. Realize that people, especially insiders, are the top threat and address the low hanging fruit and you’ll be ahead of the game.

Then you can go ahead and implement those SQL Server security features and feel better about it – kind of like locking your doors, turning on the camera, and not feeding the dog his supper (just kidding – feed your pup).

Thanks for reading!

---