26 May

Lessons learnt by implementing Backup Encryption

We’ve already discussed Backup Encryption feature in detail, incase you’ve not covered those please find the link:



There are a few things you must know before you implement backup encryption in your environment, here I’ll try to list them:

1. Original Certificate which was used as an encryptor should be available at the destination server\Instance to restore an encrypted backup. Encrypted backup keeps track of the thumbprint of the certificate, if the thumbprint doesn’t match it will never allow SQL Engine to read the backup, you won’t even be able to even get the file list details from the backup file (Restore FilelistOnly option). Refer the screenshot below which shows the error:



2. We should also refrain our self from renewing or changing the certificate in any way, renewal or changing the certificate results in changing the thumbprint of the certificate therefore making the certificate useless for the backup restore.

3. The login ID being used to restore the encrypted backup must have View Definition permissions on the encryptor (Certificate\Asymmetric Key) which was used to encrypt the backup.

4. You must restore the Database Masker Key on the destination server\SQL Instance since the certificate we used was encrypted using DMK only.

5. You need not to Restore the Service Master Key, if your DMK was encrypted using a Password- that is not a must.

6. You must take a backup of all your keys\certificates and passwords and keep it on a safe location other than the source server. The best practice would be to keep a copy of these things at your DR site or Offsite.

7. The Database master key (DMK) must be explicitly opened if it was encrypted using a password.

8. Restoring a Master Key can be a resource intensive task, if the current master key is being used to encrypt many things and you are restoring a new Master Key it has to decrypt and re-encrypt everything- we must try to do it in Off-Peak time.


Happy Learning

Sarabpreet Singh

19 May

Backup Encryption | Part-3

In Part-1 we discussed the feature of backup encryption and part-2 walks us through the scripts to prepare for the pre-requisites and take an encrypted backup, In part-2 we took the backup of Repro_FGDN Database on MBTEST server.

We took the encrypted backup using Certificate as an encryptor and used AES_256 encryption algorithm.

In this part we’ll try and restore the same encrypted backup on another server – FGDNPROD.


–Before you restore the encrypted backup on the destination server, you must create the Encryptor; in part-2 we created a certificate which was used as an encryptor. The certificate was encrypted with a DMK so we must first restore the DMK before creating the certificate.
FROM FILE = ‘C:\backup\SQL_Prod_Repro_F.key’
ENCRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’;


–You must open the master key before using the DMK, once the Database Master Key is open – it stays open for the complete session.
DECRYPTION BY PASSWORD = ‘alseImRtjSnuEiheYf8b22e’


–Create the certificate
FROM FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , DECRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’

–Closing the DMK
close master key


Restoring service master key on destination server is not required.

–Restore encrypted Database backup
Restore database repro_FGDN
from disk =’C:\backup\Repro_FGDN_22.bak’


Finally after creation of the encryptor you will be able to restore the encrypted backup, refer the screenshot where I was able to restore the encrypted backup on another server named FGDNPROD.

Restore completed without service master key2


Sarabpreet Singh

13 May

Backup Encryption | Part-2

In the first part we discussed the new feature of Backup Encryption which is introduced in SQL Server 2014, the post describes the benefits, restrictions, permissions required etc.

Now let’s see this in action, the below mentioned scripts will generate Service Master Key, Database Master key and a Certificate which will act as an encryptor and post that we’ll use the encryptor to take a backup with Encryption for a DB named Repro_FGDN.


–Regenerating Service Master Key

–script to Backup Service Master key
TO FILE = ‘C:\backup\SQL2014_service_master_key.key’

–Creating Database Master Key also known as DMK

–script to backup DMK
TO FILE = ‘C:\backup\SQL_Prod_Repro_F.key’

–Creating Certificate (Encryptor) which is encrypted by DMK

–Script to Backup certificate as soon as it gets created.
TO FILE = ‘C:\backup\FGDN_DMK_certificate.cer’
                FILE = ‘C:\backup\FGDN_DMK_certificate_private_key.key’
                , ENCRYPTION BY PASSWORD = ‘abcalseImRtjSnuEiheYf8b’

–Script to backup Database using Encryption
TO DISK = N’C:\backup\Repro_FGDN_22.bak’
        , ENCRYPTION
        ALGORITHM = AES_256

This shows the backup completion from the Server named MBTEST.



This is the screenshot of the Backup folder which consists backup of all Certificates, Keys along with Encrypted backup.



Learning\Points to consider:

1. Backup the Certificate\Keys as soon as you create them, you never know when you might need to recover the database and without these encryptors you won’t be able to recover the database.

2. Certificate to be used as an Encryptor for backup encryption should be encrypted by DMK only.


Sarabpreet Singh

6 May

Backup Encryption | Part-1

We’ve been asking for this feature from a long time and it was one of the main reasons why third party tools were being used, they were minting money like anything. Thankfully Microsoft introduced this feature in SQL Server 2014, now you can encrypt the data while creating a backup at runtime and creates an encrypted backup file.

The best part is all type of storage destinations be it On-Premise or Windows Azure Storage all of them are fully supported. In addition to that you can also use this feature if you are using SQL Server Managed Backup to Windows Azure. I know what you are thinking “you are still using old version and you’ve to rely on these third party tools” The good news is, if you want you can use Backup encryption in your old SQL versions also (like SQL Server 2005, 2008 and SQL 2008 R2) but only if you plan to take backups on Azure by using SQL Server Backup to Windows Azure Tool. Read more about this feature here…



1. This helps in securing the data; SQL Server encrypts the data at the time of creating a backup – so no need to change anything on the application front.

2. You can also use Backup Encryption for TDE enabled databases.

3. Since this is also supported with SQL Server Managed backup to Windows Azure this gives an added security for off-site backups.

4. You’ve got multiple options to select the encryption algorithm to meet your requirements.

5. You can integrate encryption keys with Extended Key Management (EKM) providers.



To make use of Backup Encryption all you need to do is provide an encryptor (that could be a Certificate or Asymmetric Key) and choose an Encryption algorithm. SQL Server provides four encryption algorithm to choose from these are: AES 128, AES 192, AES 256 and Triple DES.



During restore you don’t have to specify any encryption parameters. However the certificate or the asymmetric key used to encrypt the backup file must be available on the instance you are restoring to. The user account performing the restore must have View Definition permissions on the certificate or key. If the DB you are restoring is already TDE configured, the TDE certificate should also be available on the instance you are restoring to.



You must create a Database Master Key (DMK) before creating a Certificate or Asymmetric Key. A DMK is symmetric key that is used to protect the private keys of Certificates and Asymmetric keys that are present in the database.

Once a DMK is created you can create a certificate or asymmetric key to use for backup encryption.



1. While encrypting backups using asymmetric key always remember the keys residing in the EKM provider are supported.

2. Two SQL Editions (SQL Express and SQL Server Web) do not support encryption during backup. Having said that restoring from an encrypted backup to these editions is fully supported.

3. Also note that earlier versions of SQL Server cannot read encrypted backups.

4. Appending to an existing backup set option is also not supported if you are encrypting the backup.

It is very important to back up the certificate or asymmetric key, and preferably to a different location than the backup file it was used to encrypt. Without the certificate or asymmetric key, you cannot restore the backup, rendering the backup file unusable.



To encrypt or restore from an encrypted backup View Definition permission on the certificate or asymmetric key that is used to encrypt the database backup. However Access to the TDE certificate is not required to backup or restore a TDE protected Database.

Sarabpreet Singh

18 Apr

SQL Tip-12 | Using SQL Server Backup to Windows Azure Tool – Part2

In last part we discussed the installation, configuration and usage of the SQL Server Backup to windows azure tool.

In this part we’ll discuss some internals of this tool and how to restore the backups which were moved to Azure storage.

As we discussed in the last part this tool creates a login ID with the name of SQLBackup2Azure , let’s diagnose it further. The tool creates this login with a low privilege account which is used to run this service. This tool also creates a group called “TempGroup” and the login SQLBackup2Azure gets the membership of this group. The name of the group and login are hardcoded and can’t be changed. However the password of the login gets generated automatically and randomly which is not known to anyone including Microsoft.

When you start adding different rules in this tool, this group get read\write access on the specified location – that is how this tool is able to take backup and restore.

The biggest disadvantage of this tool is it doesn’t provide a mechanism to track the uploaded files and any kind of error logging information, you don’t even have any control over the schedule of the uploading of backups since there is a service it keeps on checking the files.


Restore a Database from the Backup file which was uploaded using this tool

This tool creates a Stub file with some metadata to use during restore. You can use this tool just like a regular backup file when you want to restore a database. SQL Server automatically reads the metadata information in the file and restore the database by getting the required file from the Azure Account. Internet connectivity is a must since the stub file contains only the metadata information about the backup file rest everything is there in the Azure Storage account.


Stub File

This tool uploads the specified backups from the file path mentioned in the rule but once the backup file gets uploaded it creates a Stub file for the same and leaves it in the local storage folder with metadata information to be used during restore.


What if Stub file is inaccessible or Deleted by mistake?

A copy of the same Stub file gets uploaded along with the backup on your Azure Storage Account. If for any reason the stub file is inaccessible you can download the copy of the stub file and place it in the same location which is configured for this tool.


Some known issues:

1. In the event of a loss of network connectivity, SQL Server will surface the error as not being able to write to the device. You will need to clean up the files relating to this backup operation (if any) and retry the backup.

2. If you want to create a backup using the Tool, you must create a new backup file instead of overwriting/appending to an existing backup file.


Sarabpreet Singh

8 Apr

SQL Tip-11 | Using SQL Server Backup to Windows Azure Tool

As you know there are so many cool benefits of using Windows Azure Storage Account, everybody want to use it to get advantage. Last month i blogged about “SQL DB Backup to URL\Cloud” feature which was first introduced in SQL Server 2012 and later in SQL 2014 it was integrated completely in every aspect.

But what if you are still running old version of SQL Server and want to take advantage of Cloud?

Microsoft is being very kind and they are providing you a workaround. They released a small tool which can help you create a one cloud backup strategy that means you will be able to take backups on cloud for prior SQL versions like SQL Server 2005, 2008 and SQL 2008 R2.

By using this tool you get all the latest features like Backup on Cloud, using Encryption and Compression in all earlier versions.  Currently these features are available only in following versions\editions.

Feature Version Edition
Backup Encryption 2014 Enterprise, Standard and Business Intelligence Edition
Backup Compression 2008 Enterprise Edition
Backup Compression 2008 R2 Enterprise and Standard Edition

Isn’t it cool you get all latest features even if you’ve not upgraded to new version\edition.

The Utility can be downloaded from this link and the utility is less than 10 MB is size. Let’s see how to install, configure and use this tool.

Installation of this tool is quite simple just like any other regular software.

1  2

3   4

The tool installs a service which takes the responsibility of taking the backups from local system to Cloud. The tool also creates a separate login for this tool with the name of “SQLBackup2Azure.



To configure this tool you’ve to provide a local path of a folder where your backups will be placed – this location will act as a source for this tool. You need to also mention the file name\extension which will be picked to be backed-up on the cloud. The best part is you can use wildcards to select the type of files.

config1 config2


Next you need to mention the details about your Windows Azure Storage Account, this is similar to creating a credential which will provide necessary access to this tool to backup the files on Azure. You get an option to Verify the account details right from the wizard.


In the next page you also get an option to use Encryption and Compression for all these backups.

config4 config5

Read the next part here…

Sarabpreet Singh