By: Bernard Black | Updated: 2020-02-21 | Comments (2) | Related: > 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.
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:
About the author
Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-02-21
