Encryption options in RESTORE HEADERONLY

Published Mar 23 2019 01:42 PM 235 Views
First published on MSDN on Aug 10, 2015

Backup Encryption was introduced in SQL Server 2014 as way to encrypt the data while creating a backup. By specifying the encryption algorithm and the encryptor (a Certificate or Asymmetric Key) when creating a backup, you can create an encrypted backup file.

However, there was no ability to look at encryption metadata in an encrypted backup to determine attributes like encryptor type, encryption algorithm and thumbprint that were used to take the backup.

RESTORE HEADERONLY in SQL Server 2014 Service Pack 1 will now show 3 new attributes:

KeyAlgorithm

nvarchar(32)

The encryption algorithm used to encrypt the backup. NO_Encryption indicates that the backup was not encrypted. When the correct value cannot be determined the value should be NULL.

EncryptorThumbprint

varbinary(20)

The thumbprint of the encryptor which can be used to find certificate or the asymmetric key in the database. When the backup was not encrypted, this value is NULL.

EncryptorType

nvarchar(32)

The type of encryptor used: Certificate or Asymmetric Key. When the backup was not encrypted, this value is NULL.


Sample output from restore headeronly against an encrypted backup in SQL Server 2014 SP1(some columns removed for brevity):

RESTORE
HEADERONLY
FROM
DISK='C:\SQL\ProductionDB_Encrypted.bak'


DatabaseName    KeyAlgorithm        EncryptorThumbprint                        EncryptorType

ProductionDB        aes_256        0xC70C89786EBC0489EB059775D44AF04BF52DAB90        CERTIFICATE

Version history
Last update:
‎Mar 23 2019 01:42 PM
Updated by: