Configure SQL Server Transparent Data Encryption with PowerShell


By:   |   Updated: 2020-02-13   |   Comments (2)   |   Related: More > 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


Last Updated: 2020-02-13


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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

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



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools