SQL backup protection

In the race to secure data, encryption is often overlooked. Philip Basham takes a look at the ramifications of data loss and how it can be prevented.

A tale of preventable loss

In the race to secure data, one basic measure – encryption – is often overlooked. Philip Basham takes a look at the ramifications of data loss and how it can be prevented.

There was once an organization that prided itself on the way in which it protected its electronic data. Every night, its array of databases was backed up automatically to a collection of tapes. The next morning, those tapes were shipped to a safe storage site that was fire-proof, flood-proof and just about bomb-proof.

Each month, the latest backups were tested to ensure that both individual files and complete datasets could be restored, not just to the main system but also to a mirror configuration that provided a contingency against catastrophic failure.

Everyone in the organization felt assured. Even the auditors could recommend only one small adjustment – that two safe storage sites should be used alternately, just to add a touch more protection.

Time passed and all was well, right up until one of those fire-proof, flood-proof and just-about-bomb-proof safe storage sites proved not to be burglar-proof. On a moonless night, some enterprising thief entered the premises and pilfered boxfuls of backup tapes and CDs and expensive equipment, making his get away, it was said, in a well-laden truck.

“Not to worry,” said the organization’s IT manager. “If we need to retrieve any backed-up data, we still have tapes at our second site.” The auditors looked suitably smug.

The real loss

Now, in this instance, the thief had a simplistic attitude towards his plunder. He considered the stolen goods as hardware – consumables to be sold to his drinking buddies and unwitting bargain-hunters at Sunday markets. What’s more, the IT manager, his boss, and the organization’s auditors looked at things in much the same way. They viewed the backups as just expendable tapes, easily substituted.

The organization had lost much more than a bunch of tapes, of course. It had lost a copy of all of its data. Had the thief been on a more targeted mission, or had the tapes fallen into more sinister hands, the organization could have been in significant trouble.

The security and confidentiality of the organization’s business information had been compromised. Somewhere out there was data that could assist competitors; jeopardize relationships with customers, suppliers and staff; imperil the organization’s very existence. And all this for want of a simple, inexpensive additional step in that apparently fail-safe backup process: data encryption.

Now, this is a fictitious story and one that may seem unlikely. Yet, a quick scan of the press in recent months reveals that this is a very real issue. Computerworld has reported loss of unencrypted backup tapes from the Bank of America that contained data on 1.2 million customers. Ameritrade and Time Warner were also reported to have experienced similar losses, with client and staff data going astray.

Simon Galbraith, marketing director of Microsoft technology provider Red Gate Software, gets to the heart of the matter.

“This is often seen as purely a technical issue,” he says, “something that rests solely in the domain of the IT guys. It’s not. Maintaining the security of data is part of good business practice and too many organizations are failing in their duty.”

Indeed, this is a matter of sound corporate governance, the aim of which is to safeguard stakeholder investment and protect the organization’s assets. In the modern business world, the gathering of information is commonly one of the biggest investments an organization can make. The value of information as an asset is often immeasurable.

Triggered by tighter regulatory frameworks and a plethora of guidance from governments and professional bodies, interest in corporate governance, and with it risk management, has increased considerably in the last few years.

The Turnbull Report, published in the U.K. in 1999, addressed the need for companies to maintain a system of internal control that would safeguard shareholder investment and protect assets. Ensuring sound corporate governance, it said, was the responsibility of the board of directors, as was a framework of policies and practices to manage risks that could impact upon achievement of the company’s objectives.

Since then, great rafts of legislation have been introduced throughout the world, most notably the Sarbanes Oxley Act in the U.S., which defines responsibility and accountability for corporate governance. Additionally, a plethora of guidance on data protection is now available from governments and professional bodies.

“Like a time bomb”

Great strides have been taken to reduce uncertainty by analyzing threats and implementing responses that eliminate or contain risks. Yet, many organizations continue to concentrate on risks with tangible qualities, such as physical security, financial welfare and personal safety. Loss of or damage to data is also considered in this context – an inconvenience mitigated by holding backup copies. Few managers are thinking beyond that to the damage that could be caused by their own data being used for nefarious means.

“This is something that needs to be taken seriously at the very highest level of an organization,” says Galbraith. “Once information has been lost, whether by theft or by being misplaced, its misuse has the potential to wound the organization mortally. Even if nothing happens immediately after such a loss, while the information remains unaccounted for, it’s like a time bomb waiting to detonate. Only when the information is out of date does the threat disappear, and that could be a very long time.”

The scale of the problem is hard to overstate. Although obtained by illegal database access rather than backup theft, data taken from ChoicePoint Inc., which keeps personal information on almost every American adult, illustrated just how enormous the repercussions can be. As the Wall Street Journal reported, the Los Angeles County Sheriff’s Department estimated that the downloaded data had been used for fraudulent credit card charges totalling millions of dollars.

Such loss of data fuels one of the world’s fastest growing crimes: identity theft. And it seems inevitable that victims of such crime who can prove the source data came from an organization with inadequate data protection procedures will seek substantial redress through the courts. Once more, it looks like the lawyers have hit pay dirt.

Taking responsibility

Data encryption involves special software that encodes backups in such a way that the data therein is meaningless unless it is translated by the same software when being restored. Anyone stealing an encrypted tape would therefore be unable to make any use of the data it carries. Encryption and decryption is now so sophisticated that the likelihood of anyone cracking the “code” is infinitesimally small.

As Galbraith says, although encryption is a technical process, it is not intended to solve a solely technical problem. IT departments are ultimately the custodians of an organization’s data, not its owner. Thus, the organization as a whole, through its senior management, should demand greater protection of its data as a crucial element of its corporate governance and its risk management regime.

If senior management abdicates this responsibility, it falls into the hands of IT managers – whose jobs could be on the line if data is lost – to make their organizations aware of options such as encryption and to recommend their implementation. Since Microsoft SQL Server databases are the most prevalent worldwide, encryption should be a top priority for DBAs and CIOs working in that environment. Fortunately, there are some easy answers according to Galbraith.

“The technology may be advanced,” he says, “but both the deployment and the day-to-day operation are extremely simple. Once in place, the whole thing is more-or-less transparent.”

It is also not a necessarily expensive solution. Red Gate’s SQL Backup, for example, is $295, less than an hour’s fee for many decent lawyers.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.

Tags: , , ,

  • 558 views

  • Rate
    [Total: 2    Average: 3.5/5]
  • AliAgha

    Question
    hi
    how do make a sql server db backup that is restored by UserName and Password.
    restoring may not be without entering database username and pass word