SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Securing SQL Backups

By Brian Kelley,

By now most folks have heard about the loss of backup tapes belonging to Bank of America. These tapes contained financial records for, among other customers, the US government. As the story goes, the tapes were stolen as they were being transported. In other words, they were completely out of Bank of America's hands. This raises the issue of backup security, especially with regards to SQL Server. Since SQL server backups contain data, and often sensitive data for a given organization, the wise DBA takes the time to understand how these backups are being handled and secured. That's what I'll discuss in this article. 

Step One: Physical Security

As the saying goes, if an attacker has physical access to a server, it's not your server any more. Physical security is a critical part of any security plan. However, it's often one of the most neglected aspects of the overall security posture. Does a third-party cleaning agency have access to your data center? How about building maintenance? How well are those folks reviewed and watched? These types of unsettling questions have to be asked in order to protect an organization's assets. So let's start at the beginning, where the data originates. Let's start at SQL Server.

Physically Secure SQL Server

If an attacker can get physical access to SQL Server, how an organization handles its backups is probably irrelevant. There are all kinds of ways of getting the data out of SQL Server, even if you've taken out BUILTIN\Administrators. Getting physical access likely means getting administrative access. Think it takes a computer expert to get into your SQL Server? It doesn't. You can teach a kid to mark the hard drives on a server in such a way that you know exactly what slots they go in. This is important if you have a physical RAID array and most severs do nowadays. Yank the power and then yank the drives. Slap those drives in equivalent hardware and the attacker has all the time in the world to use utilities to crack the administrative password. There are also a lot of cases where the attacker can slap in a specially built Linux disk and read the files straight off the drives. Therefore, the first step is always to physically secure the server.  

Physically Secure the Backup Server

If you copy your SQL Server backups to another server, this server needs to be secured as well. If you have another server that backs up your SQL Server across the network, secure this server. However you define "backup server," if you aren't spooling backups to tape on the SQL Server itself, that second server needs to be secured.

Physically Secure the Tapes

Remember me asking about the cleaning folks? If one of them got handed US$500 (or the equivalent in your currency) to try and pick up any tapes they could grab once they went in the data center, what percentage do you think would do it? Even if we go with the assumption that most folks are basically honest and would turn down such an offer, it only takes one. If your tapes aren't secure, they can be grabbed. And they can be grabbed with someone who has no computer experience. There's no cloak-and-dagger stuff trying to infiltrate the cleaning company. Just a simple bribe is all it takes. Secure the tapes.

Step Two: Define Procedures That Work

If an organization hasn't defined procedures for backups and for the handling of tapes, it cannot be sure that these things are being cared for properly. Defining procedures is an absolute must. However, the procedures have to be ones that work for the organization. I can scour the Internet and various standards and write up a top-notch procedure that'll pass the scrutiny of any auditor who is simply reviewing documentation. However, if the people who have to follow the procedure are unable to do so, the procedure is useless. For instance, if I say tapes have to be off-site by 7 AM but the backups on a particular critical server don't finish up until 8 AM, there is no possible way to honor the procedure.

Define Physical Server Access

This goes along with step one and in actuality the procedures and the security go hand-in-hand. It is unrealistic to say no physical access will ever occur for a given server. I've heard executives throw around the term, "Lights Out Data Center" but this isn't 100% doable. There are plenty of remote access solutions but if a physical drive goes bad, someone needs to physically change it. Therefore, plan for physical access to the server and define who has access, when they have it, and what the controls are to verify the procedure is being followed.

Define Backup Requirements

At a minimum an organization should determine what the acceptable maximum data loss is for a given set of data. In other words, how much "time" can be lost and still be acceptable? Is it one day, one hour, five minutes, or two seconds? This allows the database and system administrators to plan accordingly to try and ensure that should a failure occur, no more than what is defined as acceptable is lost. Of course, this is more of a business decision than a technical one, though the technical side plays a factor due to cost. But defining backup requirements shouldn't stop there. How backups are handled should also be defined. Will backups be handled by SQL Server? Will a third-party product do so? How will such backups be executed? Is SQL Server the answer or does the organization rely on Task Scheduler? Does a more robust job management system need to be used instead? These types of questions should be answered by the procedures covering backups.

Define Tape Handling by Internal Staff

Once the backups are rolled off to tape, those tapes become valuable. They must be handled appropriately based on the data they contain. Therefore, an organization should have procedures in place on how to handle backup tapes. After all, if tapes are just left lying around a data center, it makes it that much easier for that cleaning person to grab a few and make some money.

Know Your Staff

Any key personnel who might be handling data should be checked out. This includes anyone who has access to those sensitive servers and tapes. This isn't a 100% fool-proof check. After all, you could always have the individual who checks out fine but is there to compromise your organization. Also, you can have once loyal employees who become malicious due to work or personal factors. However, you do the best you can by hiring people who appear trustworthy based on background checks, validated references, etc. If nothing else, good procedures in this regard should give your organization a break on insurance premiums.

Check Your Procedures

As I said, if the procedures don't work, they are useless. People won't follow them. And the organization is essentially in the same spot as if didn't have procedures defined in the first place. Some would say the organization is actually worse off because it has lulled itself into a false sense of security by having procedures. Everyone assumes that since procedures are in place the problem is covered. Only the problem still exists because the procedures don't work.

This raises the question, "How do you know if the procedures work?" You test them. You slip someone into the cleaning staff (coordinated with that company, of course) to test physical security. You spot check the backups to ensure they're being done properly. You watch video or otherwise observe the handling of tapes. You pull employee records to ensure proper background checks were done by HR. This is extra work, but it's the only way to validate the procedures work. If they don't work, figure out why. Is it a training issue? Maybe it's a cultural issue. If these can be overcome, great! Do so. But if they can't or if the procedures flat out don't meet reality (tapes offsite when the backup isn't done yet), the procedures should be modified accordingly.

Step 3: Thoroughly Investigate Your Off-Site Storage Company

If you rely on another organization for the off-site storage of your backup tapes, you need to know a lot about them. All your internal procedures are for naught if your storage company is lax in the way they handle things. Make sure these are folks you can trust. Make sure they have solid procedures in place. Make sure they are covered for liability. 

Do the Customer Research

A company can say a lot of things to convince you to do business with them. However, the "proof is in the pudding." Get references for current and former customers. Investigate why a former customer is just that: former. Don't assume a current customer is 100% happy, either. In the not so distant past I flew up with a contingent from my organization to investigate a particular software package. This was a customer visit set up by the software vendor. We went on the customer visit sort of as a "confirmation tour." We left that customer visit with serious questions about the product. Ultimately, we didn't purchase the software. Do the same sort of interviews with any potential vendor, especially one who will store tapes holding your sensitive data.

Understand Their Transport Mechanisms

This actually is true whether you're dealing with a vendor or you ship tapes between locations in your own organization. However, I'll focus on the vendor case. The vendor has to get tapes from your site to the storage facility. How does it do so? Anyone can drive up in a van. How will vendor staff identify themselves? What credentials should they present for you to trust them with your tapes? Also, how does the vendor ensure the tapes get from your facility to their storage location safely and securely? Keep in mind that Bank of America's tapes were stolen at the airport. The initial reports say baggage handlers were suspected. Personal and government financial data was potentially compromised because the tapes were placed in unsecure hands. Make sure your off-site storage company takes proper care of your tapes from the time they leave your facility until they finally arrive at the vendor.

Physically View Their Processes

Understand how the vendor handles tapes once they reach the storage facility. During a training session at BlackHat USA 2004, our instructor (Chris Conacher) told us about one of his visits to a particular vendor. He had gone to retrieve tapes for his organization and was wading through the process to verify who he was in order to get the tapes back. However, as he stood there waiting, tapes for other customers were sitting there in boxes he could get to. There wasn't anything stopping him from reaching down and slipping a few tapes into his pockets. Therefore, pay a site visit and view how the vendor operates. Make sure you are comfortable with their internal procedures.

Step 4: Expect Failure and Plan for It

No procedures will succeed 100% of the time. Humans make mistakes. Equipment fails. Something unexpected happens. For whatever reason, a failure occurs. Are you equipped to handle it? Not everything can be planned for. However, think about common ways you might see a failure. Anticipate what is likely. Try and put controls in place to handle these failures.

Expect Human Failure

If you have a person manually kicking off backups, this is ripe for failure. What happens if the regular person is sick? Is the backup going to get done? Probably not. Automate as much as you can. Humans are still important because they provide oversight. However, what makes sense to automate should be. For instance, regular backups should be scheduled and executed by an agent. They shouldn't depend on a human to "push the button."

Expect Equipment Failure

Hardware fails. Software has bugs. Therefore, humans are needed to monitor these things. Also, support agreements, warranty contracts, and the like should be up-to-date. Make sure they are sufficient for the business need. For instance, if a particular server can only be down a maximum of one hour but your support agreement calls for a vendor's four hour response, what are you going to do if the motherboard goes bad? Are you going to maintain spare motherboards in stock? Expect equipment to fail and plan accordingly.

Expect Compromise

Security professionals plan for when the attack occurs. Expect for your backups to be compromised. Assume someone is going to grab your tapes. All it takes is one person in the right place at the right time. Now that they have those tapes, what can they do with them? If the data is encrypted on them, it makes an attacker's job that much more difficult. If the data was encrypted using a secure algorithm, now the attacker must get both the tape and the encryption key. While not impossible, it is that much harder. Bank of America issued a statement that it would be virtually impossible for whoever stole the data to be able to access the data. The company would not say if the data was actually encrypted. But there must be something protecting the data for BoA to make such a statement. 

Which reminds me: do not trust password protection to keep your SQL Server backup safe. I'm talking about something like:

TO DISK = 'C:\Backups\Northwind.bak'
WITH PASSWORD = 'DataIsStillVisible'

Yes, password protection will stop someone from successfully restoring a backup. It will not, however, fully protect your data. Using SQL Server's password protection does not encrypt the data. Figures 1 and 2 show some of the contents of a password protected backup. This is taken from the Northwind database. The first figure reveals code behind three views. The second figure reveals information in the customer table. Protect your data using encryption. If you do, you have some security in place even if your tapes are compromised. 

Figure 1: Three Views


Figure 2: Customer Table Data

Concluding Thoughts

This article was intended to be a quick overview on how to look at backup security. When putting together the overall security plan for an organization or just for a particular SQL Server, backup security should always be a consideration. The key points can be summed up as: restrict physical access, have procedures that work, and know/trust your off-site storage company. Even when you plan for those things, plan also for failure. It's not a matter of if, but a matter of when. Take into account the most likely possibilities and be prepared to handle them. This is the best way to protect your organization and its backups.

 © 2005 by K. Brian Kelley. http://www.truthsolutions.com/
 Author of Start to Finish Guide to SQL Server Performance Monitoring.


Total article views: 8785 | Views in the last 30 days: 3
Related Articles

Stored Procedure Security

Stored Procedure Security


Server Organization

How to get a handle on multiple servers


Linked Server Security

Linked Server Security Question


SQL Server 2012 security: Changes for the newest version

Database infrastructure security is extremely crucial for any organization, which is why Microsoft h...


SQL server 2005 security

SQL server 2005 security

sql server 7