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
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
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
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.
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
Figure 1: Three Views
Figure 2: Customer Table Data
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