SQL backup encryption

What to do now that you’ve become an object of management’s attentions

Judging by mainstream news coverage over the past few weeks, the issue of data theft, particularly loss of backup tapes, has reached the tipping point. It might mean that your relatively anonymous existence within your company could come to a screeching halt.
Here are a few of the issues causing widespread concern:

  • Numerous leading business and news publications have published stories and opinion pieces in the last few weeks about “complacent” company practices and resulting data theft. A prevailing thread is that it is no longer just banks, telecoms and air travel companies that need to take data theft more seriously – everyone does.
  • On June 16, 2005, the Federal Trade Commission – the U.S. authority responsible for data security issues – joined its European counterparts by ruling it a crime to lose data through bad practices.
  • Companies such as CitiFinancial, Polo Ralph Lauren, Time Warner, MCI and University of California have announced loss of credit card, bank and other valuable personal data in the last six months.
  • A majority of the stories have concerned the loss of backup tapes. In many cases of so-called “loss,” data might not have been stolen by someone capable of using it, but because the tapes were not encrypted, the companies had to announce that data was potentially lost.

Protecting your domain

Given the media fervor, senior management, key customers and other company stakeholders are going to be forced to take an interest in data protection. While this is a wider IT issue, most valuable corporate data is kept in databases and SQL Server is the world’s most popular database. It is important as a SQL Server professional to know how you fit into the overall picture.

Your job as a SQL Server professional is to help management understand the issues and to make sure that you have good solid answers. If there are potential areas of weakness, it is important to identify where you need to improve and how you are going to do that.

Securing your database from malicious electronic access, although complex, is reasonably well understood. There are numerous articles and books that can be read and it is accepted that if you follow best practices then your data will be safe. Securing the database from malicious physical access probably isn’t your problem – but it is also eminently achievable.

Data loss is caused typically by stolen, misplaced or lost backup tapes. Backup tapes are a natural weakness in the data protection chain. They are usually prepared and stored with one aim in mind – to minimize downtime in the event of a disaster.

Security is the last thing on the planner’s mind when considering the overall backup and disaster recovery plan, so backup files are typically unprotected. SQL Server backup files, for example, are unencrypted and can be opened using Notepad.

In the next few days, before your senior management descends on your part of the organization, I recommend the following two steps:

  • Encrypt all backup files.
  • Make sure that your disaster recovery person has talked through your plans in detail with the IT security team.

Although a full audit would almost certainly reveal additional minor points of interest, for the vast majority of SQL Server users these two points will keep them in the clear. For more extensive information on backup, refer to other articles in this issue of Simple Talk.