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."
This question is related to
sql-server
powershell
connection-string
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;"
Assuming you can use integrated security, you can remove the user id and pass:
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
I did remove integrated security ... my goal is to log onto a sql server using a connection string WITH active directory username / password. When I do that it always fails. Does not matter the format ... sam company\user ... upn [email protected] ... basic username.
# database Intraction
$SQLServer = "YourServerName" #use Server\Instance for named SQL instances!
$SQLDBName = "YourDBName"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName;
User ID= YourUserID; Password= YourPassword"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = 'StoredProcName'
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
#End :database Intraction
clear
The answer are as below for Window authentication
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDBName;Integrated Security=True;"
Source: Stackoverflow.com