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?

can you try to hardcode your userid and password and check whether the error still orccurs? — Martin Brandl
– Martin Brandl, Commented Feb 26, 2018 at 9:56
@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
– 0x51ba, Commented Feb 26, 2018 at 10:45
@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 — 0x51ba
– 0x51ba, Commented Feb 26, 2018 at 10:53

Alberto Morillo · Accepted Answer · 2018-02-26 13:30:23Z

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"

answered Feb 26, 2018 at 13:30

Alberto Morillo

15.8k3 gold badges29 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

0x51ba Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

Connecting to azure sql database using powershell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related