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:

BACKUP DATABASE Northwind
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.