Create a database master key and certificate in the master database.
USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';
USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO
Create a backup of the server certificate in the master database.
BACKUP MASTER KEY TO FILE = 'c:\temp\exportedmasterkey'
ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2';
Enable the TDE on user Database
use dbname
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO
ALTER DATABASE skyProduction
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM sys.databases
GO
Backup the Encryption key
USE master
GO
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\temp\dbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\dbcert_Key.pvk' ,
ENCRYPTION BY PASSWORD = 'j3tt4IL!2' )
GO
-- Use to check status
USE master
GO
SELECT * FROM sys.certificates
-- encryption_state = 3 is encrypted
SELECT * FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
SELECT *
FROM sys.dm_database_encryption_keys;
SELECT * FROM sys.databases
--- Restore database on other machine (Decrypt the Database)
--1. Create the Master Key on Target server (Password need not be same)
USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'j3uwgq#)hqI*2'
--2. After a master key has been created, create a certificate by importing the certificate we created earlier (Take the Backup of the certificate).
--Here the ‘Decryption By Password’ parameter is same as that of source server.
CREATE CERTIFICATE TDECert
FROM FILE = 'E:\Backup\Production Server keys\dbCerts\dbcert.cer'
WITH PRIVATE KEY (FILE = 'E:\Backup\Production Server keys\dbCerts\dbcert_Key.pvk',
DECRYPTION BY PASSWORD = 'j3uwgq#)hqI*2')
--3. Restore the database as regular.
Using SQL Server Management Studio
- Create a database master key and certificate in the master database. For more information, see Using Transact-SQL below.
- Create a backup of the server certificate in the master database. For more information, see Using Transact-SQL below.
- In Object Explorer, right-click the Databases folder and select New Database.
- In the New Database dialog box, in the Database name box, enter the name of the new database.
- 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 .
- In Object Explorer, click the plus sign to expand the Databases folder.
- 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 .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 .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. - When finished, click OK.
Note