With the growing need to keep data secure, by all means, don't overlook securing your database backup files. With native SQL Server backups, the data in the backup file is stored in plain text and can be easily read just by using a text editor. Depending on the data types used in your tables, some data is much easier to view than other data.
Try this little exercise. Make a backup of the Northwind database, or any small database, and then open
up the backup file using any text editor. You will see that the data itself is a bit hard to decipher, but scroll through the file until you come to stored procedure comments and you will see just how vulnerable your backup files really are. If you make it a practice to store user IDs and/or passwords in your stored procedures, which is not a good idea in the first place, this data is now very accessible to anyone who gets their hands on a backup of your database. If you have other text data types with confidential note information you are also making that data very vulnerable.
Backup passwords
One option in SQL Server is to create backups with a password. This is just another option you can use when creating your backups, but the option is not available via Enterprise Manager or SQL Server Management Studio. Here is an example backup command using the password option:
backup database northwind to disk='c:\northwind.bak' with mediapassword = 'Backup2006'
This process requires the password to be used to restore the file, but the data is still accessible using a text editor. Also, the restore cannot be done by using the GUI, so it has to be done using the T-SQL restore command along with the password.
Encrypted stored procedures
One way to combat the viewing of your stored procedures is to use the "with encryption" option when creating your stored procedures. When you do this, the data in the backup file is also encrypted. To create a stored procedure with encryption, do the following:
<![CDATA[
create procedure dbo.testEncryption
with encryption
as
SELECT * FROM products
]]>
Encrypted data
Another option is to encrypt your data when you store it in your database tables. There is no native way to do this in SQL Server 2000, but here are a couple of tools that enable you to do it:
Native encryption capabilities do exist in SQL Server 2005. Take a look at Microsoft's article How To: Encrypt a Column of Data that explains the process.
After you encrypt the data in the database, when you create your backups, the data will remain encrypted.
Secure file systems
Another option for securing your backup files is the use of a secure directory on your server or network. You can limit permissions to this directory so that only a limited number of people can access your backup files. By using some of the above techniques along with a secure directory, you'll create another level of security. This will not eliminate the need for encrypting, but it does offer added security.
Back up directly to tape
Here is another option for backing up directly to tape to ensure backup files are not on your network. It alleviate the issue of inappropriate access to your backup files. I have one big issue with this approach: Most of what I have written about backups says to always write to a disk first for fast restores if necessary and then archive to tape for long-term storage. This approach eliminates having your backups fall into the wrong hands, but, unfortunately, it makes other processes more difficult.
Encrypted backups
If you really need to secure your backup files, the best approach is to encrypt the backup files as you create them. Unfortunately, nothing exists natively within SQL Server to do this, but check out these products that will allow you to create encrypted backups with varying levels of encryption and encryption techniques.
- Idera's SQLsafe
- Quest's SQL LiteSpeed
- Red-Gate's SQL Backup
There are several ways to secure your database backup files and back up file contents. Review your databases to find out which databases have information or code that needs to stay secure. Then implement one or several of the above techniques to make sure your data stays out of the reach of prying eyes.
| ABOUT THE AUTHOR: |
|
Greg Robidoux is the president and founder of Edgewood Solutions LLC, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and has delivered presentations at regional SQL Server users' groups and national SQL Server events. Robidoux, who also serves as the SearchSQLServer.com Backup and Recovery expert, welcomes your questions.
Copyright 2006 TechTarget
|
|
