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

sys.certificates
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.
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
The content of the backup file has been scramble by encryption.
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
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.
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.
0 comments:
Post a Comment