Troubleshooting Errors with SQL Server 2014 Encrypted Backups May 22nd, 2014

Vinod Kumar

The more I play around with Encrypted backups, the more I get to uncover things. It is highly recommended that you read the other posts (Troubleshooting SQL Server 2014 Encrypted Backups) before going ahead because these are all linked in one way or other. In this blog post, let me call out some of the common errors that we can encounter when working with Encrypted backups – couple of them have been already called out at SQL Server 2014: Backup to Azure Blob post too.

Is this not similar to TDE?

When I was talking about Encrypted backups, someone at a our local user group (SQLBangalore UG) asked. I thought it is worth to mention a word over the blog. The fundamental difference between Encrypted backups and TDE is:

  1. TDE encrypts the whole database, Encrypted backups don’t encrypt the database.
  2. Encrypted backups encrypt ONLY the backup.

01 – Missing Certificate

The simplest and basic error one can get is when the certificate is not available on the server where we are restoring. The error message we will get is:

Msg 33111, Level 16, State 3, Line 133
Cannot find server certificate with thumbprint ‘0x1C706DAAC3059BDDAD4573DBF3F7D7A685397F21’.
Msg 3013, Level 16, State 1, Line 133
RESTORE DATABASE is terminating abnormally.

Well, to resolve this – restore from the certificate from our backup location. I did talk about this in our previous post – SQL Server 2014: Restoring Encrypted Backups.

02 – Certificate missing private key

This is a little misleading but the certificate we restored missed the Private Key. The error we get is:

Msg 15507, Level 16, State 30, Line 166
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 166
RESTORE DATABASE is terminating abnormally.

Make sure you restore the certificate with the Private key as backed up before. If the certificate was backed up without a private key, we must take a new backup from the source server of the certificate with the private key.

03 – Invalid Algorithm used

When the backup command has an invalid algorithm this error is raised.

Msg 102, Level 15, State 1, Line 88
Incorrect syntax near ‘AES_266’.

The supported algorithms as per documentation are – AES 128, AES 192, AES 256, and Triple DES.

04 – Backup cannot find Certificate

When an backup operation fails to find an certificate to do encrypted backups, the following error message is raised.

Msg 15151, Level 16, State 1, Line 81
Cannot find the certificate ‘BackupCert’, because it does not exist or you do not have permission.
Msg 3013, Level 16, State 1, Line 81
BACKUP DATABASE is terminating abnormally.

05 – Backup fails when adding to an encrypted backup set
This is a strange error actually. If you have different encryption keys used, then they cannot be part of the same backup set. This means an encrypted backup media needs to have the same key for encryption else a separate backup needs to be created if we are using a different key / thumbprint.

Msg 3095, Level 16, State 1, Line 81
The backup cannot be performed because ‘ENCRYPTION’ was requested after the media was formatted with an incompatible structure. To append to this media set, either omit ‘ENCRYPTION’ or create a new media set by using WITH FORMAT in your BACKUP statement. If you use WITH FORMAT on an existing media set, all its backup sets will be overwritten.   
Msg 3013, Level 16, State 1, Line 81
BACKUP DATABASE is terminating abnormally.

This wraps the complete series on encrypted backup with SQL Server 2014. I am sure there are a number of other errors which I might have skipped. But these are the most common error messages we are likely to encounter with working with Encrypted backups. Do let me know if you are using this feature in your deployments.

Tags: , , , , , , , , ,

This entry was posted on Thursday, May 22nd, 2014 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply