powershellvariablesconnection-stringazure-keyvaultazure-sql

Set PowerShell Azure SQL Connection String Parameter


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!


Solution

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