Encrypting SQL Server Backups for Legacy Systems


By:   |   Updated: 2020-02-21   |   Comments (1)   |   Related: More > Backup


Problem

One issue with SQL Server backups is that if someone got a copy of a backup they could easily restore to another environment then have access to the data.  This is where encrypting your SQL Server backups is key, but not all versions and editions of SQL Server support encrypted backups.  This feature was introduced in SQL Server 2014, but it is not supported for SQL Server Express or SQL Server Web editions.  So, if you have an older version of SQL Server still in your environment or use Express or Web editions, take a look at this approach to protect your backups.

Solution

As mentioned, not every version and edition of SQL Server supports encrypted backups, so let's look at another approach.

In most cases, your database backups tend to sit on a disk and wait writing to tape or some other long-term storage device. Maybe you delete them when they are offloaded to tape or moved to another storage device, but maybe not. Is there 3 hours or more before this happens? Why not write them to local disk, or a UNC (in my example below), immediately encrypt them, and then put them to tape whenever you want?

We are going to go outside of SQL Server and use a zip tool that allows us to zip and encrypt the backup.

To do this, install the omnipresent 7-zip tool on the server and add a step to your SQL Server Agent backup job and voila, backups are encrypted with an AES-256 symmetric key (the experts say this is safe, certainly better than no encryption). We can make this process where the password is NOT visible in any of the code and maybe the auditors will now be happy.

This setup is pretty easy to implement. The one problem I had was that big backups seem to take an inordinate amount of encrypting time. Your results may vary. Maybe someone reading this will have tips to improve 7-zip performance.

STEP 1 - Install 7-zip to Encrypt SQL Server Backups

Go to this site, download the utility and install it on the server where the backups are created.

https://www.7-zip.org/download.html

STEP 2 - Create Stored Procedure to Assist with Encryption Process

What this does is create a way for the encrypt part of the job (listed later) to have a password in it without anyone being able to see it. An encrypted stored procedure is not visible to anyone, including an sa. Keep in mind that you'll need to save the password in this procedure somewhere else in case you need to restore the backup.

CREATE PROCEDURE is_bland_name WITH ENCRYPTION 
AS
BEGIN
   CREATE TABLE ##User_3443_ARM ( is_pw char(50) )
   
   INSERT INTO ##User_3443_ARM VALUES('Your_password_blah12345'); 
END

Note that the table above is a global temporary file (##). I think a local temporary file could be used as well. Try it.

STEP 3 - PowerShell Script to Zip and Encrypt SQL Server Backups

Here is the guts of the job which is a PowerShell script to zip and encrypt your SQL Server backups. You will need to modify this code to fit your situation.

Line #1 and #2 are there to handle backing up to a UNC path. If you are not using a UNC and just backing up to a local path, omit these and just use a local location in Get-ChildItem line. 

This process will look for ".bak" files in the path you reference, zip and encrypt the backup file and then delete the ".bak" file.

######################START RUN

#### LINE 1) 
Set-Location -Path Alias:

#### LINE 2) now this. (should be PS Alias: prompt) This is for UNC usage. Don't need this if local backup
New-PSDrive -Name UNCPath -PSProvider FileSystem -Root \\your_server\Backup_Folder\Instance_name\Full_Backups

#### LINE 3) ..runs encrypted stored procedure...does select for password...then deletes the password table.
$result5 = Invoke-Sqlcmd -ServerInstance "ServerName" -Database "msdb" -Query "exec dbo.is_bland_name;SELECT is_pw FROM ##User_3443_ARM; drop table ##User_3443_ARM ;"

#### LINE 4)  Save the password.
$result6 = $result5.is_pw

#### LINE 5) This variable should point to where your 7-zip exe is.
$process = "c:\Program Files\7-Zip\7z.exe"

#### LINE 6) this creates collection of .bak files and pipes to Foreach clause
Get-ChildItem  -Path UNCPath: -Recurse -Filter *.bak |
Foreach-Object {
###  $content = Get-Content $_.FullName   ###(testing)
#### write-host $content

#### LINE 7) ...this will run 7z with a password which tells to encrypt
try {
   $destinationFile = $_.DirectoryName + "\" + $_.Basename + ".7z" 
   $sourceFile = $_.FullName

   #write-host $destinationFile
   #write-host $sourceFile

   Start-Process $process -ArgumentList "a -t7z $destinationFile $sourceFile -p$result6"-NoNewWindow -Wait
}
catch {
    Write-Warning "[CATCH] Backup Failed on *****"
    Write-Warning $destinationFile
    Write-Warning $sourceFile
    Write-Warning $result6
    Write-Warning "[CATCH] Backup Failed on *****"
    BREAK
}

#### LINE 8)  ...this line deletes the regular backup
$la_file = $_.FullName
Get-ChildItem -Path Filesystem::$la_file | del

} ###end big loop

#### LINE 9) .....this deletes older .7z files older than 36 hours. You could replace this with a maintenance step.
Get-ChildItem -Path UNCPath: -Recurse *.7z |? {$_.LastWriteTime -lt (get-date).addhours(-36)} | del

STEP 4 - Create a SQL Agent Job to Automate Backup Encryption

Add the above PowerShell script in a SQL Server Agent Job step directly after your backup step. This way right after the backups occur, this process can encrypt the backup and remove the normal backup file.

sql agent job step code
Next Steps

The above code only zips and encrypts ".bak" files, modify the process to also include ".trn" transaction log backup files.

Here is some related reading:



Last Updated: 2020-02-21


get scripts

next tip button



About the author
MSSQLTips author Bernard Black Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.

View all my tips
Related Resources





Comments For This Article




Friday, February 21, 2020 - 12:52:32 PM - Eitan Blumin Back To Top (84698)

This is an interesting and useful approach.

But if you've already stepped into the realm of powershell then a much better solution would be available to you.

First of all, you could delegate the entire process to it, including the execution of the backup itself.

Also, for the purpose of securing your password, you could make use of something like Azure key vault. It has support for powershell.



download





Recommended Reading

Backup to multiple files for faster and smaller SQL Server files

Simple script to backup all SQL Server databases

Script to retrieve SQL Server database backup history and no backups

How to monitor backup and restore progress in SQL Server

SQL Server Backup Paths and File Management








get free sql tips
agree to terms


Learn more about SQL Server tools