For over seven years, Scalability Engineers has charted the course in what it means to go the “extra mile” for clients. What started as a small company has flourished into a leading, well-respected, 360° technology solutions provider in databases.
Consultation
Sourabh - Assistant Manager- Infra
Organizations can encrypt data while making backups in SQL Server 2014. In the past, backup encryption could be accomplished using third-party technologies or transparent data encryption (TDE), a SQLfeature. Third-party tools are costly, and TDE encrypts the entire database, not just the backup. Organizations and customers have been requesting native encryption for backups in SQL Server for years. This is undoubtedly one of the most eagerly anticipated features.
Whether we create the backup on-premises or via Windows Azure, encrypted backups are supported.
Supported encryption options
Encryption Algorithm: AES 128, AES 192, AES 256, and Triple DES
Encryptor: A certificate or asymmetric Key
Benefits of Backups Encryption
1. Encrypting the backups helps secure data.
2. TDE-encrypted databases can also be encrypted with encryption.
3. Encryption is supported for SQL Server Backup to Microsoft Azure backups, which adds another layer of protection to off-site backups.
4. You can use Extended Key Management (EKM) providers to combine encryption keys.
Follow these steps to encrypt a database backup using a Certificate:
1. Create a Database Master Key
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test123$'
2. Create a Certificate
CREATE CERTIFICATE BackupCertificate
WITH SUBJECT ='Backup Encryption Certificate'
3. Create a CERTIFICATE key with the private key
BACKUP CERTIFICATE BackupCertificate
TO FILE = '\\NODE1\Shared\BackupCertificate.cer'
WITH PRIVATE KEY(
FILE='\\NODE1\Shared\BackupCertificate.ppk',
ENCRYPTION BY PASSWORD ='Test123$')
4. Take backup with [WITH ENCRYPTION] option
BACKUP DATABASE TorDB
TO DISK = '\\NODE1\Shared\TorDB.bak' WITH ENCRYPTION
(ALGORITHM = AES_256, SERVER CERTIFICATE BackupCertificate)
Now we have an encrypted backup, let’s try to restore the same backup on another SQL server instance.
5. Create a Database Master Key
USE MASTER
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test123$'
6. Restore CERTIFICATE key with private key
CREATE CERTIFICATE BackupCertificate
FROM FILE = '\\NODE1\Shared\BackupCertificate.cer'
WITH PRIVATE KEY(
FILE ='\\NODE1\Shared\BackupCertificate.ppk',
DECRYPTION BY PASSWORD='Test123$')
7. Restore DATABASE
RESTORE DATABASE TorDB
FROM DISK = N'\\NODE1\Shared\TorDB.bak', Replace
SQL Server versions before 2014 cannot read encrypted backups*