Forum Discussion

TomWechsler's avatar
TomWechsler
MVP
Oct 25, 2020

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

 

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

No RepliesBe the first to reply

Resources