Freigeben über

Encryption options in RESTORE HEADERONLY

  • Artikel

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

Comments

  • Anonymous
    November 02, 2015
    I am not getting columns 'KeyAlgorithm' 'EncryptorThumbprint' 'EncryptorType' RESTORE HEADERONLY command. My sql server version is '12.00.2000' do I need to update my sql server?

  • Anonymous
    January 06, 2016
    Yes Ashish, you need to update to SQL Server 2014 SP1. Download link: www.microsoft.com/.../details.aspx