By: Greg Robidoux | Updated: 2024-05-30 | Comments | Related: > Backup
Problem
The SQL Server backup process allows you to perform full, differential, transaction log and file level backups. The problem with all of these backups methods is that the data that is created in the backup is stored in clear text and can easily be comprised if someone cares to take the time to hack through these backup files. In addition, SQL Server makes it very easy for you to move a database from one server to another server by restoring a complete backup or by having the MDF and LDF files and using the attach functionality. So what are the best practices to combat this?
Solution
There are several things that can and should be done, but let's take a look at this issue first.
Plain Text in Backup Files
As mentioned above when backups are created the files that are generated and stored in clear text. There are a lot of control characters that are hard to decipher, but with some time and patience it is fairly easy to hack your way through some of the data. Here are a couple of views of data captured from a backup of the Northwind database. Both of these clips were taken by opening up the backup files with a text editor.
This first clip shows some data from a full backup file.
You can make out the data pretty easily as follows:
- Save-a-lot Markets 187 Suffolk Ln Ricardo Adocicados Av Copacabana
This next clip is from a transaction log backup.
As with the first file we can pretty easily make out the following:
- C:\Program Files\Microsoft SQL Northwind_log C:\Program Files
So as you can see, if someone does not have SQL Server and they get their hands on your backup files they can pretty easily pull some data out of the files by just using a text editor. This could be program passwords, social security numbers, accounts balances, etc. It doesn't take much to comprise your data.
Easy SQL Server Database Restores
Another simple problem is gaining access to your backup files and restoring them on another server. Once someone has your full backups and transaction log backups there is nothing that is stopping them from restoring the database to another server and having full access to the entire database contents. As long as this person has access to a SQL Server where they can restore the database they then have complete control over this newly restored copied. Microsoft makes this pretty easy for anyone to download a fully functional copy of SQL Server on a trial basis. So even if someone does not have a copy of SQL Server it is pretty easy to get a free working copy.
Attaching SQL Server Databases
Another practice is attaching and detaching databases for backup processes to copy the MDF and LDF files. If someone is able to get a copy of the MDF and LDF files and they have access to a fully functional version of SQL Server, someone can take these files and attach on a new server and can have full access to your complete database contents.
Floating SQL Server Backup Copies
SQL Server DBAs go through considerable effort to ensure that no one can access the database. There are several levels of security that are built into SQL Server and for the most part some level of this is implemented in all installations. These varying levels are:
- Server Access (Windows or standard accounts)
- Server Roles (sysadmin, etc...)
- Database Access
- Database Roles (built-in and user defined)
- Object level permissions
- Column and data level partitioning
- and more
Just about every SQL Server performs some type of backup process every day. In most cases a full backup is probably performed which creates a complete copy of the entire database. In general these backup files are close to the same size as the database, which makes it hard to transport very large databases to other servers outside of your company, but there are several SQL Server backup compression tools that allow you compress backups by up to 90% which makes the transport even that much easier. Also, backup compression is now built into SQL Server. Usually companies with very large databases are already using compressed backups and the vendors that make these tools offer free trial editions, so again it is pretty easy to get your hands on a copy of these tools in order to restore the compressed version. In addition, it is now very easy and fast to upload backup files to the cloud.
To help offset the risk of the backup files being compromised, DBAs or System Admins protect folders where the data and log files are stored and folders that store the backups, but often these same complete databases are restored on Test and Development servers where the same level of security is relaxed. Once these backups are copied across the network to different servers or these databases are restored to secondary servers this is where the security starts to breakdown. If the database is restored by the DBA and only the DBA has access to the backup files, this takes care of these backup files falling into the wrong hands. But if on this new server someone else has the ability to create a new backup from this restored version your data can be easily comprised once again.
Things to do to Safeguard SQL Server Backups
There are several ways to ensure that your data both in production databases and in backups on test and/or development servers is protected.
- Setup proper server and database permission levels
- Protect backup files and folders
- Don't copy backup files to unprotected folders
- Give limited people permissions to perform backups
- Protect data and log folders and files
- Monitor when backups occur
- If restoring to test or development servers maintain the same level of security or mask data you want to protect
- Encrypt your backup files
- Set passwords on your backup files
- Encrypt your data at the column level for sensitive data
Next Steps
- As you can see there are several ways that databases can be comprised with or without access to the production database servers. Take the time to secure your databases both within SQL Server and outside of SQL Server.
- Begin to implement some of the steps listed above.
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-05-30
