Encryption options in RESTORE HEADERONLY
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