Author: Som Dutt Tripathi

Backing up database without password contains several vulnerability of disclosing the confidential information. The reason that any database can be restored at any location makes this problem more serious. You can check it out by creating a Backup of your database and restoring it in some other machine. The information stored in your backup media also get disclosed by - Restore filelistonly or Restore headeronly options.


Backing up a database without supplying password could lead to several anomalies that no company would like to suffer.

Some common problems concerned

(1) Disclosing of company information, such as customers, policies and future plans.

(2) Disclosing of contact information or accounts information especially in case of Bank or Insurance companies.

(3) Disclosing of structure of the storage pattern of the company.

(4) Utilization of future plans by rival companies.

These are common problems, but there are numerous specific problems that could be faced. Let's have an estimate of some reasons why backup set must be password protected

(1) Backups in Tapes is not kept in a secure manner.
This is also recommended to store all the backup off-site. In such cases where your company stores Tapes off-site, there could be some physical security problems of there storage. There are cases when disgruntled employees have sold these tapes to rival companies to gain better perk and confidence in rival companies. As you all know, each company wants to know the structure and pattern followed by other company. A employee of company may be unfaithful and he can damage some information. But damaging some data is not a big issue than selling or disclosing information.

(2) Backups can be sent as e-mail or uploaded.
This problem is not hidden to anyone. Your company firewall may be strong, but Thumbnail drives can anytime be applied in USB port and data can be transferred. Thumbnail drives are nowadays in fashion also. Anyone can have a 1GB to 10GB of such thumbnail drives. The important thing to notice about thumbnail drives is that they do not require installation. Just plugging into USB port and computer shows a Removable media and then copy and paste of files. This is so easy to do that a newbie can do this.

How to provide security in Backups ?

This only requires to add an option of password to make your backup set password protected. The advantages of providing password in backup are:

(1) Restore Filelistonly, Restore Headeronly commands does not disclose the backup information.

(2) If you want to restore from media, error is generated by SQL Server.

The following commands can be used to provide a password. I assume that you have a backup media and backup device name 'backup01'.

Statement#1
Backup database Northwind to backup01 with password='secretpass'

Now, let us have a test on this and see what SQL Server says when password is not provided.

Restore database Northwind from backup01

Server: Msg 3279, Level 16, State 2, Line 5
Access is denied due to a password failure
Server: Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

This was an error with severity level 16. That means it's working. Now, let us have a look on restore related commands that produce some information.

Restore Filelistonly From backup01

Server: Msg 3279, Level 16, State 2, Line 5
Access is denied due to a password failure
Server: Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

Again the same error. In this way, we may conclude that this approach is easier to create this. Some other options can also be provided like media name, media description and media password.

Media Name, Media Description and Media Password

There are some differences in directly applying password while backup and using media name. When you are using following command while backup.

Statement#2
Backup database Northwind to backup01 with medianame='Set-1' , mediapassword='passwd'

This would create password for backup01 and once it was done, you get the same error Msg 3279 while you execute the following command on the same media.

Backup database Northwind to backup01

So, when your provide password for the media, you can not backup any information into media unless you provide password. After the Statement#2 you need to provide password for other backups.

Backup database MSDB to backup01 with medianame='Set-1' , mediapassword='passwd'

The statement executes successfully. This is the actual difference in using 'with password=' and 'with medianame' case. After executing Statement#1, you can use the same for other backups without mentioning password or mentioning different passwords.

Conclusion

This is all about providing protection for passwords. There are many other things to research in the same scenario and I would like to share my ideas when such studies are complete. Overall, my aim was to open your eyes that backups are not only to secure your data, but due to carelessness this may lead to leakage of information. In my opinion, disclosure of important information is more serious matter than loss of information. So, awareness is important and there is a little effort to apply in securing your backups.

About the Author:
Article written by - Som Dutt Tripathi (c) 2006. "