TDE Encryption on AdventureWorks2014
This how-to guide covers how to protect a database by using transparent data encryption (TDE), and then move the database to another instance of SQL Server by using SSMS or T-SQL. TDE performs real-time I/O encryption and decryption of log files. The encryption uses a database encryption key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an EKM module.
Limitations and Restrictions
· When moving a TDE protected database, you must also move the certificate or asymmetric key that is used to open the DEK. The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files. For more information, see Transparent Data Encryption (TDE).
· You must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key does not have to be the same as the database master key password.
· SQL Server stores the files created here in C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA by default. Your file names and locations might be different.
Permissions
· Requires CONTROL DATABASE permission on the master database to create the database master key.
· Requires CREATE CERTIFICATE permission on the master database to create the certificate that protects the DEK.
· Requires CONTROL DATABASE permission on the encrypted database and VIEW DEFINITION permission on the certificate or asymmetric key that is used to encrypt the database encryption key.
Using SQL Server Management Studio
1. Create a database master key and certificate in the master database. For more information, see Using Transact-SQL below.
2. Create a backup of the server certificate in the master database. For more information, see Using Transact-SQL below.
3. In Object Explorer, right-click the Databases folder and select New Database.
4. In the New Database dialog box, in the Database name box, enter the name of the new database.
5. In the Owner box, enter the name of the new database's owner. Alternately, click the ellipsis (...) to open the Select Database Owner dialog box. For more information on creating a new database, see Create a Database.
6. In Object Explorer, click the plus sign to expand the Databases folder.
7. Right-click the database you created, point to Tasks, and select Manage Database Encryption.
The following options are available on the Manage Database Encryption dialog box.
Encryption Algorithm
Displays or sets the algorithm to use for database encryption. AES128 is the default algorithm. This field cannot be blank. For more information on encryption algorithms, see Choose an Encryption Algorithm.
Use server certificate
Sets the encryption to be secured by a certificate. Select one from the list. If you do not have the VIEW DEFINITION permission on server certificates, this list will be empty. If a certificate method of encryption is selected, this value cannot be empty. For more information about certificates, see SQL Server Certificates and Asymmetric Keys.
Use server asymmetric key
Sets the encryption to be secured by an asymmetric key. Only available asymmetric keys are displayed. Only an asymmetric key protected by an EKM module can encrypt a database using TDE.
Set Database Encryption On
Alters the database to turn on (checked) or turn off (unchecked) TDE.
8. When finished, click OK.
Please refer to https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-server?view=sql-server-ver15 for more information.
This is a step-by-step guide for encrypting [AdventureWorks2014]:
1. Take a backup of the database by right-clicking on it Tasks > Back up...
2. Click on Backup Type: Full and click Okay
3. After database is finished backing up, go to the bi_backup folder to double-check the files are there.
4. Create a new (test) database, which will be used as our restore destination database. (You can skip this step as the script would automatically create the database)
5. After creating the database navigate to the Control Database > Programmability > Stored Procedures and right-click on the etl_setup_restore_db and select script as > Create To > New Query Editor Window
6. Repoint the script to point at the newly created database and execute it in order to restore it in the new destination
ALTER PROCEDURE etl_setup_restore_db
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--ALTER DATABASE AdventureWorks2014 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE IF EXISTS AdventureWorks2014;
RESTORE DATABASE AdventureWorks2014
FROM
DISK = 'C:\FileStructure\bi_Projects\bi_backup\AdventureWorks2014'
GO
7. Create a database master key and a certificate in the master database
USE master;
GO
-- CREATE MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password1';
GO
-- Check if Database Master Key Exists
SELECT name
FROM sys.symmetric_keys
WHERE name LIKE '%DatabaseMasterKey%';
-- CREATE Certificate
CREATE CERTIFICATE MyTDECertificate
WITH SUBJECT = 'TDE Certificate';
GO
/*
-- this provides the list of certificates
SELECT * FROM sys.certificates
-- this provides the list of databases (encryption_state = 3) is encrypted
SELECT * FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
*/
8. Back up the certificate to a location of your choice and add a private key
-- ALWAYS BACKUP YOUR CERTIFICATES AND PRIVATE KEYS
BACKUP CERTIFICATE MyTDECertificate
TO FILE = 'C:\FileStructure\bi_Projects\bi_certificates\MyTDECertificate.cer'
WITH PRIVATE KEY (
FILE='C:\FileStructure\bi_Projects\bi_certificates\MyTDECertificate.pvk',
ENCRYPTION BY PASSWORD='Password1'
)
9. Alter the database and set encryption level to ON
USE AdventureWorks2014;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyTDECertificate;
GO The database is TDE encrypted. :)
10. Back up the database - AdventureWorks2014
Also check my Restore TDE Encrypted Database Article here!
_edited_edited.jpg)
Comments