Microsoft SQL Server Backup Encryption Step by Step – Overview

Security has always been a major concern in SQL Server databases. Thus, backup plays a vital role here for SQL administrators and database analysts. However, in this rapidly evolving world, just a backup isn’t enough. Protecting this backup is also a significant step to take. SQL Server backup encryption is the solution for a 360° protected SQL backup. This step by step guide is going to help users by providing the requirements, types, benefits, and whatnot.

Carefully read the entire article without skipping any of the crucial aspects to get the best experience without a single error in encrypting the BAK for SQL Server.

Why Encrypt Backups of SQL Server Databases?

We all are aware that in today’s world, databases are no less than treasures for hackers and cyber attackers. Therefore, safeguarding these backup files is equally important as creating them. Moving ahead, we are mentioning the most critical five reasons that encourage users to add encryption to their SQL backups.

Data Integrity and Protection: Firstly, safety plays an evident role in the work profile of SQL administrators. Also, this safety feature works as an additional layer of protection against external threats like SQL injection, ransomware, etc cyber attacks.

Dealing with Insider Threats: Just like external dangers, sometimes, insider threats are also responsible for major hazards. Thus, such encryption tactics can not only safeguard the database from outsiders but also insider threats as well.

Accidental Data Exposure: There might be cases of accidental data exposure where users experience unwanted data loss. In such cases, unauthorized access can be restricted with the help of this SQL Server backup encryption step by step procedure.  

Regulatory Compliances: Several organizations have strict regulations on the safety of sensitive data of SQL Server. And in several scenarios, the backup or BAK file comes under this sensitive data. Thus, users have to encrypt or password-protect this backup.

Disaster Recovery: To expedite recovery efforts by preventing unauthorized access can be a significant win while dealing with disaster recovery scenarios with encrypted backup data from Microsoft SQL Server.

Application Level and Always Encrypted Methods

Users might already know this but still, it’s important to mention. Sensitive and crucial data should be encrypted within the databases directly. This data includes access tokens, passwords, credit card data, etc. The default methods available to get this level of encryption are:

Application Level Data Encryption: An external encryption library is used in this case to encrypt the data even before it is stored in the database. All the encryption as well as decryption occurs in the application level away from DB.

Always Encrypted: SQL Server client library and administrator come into action for this method to make the encryption process automated. Here the SQL Server automatically encrypts or decrypts the data based on requirements & manages the keys as well. 

Users can select any of these options based on their requirements. The only big difference between the two is that in the always encrypted method, SQL Server manages the rules itself whereas, users custom code manages the rules in the first method.

Now, before we move ahead, users must know that encryption plays a vital role. However, users can not encrypt the entire database as it will hamper usual tasks like searching, sorting, etc. Thus, encrypting the entire backup file becomes a good option to protect the data that will be used for restoration.

# Method 1 – Simple Backup Encryption with Archiving Tool from 7Zip

Using this the 7Zip open-source archiving tool is the most simple and and commonly used solution for quick results. It offers users a command line GUI to specify a password to encrypt the database backup. The command for the same is mentioned below:

sqlcmd -U sa -P sa_password -Q "BACKUP DATABASE SampleTest TO DISK='C:\backup\SampleTest.bak'"
"c:\Program Files\7-Zip\7z" a C:\backup\SampleTest.zip C:\backup\SampleTest.bak -pTestPassword

This implies an AES 256 strong encryption code on the database backup which is quite hard for the attackers to crack. Here, TestPassword is the password used to encrypt the backup file SampleTest. This encryption meets all global standards required to comply with the regulations.

Users just need to keep the password safe to decrypt & restore the DB later if required.

 "c:\Program Files\7-Zip\7z" e C:\backup\SampleTest.zip -oC:\backup -pTestPassword
sqlcmd -Q "RESTORE DATABASE SampleTest FROM DISK='C:\backup\SampleTest.bak' WITH REPLACE" 

# Method 2 – SQL Server Backup Encryption Step by Step with Certificate Method (Built-In)

Microsoft offers a built-in functionality for SQL Server encryption. This method works by creating a certificate and using that certificate in the backup command to encrypt it. Also, in order to decrypt the backup, users must present the same certificate in the SQL Server.

This entire SQL Server backup encryption operation can be elaborated in four simple steps as mentioned below.

Step 1 Creating A Certificate with T-SQL

The very first step is to create a certificate for which users need to start by creating a master key for the database. The command for the same is mentioned below:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '230zBL952387hxJ#K4nLe';

There might be chances that the key already exists in the database. In that case, simply open the key within the same user session. Otherwise, users need to start from scratch.

OPEN MASTER KEY DECRYPTION BY PASSWORD = '230zBL952387hxJ#K4nLe'

After this, users can enter teh below command to create the certificate.

CREATE CERTIFICATE MyCertificate
WITH SUBJECT = 'Backup Cert', EXPIRY_DATE = '20260101';

Here: 

• Create – Command to create the certificate.
• Subject – Certificate name to display in the SQL Server Management Studio (SSMS)
• Expiry Date – The date on which the certificate will expire automatically.

Step 2 Exporting The Certificate Backup

Now, that we have created the certificate, users must export this certificate. The reason here is that this certificate is crucial for the restoration process. The command for the same is:

USE master;
BACKUP CERTIFICATE MyServerCert TO FILE = 'C:\Backups\MyServerCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backups\MyServerCertKey.pvk',
    ENCRYPTION BY PASSWORD = '230zBL952387hxJ#K4nLe');

In the above command, check out the term is denotes what file or action for SQL Server Backup Encryption operation:

• .cer – This denotes the actual certificate file 
• .pvk – This file is the pirate key file. It’s required to work with the certificate encrypted with the password set earlier.

Now, let’s move ahead to create backups using the built-in encryption methods.

Step 3 Create Backup with Built-In Encryption

This built-in option can be easily accessed by the SSMS. However, users need to make sure that they select a new backup media set and also check the encrypt backup checkbox. Users can take a reference from the below image:

Also, if users want to avoid the SSMS, they can execute the exact same method with the T-SQL command. However, they have to add the WITH ENCRYPTION clause in the SQL backup command. The command for the same looks like this.

BACKUP DATABASE TEST_DB 
TO DISK = 'C:\temp\Test_DB.bak'
WITH ENCRYPTION 
    (ALGORITHM = AES_256, SERVER CERTIFICATE = MyServerCert);

Also, users must be aware that this command also encrypts the database with AES 256 encryption level and MyServerCert certificate.

Step 4 Restoring Encrypted SQL Server Backup

Now, once the database is encrypted, users must know how to restore it. For this, they must have the certificate available in the database we created in the earlier steps. Moreover, if users want to restore the database to a server where the certificate exists already, there is no need to mention anything additional for SQL Server Backup Encryption.

Let’s take an example we are restoring the database to a new server. Here, we have to create a master key with a password which could be anything. The command for the same is:

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ComplexPassword!';

Now, the next task is to restore the certificate. You must remember the .cer and .pvk files. These are required here along with the password that users certified during the certificate backup process.

USE master;
CREATE CERTIFICATE MyServerCert
FROM FILE = 'C:\Backups\MyServerCert.cer'
WITH PRIVATE KEY (
    FILE = 'C:\Backups\MyServerCertKey.pvk',
    DECRYPTION BY PASSWORD = 'AnotherComplexPassword!'
);

Now, users just need to restore the backup as usual. The system will recognize the certificate automatically.

# Method 3 – TDE (Transparent Data Encryption) Method – Page Level

This method is the Transparent Data Encryption which works on the page level. Here, the system writes the data on disk in the encrypted form only and decrypts it whenever users need to read it.

Unlike the previous certificate method, users can enable or disable this TDE feature within the database level. Thus, it does not work for the entire Server. Users need to separately enable TDE for each database as per the requirements.

The steps for the same are:

Step-1. Run the following command to create the master key :

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ComplexPassword!';

Step-2. Now, simply create the certificate to proceed with SQL Server backup encryption step by step:

CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My Certificate';

Step-3. Now, backup the certificate right after creating it.

USE master;
BACKUP CERTIFICATE MyServerCert 
TO FILE = 'C:\Cert\MyServerCert.cer' 
WITH PRIVATE KEY ( 
     FILE = 'C:\Cert\MyServerCertKey.pvk', 
     ENCRYPTION BY PASSWORD = '230zBL952387hxJ#K4nLe');

Step-4. Now, users need to create the encryption key for the database:

USE MyDatabase;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;

Step-5. Finally, users need to enable TDE decryption in SQL Server for this database.

ALTER DATABASE MyDatabase SET ENCRYPTION ON

Now, whenever a user creates a backup, it will be automatically backed up by the SQL Server without any hassles at all.

Protect Decryption Keys and Passwords

Users must understand that SQL Server backup encryption step by step isn’t that tough. However, users must take care of a few things we are going to mention:

• Users must store the backups securely even after encrypting them.
• Must use multiple locations for storing backups, keys, passwords, certificates, etc.
• Keeping the encryption keys safely in a place to extract when needed is crucial too.

Conclusion

Finally, users are aware of the SQL Server backup encryption step by step procedure with all the available solutions. After learning the reasons, benefits, and methods, users can easily encrypt the backup file of SQL Server database to get the perfect solution for safeguarding the SQL server-sensitive data files. Thus, users can restore the data files in any disaster or required situation.