CODEWITHSUNDEEP
powershellazure-sql-database
0x51ba
0x51ba

Reputation: 463

Connecting to azure sql database using powershell

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?

Upvotes: 1

Views: 6437

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15608

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"

Upvotes: 1

Related Questions