Create Database Master Key and Certificate
- USE master database.
- You should backup the Certificate (With Master Key) as soon as you create the certificate.
USE master; GO /* DROP CERTIFICATE Mycertificate01 DROP MASTER KEY */ -- Creates the database master key. (This is not the service master key) -- The key is encrypted using the password "MyPassword01". CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword01'; GO CREATE CERTIFICATE Mycertificate01 WITH SUBJECT = 'Mycertificate01'; GO USE master; GO BACKUP CERTIFICATE Mycertificate01 TO FILE = 'D:\TDE\Mycertificate01_backup' WITH PRIVATE KEY ( FILE = 'D:\TDE\MyMasterKey_backup', ENCRYPTION BY PASSWORD = 'MyPassword01' ); GO
- You can not create a Master Key if a Master Key is already created.
- You can not drop a Master Key if there are existing Certificate created with the Master Key.
Enable Encryption on Specific Database
Note: Go to the specific database. For example: USE TestEncryptedDB.
USE TestEncryptedDB; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE Mycertificate01; GO -- Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database. ALTER DATABASE TestEncryptedDB SET ENCRYPTION ON; GO
- If you get the error below while trying to encrypted the database, run a log backup. Error: This command requires a database encryption scan on database ‘TestEncryptedDB’. However, the database has changes from previous encryption scans that are pending log backup. Take a log backup and retry the command.
- Enabling encryption on a large database will take some time.
Database Encryption Check Query
-- Same for master or specific database. SELECT db.name, db.is_encrypted, dm.encryption_state, dm.percent_complete, dm.key_algorithm, dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id; GO -- Different for master and specific database. SELECT * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3; GO -- Different for master and specific database. SELECT * FROM sys.certificates;
Backup files of databases that have TDE enabled are also encrypted by using the database encryption key. As a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available. For more information, see SQL Server Certificates and Asymmetric Keys.
Note: You must USE master database, else you will get the following error: Cannot find the certificate ‘Mycertificate01’, because it does not exist or you do not have permission.
Disabling Encryption on Specific Database
USE master go ALTER DATABASE TestEncryptedDB SET ENCRYPTION OFF USE TestEncryptedDB GO DROP DATABASE ENCRYPTION KEY
USE [master]; GO -- Create the database master key -- to encrypt the certificate CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'FirstServerPassw0rd!'; GO -- Create the certificate we're going to use for TDE CREATE CERTIFICATE TDECert WITH SUBJECT = 'TDE Cert for Test'; GO -- Back up the certificate and its private key -- Remember the password! BACKUP CERTIFICATE TDECert TO FILE = N'C:\SQLBackups\TDECert.cer' WITH PRIVATE KEY ( FILE = N'C:\SQLBackups\TDECert_key.pvk', ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!' ); GO -- Create our test database CREATE DATABASE [RecoveryWithTDE]; GO -- Create the DEK so we can turn on encryption USE [RecoveryWithTDE]; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert; GO -- Exit out of the database. If we have an active -- connection, encryption won't complete. USE [master]; GO -- Turn on TDE ALTER DATABASE [RecoveryWithTDE] SET ENCRYPTION ON; GO