CODEWITHSUNDEEP
sqlpowershell
Apollo4
Apollo4

Reputation: 23

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

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.

Upvotes: 1

Views: 9795

Answers (1)

kragan
kragan

Reputation: 999

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

Upvotes: 1

Related Questions