Encrypting table in SQL Server [closed]

Posted on

Question :

I’m a .NET guy that is trying to start using table encryption on some of my Users tables but I could do with some questions being answered first. I don’t want just anyone to be able to see these tables.

When should you start encrypting your tables in SQL server, should I encrypt all tables?

How to avoid going to far?

The steps required to encrypt a table?

Answer :

Encryption is a huge topic. There are lots of caveats, and most of those are about business requirements, not about technology.

Start by taking a step back and look at the big picture. Pop the why? stack until the business requirements are clear. Then start formulating requirements about what data needs to be protected and what kind of attacks are expected. Only after then start exploring what encryption would be suitable and what wouldn’t.

Who or what would attack the database? Rogue admin? Oops, data-at-rest encryption doesn’t help much. Network sniffer? Hope you are using TLS between application and SQL Server. SQL injection? Something else?

What would happen in case of data breach? Vandalism? Lawsuits? Loss of reputation? Heist of Bitcoins? Authoritarian government hangs dissidents?

Some domains, like health care and banking, often have strict laws about data protection. US, EU and rest of the world have their own quirks.

As practical issues, key management is among the most complex one. Do you have a key management process? Without one, managing certificates and encryption keys is going to be painful. Restoring old backups and server migrations are suddenly extra complex, as backup itself isn’t enough.

If working on Enterprise Edition or Developer Edition, TDE is relatively simple to set up.

Column encryption is suitable for hiding a subset of data. An example about securing national id numbers.

Always Encrypted moves the crypto to application layer. Good solution for some cases, abysmal for the rest.

Dynamic data masking isn’t actually encryption, but suitable for many cases in which confidential data must be partly exposed.

Leave a Reply

Your email address will not be published. Required fields are marked *