Encrypt Database Backups in SQL Server Using MEDIAPASSWORD

http://www.mytechmantra.com/LearnSQLServer/Encrypt-Database-Backups-SQL-Server-Using-MEDIAPASSWORD-Option-P1.html

Introduction

One of the primary job responsibilities of a database administrator is to make sure all the production databases are backed up regularly. However, one must be well aware of the fact that the database backup file when it is not password protected or encrypted can be very easily restored on to a SQL Server Instance either of the same version or on to a higher version. Since database can be easily restored on to another instance there is a huge potential for data thief or misuse. In this article we will take a look at why it is important for a database administrator to take password protected or encrypt database backups in SQL Server. The steps mentioned in this article are applicable to SQL Server 2000 and higher versions. Let us go through a real world example to understand how to take database backups using Password Protected option there by encrypting database backup file.

"This article has a total of 2 pages including this page. Click the links below to open all 2 pages in a seperate window to learn How to Encrypt Database Backups in SQL Server. Page 1, Page 2"

Password Protected Database Backup Script for SQL Server

If you carefully go through the below script you would notice that we have included MEDIAPASSWORD option within the database backups command and we have also specified the password which will be used to Protect the database backup file. You could see that it is very simple to Password Protect a Database Backup file.
In the below sample script we have also used the COMPRESSION parameter; by using COMPRESSION parameter one can take a compressed backups for a database this will help you reduce database backup space and reduces time to back up a database. To know more about Compression feature which was introduced in SQL Server 2008 read the article titled “Database Backup Compression Feature in SQL Server 2008”

BACKUP DATABASE AdventureWorksDW 
TO DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 
WITH 
COMPRESSION, 
MEDIAPASSWORD='MyTechMantra.com'
GO

Now that we have Password Protected Database Backups File with us let us now go ahead and try restoring the database using the below database restore script.

Restore Database Script for SQL Server

RESTORE DATABASE AdventureWorksDW
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK'

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

The database restore script failed with the following “Access is denied due to a password failure” error message

---

RESTORE FILELISTONLY Command

Restore FILELISTONLY command retuns a result set containing list of all the database and log files contained within the database backup set. You could see below that the RESTORE FILELISTONLY command failed because Password to read the encrypted backup file was not specified.

RESTORE FILELISTONLY 
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

---

RESTORE HEADERONLY Command

Restore HEADERONLY command retuns a result set containing all database backup header information for all backup sets on a particular backup device. You could see below that the RESTORE HEADERONLY command failed because Password to read the encrypted backup file was not specified.

RESTORE HEADERONLY 
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

---

RESTORE VERIFYONLY Command

Restore VERIFYONLY command verifies the database backup file and checks to see that the backup set is complete and the entire database backup set is readable when required. This command when used will not restore the database however, it just verifies the structure of the data contained within the backup files. You could see below that the RESTORE VERIFYONLY command failed because Password to read the encrypted backup file was not specified.

RESTORE VERIFYONLY 
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
VERIFY DATABASE is terminating abnormally.

RESTORE LABELONLY Command

Restore LABELONLY command retuns a result set containing information about the backup media identified by the given backup device. You could see below that the RESTORE LABELONLY command failed because Password to read the encrypted backup file was not specified.

RESTORE LABELONLY 
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK'

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE LABELONLY is terminating abnormally.

The advantage of creating Password Protected Database Backups is that without knowing the password one won’t be able to restore the database. However, you must know that this is not a full proof mechanism. Once you specify the Password along with the database restore script the database will be restored without any issue.

Script to Restore Password Protected Database

RESTORE DATABASE RestoreAdventureWorksDW
FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 
WITH
MOVE 'AdventureWorksDW_Data' TO 'D:\MSSQL\DATA\RestoreAdventureWorksDW_Data.mdf',
MOVE 'AdventureWorksDW_Log' TO 'D:\MSSQL\LOG\RestoreAdventureWorksDW_Log.ldf',
MEDIAPASSWORD = 'MyTechMantra.com',
RECOVERY 
GO

Restore FILELISTONLY, HEADERONLY, VERIFYONLY & LABELONLY Command Using MEDIAPASSWORD Option

RESTORE FILELISTONLY FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK'  
WITH MEDIAPASSWORD = 'MyTechMantra.com'

RESTORE HEADERONLY FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK'  
WITH MEDIAPASSWORD = 'MyTechMantra.com'

RESTORE VERIFYONLY FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 
WITH MEDIAPASSWORD = 'MyTechMantra.com'

RESTORE LABELONLY FROM DISK = 'C:\DBBackups\AdventureWorksDW.BAK' 
WITH MEDIAPASSWORD = 'MyTechMantra.com'

Important Note Always keep your Passwords in secure location and incase if you forget the password you won’t be able to restore the database. Hence, keep the password file safely and only people who need to know the Password should have access to secure location. Restrict the number of DBA’s who need privileges to back up the database. Secure the database back up location by removing access from people who are not needed to access the database backup files. Take advantage of Transparent Data Encryption Feature of SQL Server 2008 to encrypt database backups. Try Transparent Data Encryption Feature first on a development environment and get confortable before using it on a Production System. Also refer Moving a TDE Protected Database to Another SQL Server article to know how to move a TDE Protected Database to Another SQL Server.

Conclusion

In this article you have seen how to encrypt a database back up file using Password to avoid potential data theft or data misuse. It is recommended that one should always use this option which is freely available in SQL Server to encrypt database backup files.