Set PowerShell Azure SQL Connection String Parameter

Question

I have an Azure SQL database in one resource group and a key vault in a separate resource group. Everything is already deployed but I'd like to assign the connection string values of the sql db to a variable and user it to create a secret in my key vault resource. I currently have this set as the connection string value:

$dbsecretvalue = ConvertTo-SecureString -String "Server=tcp:$sqlServerfqdn,1433;Initial Catalog=$database;Persist Security Info=False;User ID=$adminlogin;Password=$password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30" -AsPlainText -Force

The problem is in key vault this additional text is being added to the secret value instead of just the server name: Server=tcp:@{FullyQualifiedDomainName=servername.azure.net......

How do I go about removing the additional text so only the FQDN of my sql db is returned when I set the variable (ex: databaseserver.azure.net instead of @{FullyQualifiedDomainName=databaserver.azure.net)? I have very limited powershell experience so any help/advice would be appreciated.

Thanks!

Answer 1

There are a couple ways to do this. The simplest is going to be to break this up into two lines:

$server = Get-AzSqlServer -ResourceGroupName $resourceGroup -ServerName $sqlServer
$sqlserverfqdn = $server.FullyQualifiedDomainName
$sqlserverfqdn
#output databaserver.azure.net

This command is fine, and in general Powershell will try to make the JSON into an object by default, but once in awhile piping into ConvertFrom-Json will get the data loaded into an object you can manipulate that way

If you need to clean a string, then the replace method will usually handle it:

$fqdn = '@{FullyQualifiedDomainName=databaserver.azure.net'
$sqlserverfqdn = $fqdn -replace "@{FullyQualifiedDomainName=",""