Episodes / Episode 7 of 9

SQL Server Security: CIS Benchmarks, TDE and TLS

Episode 7: SQL Server Security: CIS Benchmarks, TDE and TLS
Episode 7SQL Server Security: CIS Benchmarks, TDE and TLS6:25

It’s war: the black hats are always trying to stay ahead. Where a SQL Server DBA starts with security, from the CIS Benchmark to encryption, patching and role-based access.

Watch on YouTubeFull course playlistSubscribe

In this episode

  • Start with the CIS Benchmark that matches your SQL Server version
  • Talk to the developers before you change anything, and use the same release path
  • Red teams, blue teams, and SQL Server Audit
  • Encryption at rest with TDE (back up the certificate) and in transit with TLS and TDS 8.0 strict encryption
  • Patching, role-based access control, and the sa account

Chapters

  1. It’s War!
  2. Where to start?
  3. Talk to the developers
  4. Red teaming
  5. Blue teaming
  6. Data Encryption At Rest
  7. Data Encryption In Transit
  8. Patching
  9. RBAC (role-based access control)

Transcript

Read the full transcript

It’s War!

“It’s War!” The black hats are always trying to stay ahead of the white hats, and the white hats are always trying to stay ahead of the black hats. This is a topic that there are no guarantees for. You can do everything right, and still lose. At the end of the day, you can only try your best.

Where to start? Make sure you’re doing what the current industry standard is. It might be difficult to figure this out because if someone wrote it down and made it public, that would be like handing your war plans over to your adversary, oh wait, someone wrote them down. An organization called the Center for Internet Security keeps something called CIS Benchmarks for SQL Servers. You have to do every last thing on their benchmarks because the adversaries have the list too, and are looking for lazy DBAs. Go to the CIS website, fill out the little form, download the CIS Benchmark that matches your SQL Server version, and start figuring out how to do them. I’m not going to go over what’s in the CIS. Go to CIS, and get it from there. The CIS is always being updated, and you should be getting your updated copies directly from CIS.

After you have the CIS, it may be tempting to frantically start closing all these security vulnerabilities as soon as you find them, but you need to talk to the developers to make sure they haven’t made a feature that depends on them. If your developers are not in house, they should be able to provide requirement documentation that you can review. If the developers are in house, use the same process that’s used for releasing new code. Start by applying the change to the development environment, let the developers test the app to make sure it didn’t break anything, then move along the same release path that’s used for anything else. You might find that some of the CIS recommendations will prevent your applications from functioning. In those situations you will have to do one of the following:

Get your boss to accept the risk the vulnerability creates. Get the developers to change the applications so that the risky practice is no longer required to keep the application running. Mitigate the risk using another creative solution. More strict permissions or networking architecture.

The red team is the people your company pays to attack you. Pen testers. They do what the black hats do, except they write you a report afterwards instead of a ransom note. If your company has a red team, make friends with them. When they find something on your SQL Server, don’t get defensive. Say thank you, put it in a ticket, and figure out how to get it fixed.

The blue team is the defenders. Security operations. The people watching the logs. Your job is to make sure your SQL Servers give them something worth watching. Failed logins, people being added to sysadmin, permission changes. SQL Server Audit can capture all of that, and it’s in every edition, so there’s no excuse.

Data Encryption At Rest

Data Encryption At Rest. Somebody walks out of the data center with a disk, or a backup file ends up on a file share it shouldn’t be on. Encryption at rest is what makes that a bad day instead of a headline.

Use Transparent Data Encryption, TDE. It encrypts the data files, the log files, and the backups, and the app doesn’t have to change at all. It used to be Enterprise only. Now it’s in Standard edition too.

Back up the certificate. Right now. Microsoft’s own documentation says: after you enable TDE, immediately back up the certificate and its private key. If you lose that certificate, you can’t open the database. You will have perfectly encrypted, perfectly useless backups. Store the certificate backup somewhere that is not the same server, and make sure two people know where it is.

Data Encryption In Transit

Data Encryption In Transit. Every query and every result goes across the network between the app and SQL Server. If it isn’t encrypted, anybody in the middle can read it.

Encrypt the connections with Transport Layer Security, TLS, using a real certificate from your company’s certificate authority. Not a self-signed one.

If the connection strings say TrustServerCertificate=True, that means the app will trust any server that answers, including the wrong one. Fix the certificate, then take that setting out.

SQL Server 2022 and later support TDS 8.0 and strict encryption. TDS is Tabular Data Stream, the protocol apps use to talk to SQL Server. With Encrypt=Strict, everything is always encrypted. TLS 1.3 support depends on the operating system, so that’s one more reason to keep your Windows Servers current, like episode 8 says.

Patching

Patch the same way you release code. Development first, let it sit, then test, then production. Same release path.

Automate it.

RBAC

Role-based access control.

Give permissions to roles, and put Active Directory groups, AD groups, in the roles. Never grant anything to a person. When Brad leaves, you take him out of one group, instead of hunting through 47 databases for every permission he ever had.

Nobody gets sysadmin who doesn’t need it. App service accounts never need it.

CIS has a lot to say about the sa account and the built-in logins. Go do what it says.

Review who has what four times a year. Permissions only ever grow unless somebody prunes them, and that somebody is you.