Lesson Learned #167: Working with Get-AzSqlDatabaseSecureConnectionPolicy

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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!

REMEMBER: these articles are REPUBLISHED. Your best bet to get a reply is to follow the link at the top of the post to the ORIGINAL post! BUT you're more than welcome to start discussions here:

This site uses Akismet to reduce spam. Learn how your comment data is processed.