PROCESS ON HOW TO ENCRYPT DATABASE

(NOTE: Backup all the databases before doing encryption of database and store them in a safe location)

Encryption is the process of obfuscating data by the use of a key or password. This can make
the data useless without the corresponding decryption key or password. Encryption does not solve
access control problems. However, it enhances security by limiting data loss even if access controls are
bypassed. For example, if the database host computer is misconfigured and a hacker obtains sensitive
data, that stolen information might be useless if it is encrypted.

1. Login to your SQL Server through SSMS (SQL Server Management Studio) Client tool with
administration rights (sa).

2. Click the New Query and run the script below to check if the database is not encrypted.

--Check TDE status

SELECT DB_Name(database_id) As [DB Name], encryption_state, encryption_state_desc,
percent_complete, encryption_scan_state_desc
FROM sys.dm_database_encryption_keys
Go
--Check TDE for Tempdb and user datababase
SELECT name, is_encrypted
FROM sys.databases
Go

DATABASE ENCRYPTION
3. Open Command Prompt as an administrator right and make directory on which path you want to
save the certificates of SQL Database Encryption. (Note: Do this to make the created folder have
administration rights).

4. After checking if the target Database is not Encrypted, copy, and RUN the script below to Encrypt
the Database. (NOTE: Change the corresponding Database name and Path Location of certificates
– path location that you make earlier. IF this is the first time you will create an encrypted database
run all the scripts, hence, don’t include the following;
“--CREATE MASTER KEY
USE master;

Go
CREATE MASTER KEY ENCRYPTION
BY PASSWORD=’YourPasswordHere’
GO).”

DATABASE ENCRYPTION
--CREATE MASTER KEY
USE master;

Go
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='YourPasswordHere'
GO

--CREATE CERTIFICATE
CREATE CERTIFICATE AUDIT_TDE_CERT --changeDB name
WITH SUBJECT='AUDITTDE' --changeDB name

--BACKUP CERTIFICATE
BACKUP CERTIFICATE AUDIT_TDE_CERT
TO FILE ='D:\SQL_Encryption\AUDIT_TDE_CERT.cer' --change/rename Path
WITH PRIVATE KEY (FILE='D:\SQL_Encryption\AUDIT_TDE_CERT.pvk', --
change/rename Path
ENCRYPTION BY PASSWORD='YourPasswordHere') --change password

--ENCRYPT DATABASE
USE [AUDIT] --changeDB name

GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE AUDIT_TDE_CERT; --Based on certificate
created
GO

--TURN ON ENCRYPTION
ALTER DATABASE [AUDIT] --changeDB name
SET ENCRYPTION ON;
GO

DATABASE ENCRYPTION
DATABASE ENCRYPTION
5. After the script above, run again the SQL Encryption Checker to determine that the target database is already
encrypted.

6. Check the created location if there is files created during database encryption.

DATABASE ENCRYPTION
7. If you need to Encrypt a new Database, Login to your SSMS (SQL Server Management Studio) Client tool and
Run the command below.

USE master;

--CREATE CERTIFICATE
CREATE CERTIFICATE RESTBASE_TDE_CERT --changeDB name
WITH SUBJECT='RESTBASETDE' --changeDB name

--BACKUP CERTIFICATE
BACKUP CERTIFICATE RESTBASE_TDE_CERT
TO FILE ='D:\SQL_Encryption\RESTBASE_TDE_CERT.cer' --change/rename Path
WITH PRIVATE KEY (FILE='D:\SQL_Encryption\RESTBASE_TDE_CERT.pvk', --
change/rename Path
ENCRYPTION BY PASSWORD='YourPasswordHere') --change password

--ENCRYPT DATABASE
USE [RESTBASE] --changeDB name

GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE RESTBASE_TDE_CERT; --Based on certificate
created
GO

--TURN ON ENCRYPTION
ALTER DATABASE [RESTBASE] --changeDB name
SET ENCRYPTION ON;
GO

DATABASE ENCRYPTION
DATABASE ENCRYPTION
8. Run the Scripts below to check the Database Encryption Status.

--Check TDE status

SELECT DB_Name(database_id) As [DB Name], encryption_state, encryption_state_desc,
percent_complete, encryption_scan_state_desc
FROM sys.dm_database_encryption_keys
Go
--Check TDE for Tempdb and user datababase
SELECT name, is_encrypted
FROM sys.databases
Go

DATABASE ENCRYPTION
 PROCESS ON HOW TO BACKUP AND RESTORE ENCRYPTED DATABASE

BACKUP ENCRYPTED DATABASE

1. Login to your SQL Server through SSMS (SQL Server Management Studio) Client tool with
administration rights (sa).

2. Click the New Query and run the script below to check if the database is encrypted.
--Check TDE status
SELECT DB_Name(database_id) As [DB Name], encryption_state, encryption_state_desc,
percent_complete, encryption_scan_state_desc
FROM sys.dm_database_encryption_keys
Go
--Check TDE for Tempdb and user datababase
SELECT name, is_encrypted
FROM sys.databases
Go

DATABASE ENCRYPTION
3. Right click on the target Database> TASK> BACKUP.

DATABASE ENCRYPTION
4. Specify where you want to save the backup of your encrypted database.

5. Click the Media Options and Select “Back up to a new media set, and erase all existing backup
sets” then input New media set name to your choice name.

DATABASE ENCRYPTION
6. Then Click the Backup Options and under Encryption, Check the Encrypt Backup.
Choose the algorithm: “AES 256” and in the Certificate or Asymmetric key: “NAME OF YOUR
DATABASE CERTIFICATE”

7. Then click OK to proceed with the backup.

8. Now check the folder where you set the backup file.

DATABASE ENCRYPTION
RESTORING ENCRYPTED DATABASE

1. Login to your SQL Server through SSMS (SQL Server Management Studio) Client tool with
administration rights (sa).

2. Open Command Prompt as an administrator right and make directory on which path you want to
save the certificates of SQL Database Encryption. (Note: Do this to make the created folder have
administration rights – Copy the backup and certificates of your encrypted database.).

DATABASE ENCRYPTION
3. Open a new Query and run the script below to import the certificate of your target database to
be restored.

USE master
Go
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='YourPasswordHere' --Change password
GO

CREATE CERTIFICATE AUDIT_TDE_CERT

FROM FILE = 'D:\SQL_Encryption\AUDIT_TDE_CERT.cer' --Location of your copied Certs
WITH PRIVATE KEY(FILE = 'D:\SQL_Encryption\AUDIT_TDE_CERT.pvk', --Location of your
copied Certs
DECRYPTION BY PASSWORD = 'YourPasswordHere' --Password of your Encrypted Database
)

DATABASE ENCRYPTION
4. Now in the database, right click on it and Click Restore Database.

5. Click the Device and find the backup file of encrypted database you want to restore and click
Okay.

DATABASE ENCRYPTION
6. Wait until the process is completed.

7. Now you successfully restored the encrypted database.

DATABASE ENCRYPTION
8. Lastly check if the database is still encrypted. It should be encrypted.

DATABASE ENCRYPTION