Lesson Learned #167: Working with Get-AzSqlDatabaseSecureConnectionPolicy

[Jose_Manuel_Jurado]

Today, I worked on a service request that our customer is using this CmdLet to obtain all the secure connection string. Unfortunately, this CmdLet is deprecated and this command will be removed in a future release and the recomendation is to use the SQL database blade in the Azure portal to view the connection strings. In this situation, as our customer needs to have this call working until they will be able to change and test the code we suggested a workaround.

At the end, this cmdlet is providing the connection string for the database, in this situation, we wrote the following script in order to provide this information in the same way that CmdLet does. 

Class ConnectionStrings
{
    [String] $ServerName
    [String] $DBName
    ConnectionStrings ([String] $ServerName, [String] $DBName)
    {
        $this.ServerName = $ServerName
        $this.DBName = $DBName
    }
    [string] AdoNetConnectionString()
    {
      return "Server=tcp:" + $this.ServerName + ".database.windows.net,1433;Initial Catalog=" + $this.DBName + ";Persist Security Info=False;User ID=UserName;Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
    }
    [string] JdbcConnectionString()
    {
      return "jdbc:sqlserver://"+ $this.ServerName + ".database.windows.net:1433;database=" + $this.DBName +";user=UserName@"+ $this.ServerName + ";password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
    }
    [string] OdbcConnectionString()
    {
      return "Driver={ODBC Driver 13 for SQL Server};Server=tcp:" + $this.ServerName + ".database.windows.net,1433;Database=" + $this.DBName +";Uid=UserName;Pwd={your_password_here};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
    }
    [string] PhpConnectionString()
    {
      $ConnString = [char]34 + "sqlsrv:server=tcp:" + $this.ServerName + ".database.windows.net,1433;Database=" + $this.DBName + [char]34
      $ConnString = $ConnString + "," + [char]34 + "UserName" + [char]34 + "," + [char]34 + "{your_password_here}" + [char]34
      return $ConnString
    }
}

Class ConnectionPolicy
{
    [String] $ServerName
    [String] $DBName
    [ConnectionStrings] $ConnectionStrings
    ConnectionPolicy ([String] $ServerName, [String] $DBName)
    {
        $this.ServerName = $ServerName
        $this.DBName = $DBName
        $this.ConnectionStrings = New-Object ConnectionStrings($ServerName,$DBName)
    }
}

 
Function Get-AzSqlDatabaseSecureConnectionPolicy
{
[CmdletBinding()]
param (
    [Parameter(Mandatory=$true, Position=0)]
    [string] $ServerName,
    [Parameter(Mandatory=$true, Position=1)]
    [string] $DbName )
     
 return New-Object ConnectionPolicy($ServerName,$DbName)
}

Now, running this example, we could have the same connection string that we have with the previous call 

$getConnStrings = Get-AzSqlDatabaseSecureConnectionPolicy -ServerName "MyServer" -DbName "MyDatabase"
$adonet = $getConnStrings.ConnectionStrings.AdoNetConnectionString()
$jdbc   = $getConnStrings.ConnectionStrings.JdbcConnectionString()
$odbc   = $getConnStrings.ConnectionStrings.OdbcConnectionString()
$php    = $getConnStrings.ConnectionStrings.PhpConnectionString()

Enjoy!