Create an Encrypted Backup in SQL Server 2014 | SansSQL

Wednesday, May 7, 2014

Create an Encrypted Backup in SQL Server 2014

Encryption for Backups is a new feature introduced in SQL Server 2014 and the benefits of this option are
  1. Encrypting the database backups helps secure the data.
  2. Encryption can also be used for databases that are encrypted using TDE.
  3. Encryption is supported for backups done by SQL Server Managed Backup to Windows Azure, which provides additional security for off-site backups.
  4. This feature supports multiple encryption algorithms including AES 128, AES 192, AES 256, and Triple DES
  5. You can integrate encryption keys with Extended Key Management (EKM) providers. 
The following are pre-requisites for encrypting a backup:
  1. Create a Database Master Key for the master database.
    USE master;
    GO
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'somepass@word123';
    GO
    
  2. Create a certificate or asymmetric Key to use for backup encryption.
    Use Master
    GO
    CREATE CERTIFICATE CertforBackupEncryption
       WITH SUBJECT = 'Certificate for Backup Encryption ';
    GO
    
Backup the database with encryption:
BACKUP DATABASE [SansSQL]
TO DISK = N'C:\Backup\SansSQL.bak'
WITH
  INIT,
  COMPRESSION,
  ENCRYPTION 
   (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = CertforBackupEncryption
   ),
  STATS = 10
GO

Restoring the encrypted backup:
SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key. If you are restoring the encrypted backup to a different instance, you must make sure that the certificate is available on that instance.

Referencehttp://msdn.microsoft.com/en-us/library/dn449489(v=sql.120).aspx

1 comment:

Sam Maron said...

Whether your business or organization is just starting out, or the network of IT assets has grown to include dozens or more endpoints, it is important to consider an online backup service. By subscribing to the best data rooms service, you can ensure that your data is protected and secure from a number of threats, such as an employee mistakenly deleting an important file, a hardware issue with a device, or a system-wide outage across the network.

Post a Comment