Configure SQL Server Transparent Data Encryption with PowerShell

By:   |   Updated: 2020-02-13   |   Comments (5)   |   Related: > Encryption


Problem

Transparent Data Encryption is way to protect the data in your SQL Server database. This can be setup using T-SQL, SSMS and using PowerShell.  In this tip we will cover the steps using PowerShell which allow you to configure TDE for any database on any instance of SQL Server.

Solution

In this walkthrough, we look at how to use PowerShell and Server Management Object (SMO) to enable TDE following these 4 steps:

  1. Create a master key in master database
  2. Create a certificate which protected by the master key
  3. Create a database encryption key which protected by the certificate
  4. Enable transparent data encryption for particular database

1 - Create a SQL Server Database Master Key using PowerShell

First of all, we need to create a database master key - ##MS_DatabaseMasterKey## 

param (
    [string]$InstanceName = $( Read-Host "Insert your SQL Server Name" ),   
    [string]$password = $( Read-Host -asSecureString "Enter Password for Master Key" )
)
 
#Import SQLPS module
Import-Module SQLPS 
 
#Set SQL Server Management Object(SMO) to variable
$SQLServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $InstanceName
 
#Create a Database Master Key
$MasterDatabase = $SQLServer.Databases["master"]
$DBMasterKey = New-Object -TypeName Microsoft.SqlServer.Management.Smo.MasterKey -ArgumentList $MasterDatabase
$DBMasterKey.Create($password)
 
Write-Host -NoNewline "Database Master Key has been created" -BackgroundColor Green -ForegroundColor Black

You can check if the master key was created successfully using this T-SQL. 

SELECT name, symmetric_key_id, algorithm_desc, create_date
FROM sys.symmetric_keys 
WHERE symmetric_key_id = 101

As you can see below, the database master key was created successfully.

query results

This is equivalent in T-SQL to create a master key:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'JohnnieWalker1867*';  
GO

2 - Create a SQL Server Certificate using PowerShell

The second step is the creation of certificate protected by the master key, in my case, the name of the certificate is CertForTDE, and the expiration date is January 3, 2021.My Instance name is SQLServer2019. 

param (
    [string]$InstanceName = $( Read-Host "Insert your SQL Server Name" ),    
    [string]$CertificateName = $( Read-Host "Enter Name of New Certificate" ),
    [string]$Subject = $( Read-Host "Enter Subject, i.e. - Certification for TDE" ),    
    [string]$StartDate = $( Read-Host "Enter Start Date of Certificate, i.e. - January 22,2020" ),    
    [string]$ExpirationDate = $( Read-Host "Enter Start Date of Certificate, i.e. - January 22,2021" )    
)
#Import SQLPS module
Import-Module SQLPS 
 
#Set SQL Server Management Object(SMO) to variable
$SQLServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $InstanceName
 
#Creation of Certificate
$MasterDatabase = $SQLServer.Databases["master"]
$Certificate = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Certificate -ArgumentList $MasterDatabase, $CertificateName
 
#Set Certificate Properties
$Certificate.Subject = $Subject
$Certificate.StartDate = $StartDate
$Certificate.ExpirationDate = $ExpirationDate
$Certificate.Create() 

PowerShell output:

create certificate

This is equivalent in T-SQL to create a master key:

USE master;
GO
CREATE CERTIFICATE CertforTDETest
WITH SUBJECT = 'Certificate for TDE'
START_DATE = '20200122'
EXPIRY_DATE = '20210122';  
GO

We can check this with T-SQL as follows:

select name, subject, start_date, expiry_date FROM sys.certificates
query results

3 - Create a SQL Server Database Encryption Key using PowerShell

In this step, we need to create database encryption key protected by our new certificate from the previous step. The name of the database is PigeonSQL, we will use the AES256 encryption algorithm. 

param (
    [string]$InstanceName = $( Read-Host "Insert your SQL Server Name" ),   
    [string]$CertificateName = $( Read-Host "Enter your Certificate for TDE" ),
    [string]$DatabaseName = $( Read-Host "Enter your Database Name" )
)
#Import SQLPS module
Import-Module SQLPS 
 
#Set SQL Server Management Object(SMO) to variable
$SQLServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $InstanceName
 
#Creation of Database Encryption Key
$DatabaseEncryption = New-Object Microsoft.SqlServer.Management.Smo.DatabaseEncryptionKey
$DatabaseEncryption.Parent = $SQLServer.Databases[$DatabaseName]
$DatabaseEncryption.EncryptionAlgorithm = [Microsoft.SqlServer.Management.Smo.DatabaseEncryptionAlgorithm]::Aes256
$DatabaseEncryption.EncryptionType = [Microsoft.SqlServer.Management.Smo.DatabaseEncryptionType]::ServerCertificate
 
#Encryption By Server Certificate CertForTDE
$DatabaseEncryption.EncryptorName = $CertificateName
$DatabaseEncryption.Create() 

PowerShell output:

create encryption key

This is the equivalent in T-SQL:

USE PigeonSQL;
GO
CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256 
ENCRYPTION BY SERVER CERTIFICATE CertForTDETest;  
GO

4- Enable SQL Server Transparent Data Encryption on a Database Using PowerShell

The last of all, we need to enable TDE for our database PigeonSQL with the following commands:

param (
    [string]$InstanceName = $( Read-Host "Insert your SQL Server Name" ),   
    [string]$DatabaseName = $( Read-Host "Enter your Database Name" )
)
 
#Import SQLPS module
Import-Module SQLPS 
 
#Set SQL Server Management Object(SMO) to variable
$SQLServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $InstanceName
 
#Enable Transparent Data Encryption
$DatabaseName = $SQLServer.Databases[$DatabaseName]
$DatabaseName.EncryptionEnabled = $true
$DatabaseName.Alter()

PowerShell output:

enable tde

This is equivalent code in T-SQL:

ALTER DATABASE PigeonSQL  
SET ENCRYPTION ON;  
GO

Check SQL Server TDE Status

We can use the following to check the TDE status of the database.

SELECT 
d.name,
d.is_encrypted,
dek.encryption_state,
dek.percent_complete,
dek.key_algorithm,
dek.key_length
FROM
sys.databases as d
INNER JOIN sys.dm_database_encryption_keys AS dek
ON d.database_id = dek.database_id
check tde status

As we can see our database PigeonSQL is encrypted with Transparent Data Encryption.  Also, the TempDB database will be automatically encrypted. This might have a performance impact after enabling TDE especially when using snapshot isolation level on any database on the SQL Server instance that provides row versioning which means that each time a row is updated, inserted or deleted, SQL Server stores a copy of the original row in TempDB.

We can also confirm that TDE is enabled in SSMS by right clicking on the database and selecting Properties. On the Options page we can see Encryption Enabled is True.

check tde status
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Filip Holub Filip Holub is a big SQL Server enthusiast from the Czech Republic with 5 years of DBA experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2020-02-13

Comments For This Article




Sunday, May 17, 2020 - 1:23:49 PM - tonny poulsen Back To Top (85682)

Hi Filip

Nice article.

Have you considered to include backups of the master keys and certificate in the code i.e 

--Backup SMK
Use master
BACKUP SERVICE MASTER KEY TO FILE = 'C:\PATH\ServiceMasterKey_SERVERNAME'
ENCRYPTION BY PASSWORD = 'SMKPassword' 
GO

--Backup DMK
USE master
BACKUP MASTER KEY TO FILE = 'C:\PATH\DatabaseMasterKey_master_SERVERNAME'
ENCRYPTION BY PASSWORD = 'DMKBckPassword';
GO

--Backup certificate
BACKUP CERTIFICATE NAME_Certificate
TO FILE = 'C:\PATH\NAME_Certificate.cer'
WITH PRIVATE KEY (FILE = 'C:\PATH\NAME_Certificate.pvk', ENCRYPTION BY PASSWORD = 'CertPassword')

Monday, April 6, 2020 - 5:47:29 AM - Filip Holub Back To Top (85293)

Hi Elango,

Sorry, I don't have any PowerShell code for that portion of the example.

Filip

Wednesday, April 1, 2020 - 1:16:34 PM - Elango Back To Top (85244)

In step 2: we are creating Database certificate. Do you have a powershell script for the following equivalent?

Example:

CREATE CERTIFICATE Shipping11 FROM 
FILE = 'c:\Shipping\Certs\Shipping11.cer' WITH PRIVATE 
KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk', DECRYPTION BY 
PASSWORD = 'sldkflk34et6gs%53#v00'); GO

Friday, February 14, 2020 - 9:56:54 AM - Filip Holub Back To Top (84482)

Hi Pavan,

Unfortunately, this feature is still only for Enterprise Edition, with SQL 2016 SP1 you can you another type of encryption feature such as Always Encrypted in Standard edition. Otherwise new SQL Server 2019 supports Transparent data encryption in Standard edition which is nice and step forward from Microsoft.

Filip

Friday, February 14, 2020 - 7:33:24 AM - Pavan Back To Top (84476)

Can I use these scripts for SQL Server 2016 SP1 Standard Edition? I know the TDE Option available only for Enterprise editions.














get free sql tips
agree to terms