Encrypting a SQL Server database backup is necessary in many cases, especially when the database has sensitive data.
SQL Server provides an easy way to encrypt database backups.
Let’s further examine this functionality with a step-by-step example.
In this example, we are going to backup a SQL Server 2014 database, encrypt it, and then restore it on a SQL Server 2016 instance. The sample database’s name is “TestDB1” (not quite an original name for a database 🙂
In SQL Server Management Studio, if we right-click on the database and go to “Tasks”, “Back Up…”, we are presented with the well-known backup dialog:
--Create Database Master Key and Encrypt it with a Strong Password USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyComplexMasterKeyPassword'; GO --Create Backup Certificate USE master; GO CREATE CERTIFICATE TestDB1BackupEncryptCert WITH SUBJECT = 'TestDB1 Backup Encryption Certificate'; GO --IMPORTANT NOTE: It is critical that you backup the master DB key and the database backup certificate to a secure location --Backup Master DB Key BACKUP MASTER KEY TO FILE = 'c:\tmp\MasterKey.key' ENCRYPTION BY PASSWORD = 'S3curePass!'; GO --Export the Backup Certificate to a File BACKUP CERTIFICATE TestDB1BackupEncryptCert TO FILE = 'c:\tmp\TestDB1Cert.cert' WITH PRIVATE KEY ( FILE = 'c:\tmp\TestDB1CertKey', ENCRYPTION BY PASSWORD = 'S3curePassCert!')
Note that the above file keys are created by the service account that runs SQL Server Database Engine and it is the only user that has full access. In order to get access to these files, if you are a local administrator on the machine running SQL Server, you can do so by editing the permissions (via Advanced dialog).
Now, let’s try again to take an encrypted backup of the database:
--Recreate master DB key on destination SQL Server instance CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'S3curePass!'; GO --Restore the Certificate Based on the Previously Exported Key/Cert files CREATE CERTIFICATE TestDB1BackupEncryptCert FROM FILE = 'c:\tmpBackups\keys\TestDB1Cert.cert' WITH PRIVATE KEY (FILE = 'c:\tmpBackups\keys\TestDB1CertKey', DECRYPTION BY PASSWORD = 'S3curePassCert!'); GO --Restore Encrypted Database 'TestDB1' RESTORE DATABASE [TestDB1] FROM DISK = 'c:\tmpBackups\TestDB1.bak' WITH MOVE 'TestDB1' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDB1_Data.mdf', MOVE 'TestDB1_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\TestDB1_Log.ldf'; GO
As you can see, now the encrypted database has been successfully restored on the destination SQL Server instance:
Reference: SQLNetHub (https://www.sqlnethub.com)