Connecting to azure sql database using powershell

Question

I have a powershell script in which I am trying to establish a connection to an azure sql db. When Building my connection string in the following way, the creation of the SqlConnection object always fails with an error stating that the format of the connection string does not conform to specification (the variable cred is of type "PSCredential").

 $connString = "Server=$($serverName).database.windows.net;"
 $connString = $connString + "Database=$($dbName);"
 $connString = $connString + "Integrated Security=False;"
 $connString = $connString + "User ID=$($cred.Username)@$($serverName);"
 $connString = $connString + "Password=$($cred.GetNetworkCredential().Password);"
 $connString = $connString + "Trusted_Connection=False;"
 $connString = $connString + "Encrypt=True;"
 $connString = $connString + "Connection Timeout=30;"
 $SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection($connString)

Anyone got an idea what goes wrong here?

• Martin Brandl: can you try to hardcode your userid and password and check whether the error still orccurs?
• gvee: You need to inspect the result of Write-Host $connString.
• 0x51ba: @MartinBrandl With hard coded values everything goes fine
• 0x51ba: @gvee I have already checked out the $connstring and everything looks fine to me. I guess something goes wrong while replacing the values of the variables in the connection string.
• 0x51ba: @gvee The content looks correct, I am not sure about the format of the resulting string and whether it contains characters which SqlConnection can not deal with

Answer 1

Try the following:

$SQLServer = "aaaa.database.windows.net"
$SQLDBName = "Database"
$uid ="john"
$pwd = "pwd123"
$SqlQuery = "SELECT * from table;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = False; User ID = $uid; Password = $pwd;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$DataSet.Tables[0] | out-file "C:\Scripts\xxxx.csv"

• 0x51ba: Despite the missing TrustedConnection is there any difference between your connection string and mine? Having my connection string above, as soon as i try to assign the connection string to the SqlConnection.ConnectionString variable i get the format error. BTW I prefer to use encrypted passwords and no hard coded password strings in my script.
• 0x51ba: It turned out that the reason for the exception was a control character which was built into the connection string during its construction with powershell. I was able to see the control character after I had copied the string from powershell console and pasted it in a text editor for debugging purposes. Having a look on the implementation of the connection string parser on github, one can see that the parsing state machine makes use of Char.IsControl in some cases and throws an exception if finding one.