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."

SQL Server Management Studio 2012 isn't a "version of SQL" - it's an application that can connect to and manage SQL Server, and it can connect to any version of SQL Server from 2000 (maybe older) through 2014. — alroc
– alroc, Commented Sep 5, 2014 at 11:17
version of sql ... sql 2000 ... sql 2012 ... irrelevant but thanks for your comment — William Lee
– William Lee, Commented Dec 30, 2020 at 15:49

Aaron Jensen · Accepted Answer · 2014-09-05 21:48:26Z

40

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;"

answered Sep 5, 2014 at 21:48

Aaron Jensen

27.2k18 gold badges85 silver badges93 bronze badges

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

Comments

Snake Eyes · Accepted Answer · 2018-03-28 06:37:47Z

21

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.

edited Mar 28, 2018 at 6:37

Snake Eyes

16.8k39 gold badges120 silver badges235 bronze badges

answered Sep 5, 2014 at 9:30

ojk

2,54218 silver badges17 bronze badges

Comments

Shad · Accepted Answer · 2023-10-19 04:53:32Z

10

# Database Interaction

$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 Interaction
clear

edited Oct 19, 2023 at 4:53

Shad

4734 silver badges9 bronze badges

answered Mar 3, 2015 at 5:39

Mike Clark

1,88015 silver badges22 bronze badges

Comments

wonea · Accepted Answer · 2018-01-26 11:35:31Z

3

The answer are as below for Window authentication

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=$SQLServer;Database=$SQLDBName;Integrated Security=True;"

edited Jan 26, 2018 at 11:35

wonea

4,99917 gold badges91 silver badges144 bronze badges

answered Oct 3, 2016 at 7:06

Arjun Dhilod

1811 gold badge2 silver badges15 bronze badges

Comments

ne1410s · Accepted Answer · 2014-09-05 09:35:41Z

2

Assuming you can use integrated security, you can remove the user id and pass:

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

answered Sep 5, 2014 at 9:35

ne1410s

7,1126 gold badges60 silver badges63 bronze badges

Comments

m4n0 · Accepted Answer · 2021-09-14 13:21:13Z

0

To connect to SQL Server as an active directory user just start the PowerShell as an active directory user and connect to SQL Server with TrustedSecurity=true

edited Sep 14, 2021 at 13:21

m4n0

32.6k28 gold badges81 silver badges98 bronze badges

answered Sep 14, 2021 at 13:06

user16909280

91 bronze badge

1 Comment

TylerH Over a year ago

TrustedSecurity is not a supported keyword for SqlConnection.ConnectionString

Eashwer B. Iyer · Accepted Answer · 2021-12-02 16:54:34Z

-1

I think that may work only if the specific user in question is explicitly set up to log in with a password on the SQL server database i.e. without inheriting credentials from integrated Windows / single-sign-on

answered Dec 2, 2021 at 16:54

Eashwer B. Iyer

1

Collectives™ on Stack Overflow

Connect to SQL Server Database from PowerShell

7 Answers 7

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related