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, Sep 05 '14 at 11:17
version of sql ... sql 2000 ... sql 2012 ... irrelevant but thanks for your comment — William Lee, Dec 30 '20 at 15:49
ssms just like powershell if i launch AS another user it works but only because of integration. — William Lee, Dec 30 '20 at 15:50
I know I can do some powershell stuff to run as another user but what I want to do is just pass in a active directory username / password. — William Lee, Dec 30 '20 at 15:51

score 37 · Answer 1 · answered Sep 05 '14 at 21:48

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

score 21 · Accepted Answer · edited Mar 28 '18 at 06:37

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 '18 at 06:37

Snake Eyes

16,287
34
113
221

answered Sep 05 '14 at 09:30

ojk

2,502
15
17

score 9 · Answer 3 · answered Mar 03 '15 at 05:39

# 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

score 3 · Answer 4 · edited Jan 26 '18 at 11:35

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 '18 at 11:35

wonea

4,783
17
86
139

answered Oct 03 '16 at 07:06

Arjun Walmiki

173
1
1
13

score 2 · Answer 5 · answered Sep 05 '14 at 09:35

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 05 '14 at 09:35

ne1410s

6,864
6
55
61

score 1 · Answer 6 · edited Sep 14 '21 at 13:21

1

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 '21 at 13:21

m4n0

29,823
27
76
89

answered Sep 14 '21 at 13:06

user16909280

11
1

score 0 · Answer 7 · answered Dec 30 '20 at 15:47

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 whatever@company.com ... basic username.

score -1 · Answer 8 · answered Dec 02 '21 at 16:54

-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 02 '21 at 16:54

Eashwer B. Iyer

1

Connect to SQL Server Database from PowerShell

8 Answers8

Linked