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:
Strengthen your SQL Server Administration Skills – Enroll to our Online Course!
Check our online course on Udemy titled “Essential SQL Server Administration Tips”
(special limited-time discount included in link).
Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!
--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:
Watch a Video from our YouTube Channel: How to Secure Your SQL Server Instances
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database for Beginners
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
Other SQL Server Security-Related Articles
- How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
- Why You Need to Secure Your SQL Server Instances
- [DBNETLIB] [ConnectionOpen (SECDoClientHandshake()).] SSL Security Error – How to Resolve
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- SQL Server Row Level Security by Example
- Frequent Password Expiration: Time to Revise it?
- Policy-Based Management in SQL Server
- The “Public” Database Role in SQL Server
- Encrypting SQL Server Databases
- Transparent Data Encryption (TDE) in SQL Server
- Encrypting a SQL Server Database Backup
Check our latest software releases!
Easily generate snippets with Snippets Generator!
Secure your databases using DBA Security Advisor!
Convert static T-SQL to dynamic and vice versa with Dynamic SQL Generator.
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018) and a Udemy Instructor. He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.