Connect to SQL Server Database from PowerShell

Question

I have looked around online for a while now and found many similar problems but for some reason I can't seem to get this working.

I am just trying to connect to a SQL server database and output the query results to a file - See PowerShell script below. What I am uncertain about is how to integrate the User ID and Password into the connection string.

$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 = True; 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" 

The following error message is received:

Exception calling "Fill" with "1" argument(s): "Windows logins are not supported in this version of SQL Server."

Answer 1

Integrated Security and User ID \ Password authentication are mutually exclusive. To connect to SQL Server as the user running the code, remove User ID and Password from your connection string:

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;" 

To connect with specific credentials, remove Integrated Security:

$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; User ID = $uid; Password = $pwd;" 

Answer 2

Change Integrated security to false in the connection string.

You can check/verify this by opening up the SQL management studio with the username/password you have and see if you can connect/open the database from there. NOTE! Could be a firewall issue as well.