Saturday, October 4, 2014

0 SQL Server 2014: Backup Encryption

From SQL Server 2014, SQL Server support the backup encryption . Which means we can encrypted our backup file to enhance the security for the backup media.

  • Backup compression can also work with encryption.

prerequisites

Create the master key and certificate. Those will be used when we need to backup and restore the database.

---- Create Master key
USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@word'
GO
--- Crete Certificate
CREATE CERTIFICATE BackupCert
   WITH SUBJECT = 'BackupCert';
GO
select * from sys.certificates
GO
Use [master]
GO
--- Backup certificate
BACKUP CERTIFICATE BackupCert 
    TO FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'
    WITH PRIVATE KEY ( 
        FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey' , 
    ENCRYPTION BY PASSWORD = 'Pass@word');
GO
EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'
GO
EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey'
GO
image

sys.certificates

image

Create test Database

Create the test database and insert some sample data then backup without encryption.

--- crete test database
create database BackupEncryptionDB;
go 
use [BackupEncryptionDB]
go
CREATE TABLE t1 (c1 int,c2 varchar(20))
GO
INSERT INTO t1 VALUES
        ('1', 'Summer')
        ,('2', 'Sunny')
        ,('3','Skye');
GO
Backup database BackupEncryptionDB 
to disk='C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.bak'
with init, STATS=10;
GO
EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.bak'
GO

If we open the non backup file with notepad, we can find our data is present in the backup file as clear text.

image

Backup database with encryption

BACKUP DATABASE [BackupEncryptionDB]
TO DISK = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'
WITH
  init,format,
  ENCRYPTION (
   ALGORITHM = AES_256,
   SERVER CERTIFICATE = BackupCert
   ),
  STATS = 10
GO
EXEC master..xp_fileexist 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'
GO

image

The content of the backup file has been scramble by encryption.

image

BackupSET metadata

SELECT backup_set_id, name, backup_size, compressed_backup_size, key_algorithm, 
encryptor_thumbprint, encryptor_type 
FROM msdb.dbo.backupset
GO
SELECT media_set_id, is_password_protected, 
is_compressed, is_encrypted 
FROM msdb.dbo.backupmediaset
GO

image

Restore encrypted database backup

To simulate restore to another server or if the server certificate is lost for some reason. Lets drop the certificate and restore.

--- Remove the certificated to simulate in other server
DROP CERTIFICATE BackupCert
go
select * from sys.certificates
go
Restore database [BackupEncryptionDB]
from disk='C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'
with replace, stats=10
go

Because there is no certificated therefore SQL server can not validate the backup. Restore will fail with below error: 

Msg 33111, Level 16, State 3, Line 64
Cannot find server certificate with thumbprint '0x35FA80FB4FC5F4555B3F89FF914A98ABA212C5C3'.
Msg 3013, Level 16, State 1, Line 64
RESTORE DATABASE is terminating abnormally.

image

Lets restore the certificate and restore the database.

-- Restore the certificate
Use [master]
CREATE CERTIFICATE BackupCert
    FROM FILE = 'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.cert'
    WITH PRIVATE KEY ( 
            FILE =  'C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupCert.privatekey' , 
    DECRYPTION BY PASSWORD = 'Pass@word' );
GO
Restore database [BackupEncryptionDB]
from disk='C:\Database\MSSQL12.SGC4SQL2014\MSSQL\Backup\BackupEncryptionDB.encryption.bak'
with replace, stats=10
go

database successfully restore.

image

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates