A few months back I got a call from a client. The client was worried about a database backup that had gone missing and they wanted me to determine if that was a problem. Well, it was… But, let me start at the beginning.
You probably have a sophisticated backup and restore strategy in place. You regularly take backups of your valuable data and you might even ship it offsite.
The benefit of a backup is that it contains a copy of your data. However, the big problem with backups is that they contain a copy of your data.
Most of us spend a lot of energy on securing the data in the database, making sure the application and the different roles in the organization are all following the principle of least privilege, and no one can get unauthorized access to the precious data.
However, when it comes to backups, many start to get less concerned. Backups are placed on tape and shipped across the country. Backups end up on employees laptops for development purposes. You might even use an online backup solutions.
But if you are dealing with sensitive data, the backups contain all that same sensitive data and you therefore have to protect them the same way. Anyone that can get a hold of a backup file can just restore the database on one of their own servers, and then go about dissecting the sensitive data within it undisturbed.
The client I was talking about before had thought about all this ahead of time and had encrypted the data in the database. Encrypted table data naturally stays encrypted within any backup. Therefore they thought they had dealt with the problem of backup data exposure. But when an employee's laptop with one of the more recent backups was stolen they got worried nonetheless and decided to bring me in to reconfirm that they were indeed in the clear.
When I looked at the database, a big problem became immediately obvious. The data was encrypted using the ENCRYPTBYPASSPHRASE function and in an attempt to make the encryption as transparent to the application as possible, the passphrase was stored within the same database in several places. This step forced access through a controlling T-SQL module and therefore increased access control for the data in question. However, it did not really provide any encryption benefits and certainly no backup data exposure protection.
This example might be an extreme case of encryption key mismanagement, but the point I am trying to make is that you not only have to protect the data within your database, but you also have to protect the backups. A strategy designed to protect the data within the database is often not sufficient to protect the data within a backup. The easiest way to protect the data within a backup is to encrypt the entire backup. If you are on SQL 2014 already, that functionality is built in. In earlier versions, you have to find other ways to protect your backups (e.g. with an external tool to do the encryption).
The time to act is now, not after one of your backups has been stolen. Review your entire backup file life cycle and make sure you have no areas of unintentional backup data exposure.
Backup Data Exposure is one of the most commonly encountered database vulnerabilities. In this series of posts, I discuss 10 of them. Below are the ones that are published so far: