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!

I'm not entirely sure what you are trying to do. Are you trying to remove the partial string from the $sqlServerfqdn variable or are you trying to prevent the variable from getting the ":@{FullyQualifiedDomainName" added in the first place? If the second option, could you please share how you are setting that variable. — SamaraSoucy
Here's how I set the variable: $sqlserverfqdn = Get-AzSqlServer -ResourceGroupName $resourceGroup -ServerName $sqlServer | Select FullyQualifiedDomainName. This returns a 'FullyQualifiedDomainName' header with the name of my db listed under it. I only want the name of the database returned. When I create the secret in my vault (see cmd above for how I create secret) '@{FullyQualifiedDomainName=' is also being added which isn't a valid connection string. I need '@{FullyQualifiedDomainName=' removed so only server name host name (fdqn) is listed/returned. Hope this helps. — jrd1989

SamaraSoucy SamaraSoucy · Accepted Answer · 2020-12-10T03:28:08

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=",""

Set PowerShell Azure SQL Connection String Parameter

1 Answers