How can I connect to SQL through PowerShell using Windows authentication other than my local one?

Question

Here's my current code:

[string] $Server= "server"
[string] $Database = "database"
[string] $UserSqlQuery= $("SELECT * FROM [dbo].[User]")
[string] $UserID = "userid"
[string] $Pass = "pass"

$resultsDataTable = New-Object System.Data.DataTable
$resultsDataTable = ExecuteSqlQuery $Server $Database $UserSqlQuery $UserId $Pass

# executes a query and populates the $datatable with the data
function ExecuteSqlQuery ($Server, $Database, $SQLQuery, $UserID, $Pass) {
    $Datatable = New-Object System.Data.DataTable

    $Connection = New-Object System.Data.SQLClient.SQLConnection
    $Connection.ConnectionString = "server='$Server';database='$Database';trusted_connection=true;User ID = '$UserID';Password='$Pass';"
    $Connection.Open()
    $Command = New-Object System.Data.SQLClient.SQLCommand
    $Command.Connection = $Connection
    $Command.CommandText = $SQLQuery
    $Reader = $Command.ExecuteReader()
    $Datatable.Load($Reader)
    $Connection.Close()

    return $Datatable
}

#validate we got data
Write-Host ("The table contains: " + $resultsDataTable.Rows.Count + " rows")

So I realize I can replace UserID and Password with Integrated Security=true in order to use Windows authentication. The problem is I'm trying to use a Windows authentication other than my current one to get on SQL. Is there any way to do this? Thanks.

kragan · Accepted Answer · 2016-05-31 23:03:17Z

1

If the SQL connection string needs to specify Integrated Security then user impersonation will be needed. If you hold down the shift key and right click the powershell icon you can run the powershell process as another user by selecting "Run As" and entering the correct Windows credentials.

If the script must start running under one Windows user, but then impersonate a different Windows user for the SQL connection, then some additional scripting will be needed to setup that impersonation. Here's a link that may be useful in working that out - http://poshcode.org/1867

answered May 31, 2016 at 23:03

kragan

9995 silver badges8 bronze badges

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

2 Comments

Akash Masand Over a year ago

The link does not exist anymore. I am facing this issue currently and don't know how to login to sql server using windows authentication with PowerShell.

kragan Over a year ago

Here's a link to an archived version - web.archive.org/web/20170410101407/http://poshcode.org/1867

Collectives™ on Stack Overflow

How can I connect to SQL through PowerShell using Windows authentication other than my local one?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related