Script for Backup Encryption

/**********************************************************************************************

This script demonstrates the process of creating, backing up, encrypting, and restoring a SQL Server 
database using Transparent Data Encryption (TDE) with a master key and certificate. The workflow includes:

1. Dropping and recreating the ExampleSecDB database.
2. Copying data from an external source into a new table.
3. Creating a master key and certificate in the master database for encryption purposes.
4. Backing up the master key and certificate to secure files with passwords.
5. Performing encrypted and compressed backups of the ExampleSecDB database.
6. Providing commented-out sections for cleanup (dropping keys, certificates, and the database).
7. Demonstrating the restoration of the master key and certificate on another system.
8. Restoring the encrypted database backups to a new SQL Server instance.
9. Querying the restored database to verify data integrity.

Important Notes:
- File paths and passwords used in this script are for demonstration purposes only and should be replaced 
    with secure values in production environments.
- Ensure that the backup files and key/certificate files are stored securely and access is restricted.
- The script assumes the existence of a source table (mydummydata.dbo.DUMMY) for data import.
- The script includes SAP HANA's "DUMMY" table reference for demonstration, which may need adjustment 
    for SQL Server environments.

**********************************************************************************************/
USE master;
GO

DROP DATABASE ExampleSecDB
GO

CREATE DATABASE ExampleSecDB
GO

USE ExampleSecDB
GO

SELECT *
INTO DUMMY
FROM mydummydata.dbo.DUMMY
GO

/*************************************************************
CREATE YOUR MASTER KEY AND YOUR CERTIFICATE FOR TDE AND ALL OTHER TYPES
OF ENCRYPTION
USE MASTER 
GO
DROP SYMMETRIC KEY SYMMKEY
GO
DROP CERTIFICATE MYCERTIFICATE
go
DROP MASTER KEY
*************************************************************/
GO

USE MASTER
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd'

CREATE CERTIFICATE MYCERTIFICATE
	WITH SUBJECT = 'ENCRPTYDATABASECERTIFICATE'

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

BACKUP MASTER KEY TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MasterKeyBackup' ENCRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';
GO

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

BACKUP CERTIFICATE MYCERTIFICATE TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKP'
WITH PRIVATE KEY (
		FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKPK'
		,ENCRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
		);

CLOSE MASTER KEY
GO

BACKUP DATABASE ExampleSecDB TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\ExampleSecDB.bak'
WITH FORMAT
	,INIT
	,MEDIANAME = N'Newnecryptedbackup'
	,NAME = N'ExampleSecDB-Full Database Backup'
	,ENCRYPTION (
		ALGORITHM = AES_256
		,SERVER CERTIFICATE = [MYCERTIFICATE]
		)
	,COMPRESSION
	,STATS = 10
GO

BACKUP DATABASE [ExampleSecDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SecdbCompress.bak'
WITH NOFORMAT
	,NOINIT
	,NAME = N'ExampleSecDB-Full Database Backup'
	,SKIP
	,NOREWIND
	,NOUNLOAD
	,COMPRESSION
	,STATS = 10
GO

/**********************************
use master 
DROP CERTIFICATE MYCERTIFICATE
DROP MASTER KEY
DROP DATABASE ExampleSecDB
GO
***********************************/
/**********************************
RESTORING YOUR MASTER KEY AND CERTIFICATE ON ANOTHER SYSTEM
***********************************/
RESTORE MASTER KEY
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MasterKeyBackup' DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd' ENCRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd' FORCE

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

CREATE CERTIFICATE MYCERTIFICATE
FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKP'
WITH PRIVATE KEY (
		FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\keys\MYCERTIFICATEBKPK'
		,DECRYPTION BY PASSWORD = '997jkhUbhk$w4ez0876hKHJH5gh'
		);
GO

DROP DATABASE ExampleSecDB

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Thisi$notaSecurePassw0rd';

RESTORE DATABASE ExampleSecDB
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\ExampleSecDB.bak'
WITH MOVE 'ExampleSecDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb.mdf'
	,MOVE 'ExampleSecDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb_log.ldf';
GO

RESTORE DATABASE ExampleSecDB
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SecdbCompress.bak'
WITH MOVE 'ExampleSecDB' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb.mdf'
	,MOVE 'ExampleSecDB_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Data\ExampleSecDb_log.ldf';
GO


GO

USE ExampleSecDB
GO

SELECT *
-- Selects data from the special SAP HANA system table "DUMMY", which is commonly used for queries that do not require actual table data.
FROM DUMMY

Please Consider Subscribing

Subscribe