Enable Microsoft SQL Server TDE
Applies to:
- DxEnterprise for Windows
Summary
Microsoft SQL Server Transparent Data Encryption (TDE) is designed to secure Microsoft SQL Server system and data. For more technical details, please see the following Microsoft document.
Information
The following is the recommended procedure to implement TDE on a DxEnterprise cluster.
Please ensure every node in the cluster is running under the same DOMAIN user account for the Microsoft SQL Server instance. Using a local account will cause the nodes to fail decryption of the data when the instance moves between nodes in the cluster. The below procedure works best when Microsoft SQL Server (domain) service account is part of the local Administrators group. If the service account is not part of the local Administrators group, please ensure that DxEnterprise 15.0.337.1337 (or later) is installed.
-- Connect to master database and (re)generate the service master key.
USE master
GO
ALTER SERVICE MASTER KEY REGENERATE
GO
-- Back up the service master key.
BACKUP SERVICE MASTER KEY TO FILE = 'pathsmk.bak' ENCRYPTION BY PASSWORD = 'instance_name'
GO
-- Create database master key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'instance_name'
GO
-- Back up the database master key.
BACKUP MASTER KEY TO FILE = 'pathdmk.bak' ENCRYPTION BY PASSWORD = 'instance_name'
GO
-- Create server certificate.
CREATE CERTIFICATE mycert WITH SUBJECT = 'my cert'
GO
-- Back up the server certificate and private key.
BACKUP CERTIFICATE mycert TO FILE = 'pathcert.bak' WITH PRIVATE KEY (FILE='pathprivatekey.bak', ENCRYPTION BY PASSWORD = 'instance_name')
GO
-- Switch to user database of interest.
USE [db]
GO
-- Create database encryption with server certificate.
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE mycert
GO
-- Enable database encryption.
ALTER DATABASE [db] SET ENCRYPTION ON
GO