Deploy a database with the PowerShell in Azure and verify Transparent Data Encryption (TDE)!

[TomWechsler]

Hi Azure friends,

I used the PowerShell ISE for this configuration. But you are also very welcome to use Visual Studio Code, just as you wish. Please start with the following steps to begin the deployment (the Hashtags are comments):

#The first two lines have nothing to do with the configuration, but make some space below in the blue part of the ISE

Set-Location C:\Temp
Clear-Host

#So that you can carry out the configuration, you need the necessary cmdlets, these are contained in the module Az (is the higher-level module from a number of submodules)

Install-Module -Name Az -Force -AllowClobber -Verbose

#Log into Azure
Connect-AzAccount

#Select the correct subscription

Get-AzContext

Get-AzSubscription

Get-AzSubscription -SubscriptionName "your subscription name" | Select-AzSubscription

#Prefix for resources

$prefix = "tw"

#Let's create a SQL DB that we will encrypt (never use secrets in code, but for this demo it's fine!)

$Location = "westeurope"

$id = Get-Random -Minimum 1000 -Maximum 9999

$ResourceGroupName = "$prefix-sql-$id"

$SQLServerName = "$prefix-sql-$id"

$SQLDatabaseName = "$prefix-sql-db"

$SQLAdmin = "sqladmin"

$SQLAdminPassword = ConvertTo-SecureString -String 'P@ssw0rd007!' -AsPlainText -Force

$SQLAdminCredentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $SQLAdmin,$SQLAdminPassword

#Now create a resource group

$sqlRG = New-AzResourceGroup -Name $ResourceGroupName -Location $Location

#Create the SQL Server

$sqlServerParameters = @{

    ResourceGroupName = $sqlRG.ResourceGroupName

    Location = $Location

    ServerName = $SQLServerName

    SqlAdministratorCredentials = $SQLAdminCredentials

}

$sqlServer = New-AzSqlServer @sqlServerParameters

#Create the database

$databaseParameters = @{

    ResourceGroupName = $sqlRG.ResourceGroupName

    ServerName = $sqlServer.ServerName

    DatabaseName = $SQLDatabaseName

    RequestedServiceObjectiveName = "S0" 

    SampleName = "AdventureWorksLT"

}

$database = New-AzSqlDatabase @databaseParameters

#Check the TDE settings and remove encryption

$tdeParameters = @{

    ResourceGroupName = $sqlRG.ResourceGroupName

    ServerName = $sqlServer.ServerName

    DatabaseName = $database.DatabaseName

}

Get-AzSqlDatabaseTransparentDataEncryption @tdeParameters

#Disable TDE (possibly to check the performance without TDE)

Set-AzSqlDatabaseTransparentDataEncryption @tdeParameters -State Disabled

#Enable TDE (now check the performance again with TDE)

Set-AzSqlDatabaseTransparentDataEncryption @tdeParameters -State Enabled

Now you have used the PowerShell to deploy a Database in Azure and TDE verified! Congratulations!

#Delete all resources (when you no longer need it)

Remove-AzResourceGroup -Name $ResourceGroupName -Force

I hope this article was useful. Best regards, Tom Wechsler

P.S. All scripts (#PowerShell, Azure CLI, #Terraform, #ARM) that I use can be found on github! https://github.com/tomwechsler