This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.
There are obviously many things to check when it comes to SQL Server security. We’re talking about a lot of those elements of security this month. Today I want to talk a little more about backups as it relates to their role in securing your SQL Servers.
When it comes to your backups and security in SQL Server –>
1. Encrypt your SQL Server backups
Whether it’s using Transparent Data Encryption or Backup encryption (or a combination), there’s no excuse in 2024 to have your open data out there and available. If anyone gets past all of your layers of security around your databases and your backups, make sure they get access to a backup they can’t restore without your encryption key or certificate (or a lot of work and time by well-determined and well-resourced hacker – but make it hard! Let someone else be easy to defeat).
When you use encryption, make sure you also back up your certificates, which protect your backup encryption or TDE. When you need to restore a database – it’s a bad time to find out that your only certificate copy was stored on a SQL Server that crashed.
Make sure the keys/passwords and certificate files are all stored in a secure location, with only those who you want to be able to restore a database having access to those keys and files.
Mind the expiration dates on your certificates for TDE/Backups, also.
2. Air Gap your SQL Server backups
Ransomware is a prevalent threat around the globe right now. After having a few companies become clients after they hit ransomware and seeing other clients go through it – we’ve seen that one common approach with ransomware attacks is to find and destroy backup files. Either through explicitly deleting anything in a backup directory, with a common backup file extension or just through encrypting the backups along with everything else.
It is no longer enough to have your SQL Server backups in a different location! They must be stored OFF NETWORK and off location ultimately. We’re still big fans of using native SQL backups and then backing up the backups. Those second backups are excellent to store in a journaled, protected, separate location. Be it a backup device which is write-protected to prevent deletes or encryptions or a cloud based backup tool like Azure Backup. The point here is you have to protect your backups from threats from outside actors and even inside actors. No sysadmin should be able to delete a backup file without a waiting period (preventing from a rogue and angry sysadmin destroying your environment as I blogged about years ago) and no rogue virus should be able to delete or overwrite the backups.
Get them off the network and domain to protect them from those threats. And get them offsite to protect from data loss in equipment failures/fires/natural disasters.
3. Test Your SQL Server backups!
In one of my first blog posts 15 years ago, I asked if folks focused too much on their backups. A silly way of making folks focus on their restores, not their backups. It’s the same here – a backup is only good if it can be restored. And while you should be running regular checkdbs, have the proper page verify options set, always verify your backups, and do a restore verify only on your backups – you still are not sure you can restore unless you test it.
And when you have a security incident—if it’s an environment-killing incident caused by ransomware or if it takes down large swaths of your environment—that’s the absolute worst time to determine that you’ve been taking bad backups all this time.
Test your backups as often as you can. Set up processes to restore and check your backups regularly. If you are a fan of the DBATools Powershell scripts, they already have a command for you with Test-DBALastBackup. You just need space. It’s relatively inexpensive in general—and WAY less expensive than downtime and data loss.
4. Control who can access the backups
Take care when you find your storage points for the backups. Take care when you determine where you store your encryption certificates and scripts. Whoever can access those files and assets directly (or indirectly through asking IT to add them to the domain group that has the rights!) has access to your data. Security isn’t just about keeping bad people from hurting your SQL Servers – it’s about keeping unauthorized users from accessing/stealing your data, too.
5. Have a solid regular backup schedule and monitor them!
Backups really are a layer of security. If you are not taking proper regular backups in a consistent way and monitoring the health of your backups and testing them regularly through restore tests – you really can’t say you care about security.
At Straight Path, we’re big fans of a schedule of weekly full backup, daily differential backups, and transaction log backups taken at least every 15 minutes. We like to try and keep “local/quick access” to at least 2 weeks of these backup files – and then we hope that the backup of the backups in the air-gapped location are copies of the backups as close to the time they were taken and stored at the proper retention time your company needs.
Having a week (at least) or two weeks of log files allows you to restore to a specific point in time – which is excellent for corruption recovery and the ability to even potentially do some forensics of data changes over time through analyzing the log backups in the unfortunate event you need to do get into that deep of work after a security issue.
