0

I've found plenty of information on how to connect to SQL Server with PowerShell using either the currently logged in user with Windows authentication, or using a SQL Server account to connect with other credentials. I'm wondering if there's a way to connect with Windows authentication but impersonating another Windows user.

Here are the ways I have found it possible to use to connect to a SQL Server database using either Windows authentication or SQL Server authentication:

Windows authentication using .Net:

$Connection = New-Object System.Data.SqlClient.SQLConnection
$Connection.ConnectionString = "Data Source=$SQLServer;Integrated Security=$true;Initial Catalog=$Database"
$Connection.Open()

SQL Server authentication using .Net:

$CredentialObject = New-Object System.Management.Automation.PSCredential -ArgumentList $SQLUsername,$SQLPassword
$CredentialObject.Password.MakeReadOnly()
$SQLCredential = New-Object System.Data.SqlClient.SqlCredential($CredentialObject.UserName,$CredentialObject.Password)

$Connection = New-Object System.Data.SqlClient.SQLConnection
$Connection.ConnectionString = "Data Source=$SQLServer;Initial Catalog=$Database"
$Connection.Credential = $SQLCredential

Windows authentication using SqlServer module:

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -Query "SELECT....."

SQL Server authentication using SqlServer module:

Invoke-Sqlcmd -ServerInstance $SQLServer -Database $Database -Query "SELECT....." -Username $SQLUsername -Password $SQLPassword

I've noticed that the -Credential flag exists for Invoke-Sqlcmd but according to this post that too assumes SQL Server authentication and cannot use Windows authentication.

Are there any other options available if I want to impersonate a user using Windows authentication?

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tanaka Saito
  • 943
  • 1
  • 17
  • 40
  • I don't know to do directly what you want. If this is an automated / background process, you can set the process to run as a different user. If it is an interactive process it might be easier to just RunAs the powershell console. You could also try using the "Windows Credentials" applet to tie specific windows credentials to a SQL Server – Nick.Mc Nov 09 '21 at 06:12
  • 1
    Does this answer your question? [Run code block locally as a different user in powershell script](https://stackoverflow.com/questions/25600870/run-code-block-locally-as-a-different-user-in-powershell-script) – Charlieface Nov 09 '21 at 06:13
  • Oh yeah, using Invoke-Command but specifying the local server? I'll have to try that, let me get back to you! – Tanaka Saito Nov 09 '21 at 06:25
  • I haven't forgotten this, my testing environment broke and I'm rebuilding it :( will get back as soon as I have tried it – Tanaka Saito Nov 10 '21 at 05:04
  • Update: this seems to at least run in the correct user context, but I don't understand how I can retrieve the output from the command. As in, I can't see if it failed or succeeded as it runs in a different process. If I run Start-Process with different credentials and -ArgumentList "-Command","whoami" or if I deliberately misspell it like "-Command","whoamiiii" the process still runs. If I add some Start-Sleep before and after I can see the output in the new PS window, but can I redirect that back to the original powershell instance that called the new one? – Tanaka Saito Nov 15 '21 at 23:45

1 Answers1

0

The suggested post does seem to run it correctly under a different user context, but it does not work for SQL queries for some reason, most likely because the commands contain single quotation marks '. When using anything more complex than SELECT * FROM dbo.MyTable it would not run properly, for example when you run WHERE MyColumn LIKE '%filter%'. Even when it ran correctly, it didn't produce any output, as in Start-Process wouldn't tell me what failed, if anything at all. I needed a way to capture the StandardOutput and StandardError.

The solution I found was this post which allowed me to capture the output using System.Diagnostics.ProcessStartInfo. But, I also needed to run it in a different user context, which is possible with .UserName, .Domain, and .Password. The final code looks like this:

$ProcessStartInfo = New-Object System.Diagnostics.ProcessStartInfo
$ProcessStartInfo.CreateNoWindow = $true
$ProcessStartInfo.UseShellExecute = $false
$ProcessStartInfo.RedirectStandardOutput = $true
$ProcessStartInfo.RedirectStandardError = $true
$ProcessStartInfo.FileName = 'powershell.exe'
$ProcessStartInfo.Arguments = @("-Command",$MyCommand)
$ProcessStartInfo.UserName = $Username
$ProcessStartInfo.Domain = $Domain
$ProcessStartInfo.Password = $Password
$Process = New-Object System.Diagnostics.Process
$Process.StartInfo = $ProcessStartInfo
[void]$Process.Start()
$StandardOutput = $Process.StandardOutput.ReadToEnd()
$StandardError = $Process.StandardError.ReadToEnd()
$Process.WaitForExit()

if ($Process.ExitCode -eq 1) {

    throw $StandardError

}
else {

    Write-Output $StandardOutput

}

$MyCommand is the full command I need to run which looks like this:

$SQLCommand = $SQLCommand -replace "'","''"

$MyCommand = '
$Connection = New-Object System.Data.SqlClient.SQLConnection
$Connection.ConnectionString = ''Data Source=' + $SQLServer + ';Integrated Security=' + $true + ';Initial Catalog=' + $Database + '''

$Command = New-Object System.Data.SqlClient.SqlCommand(''' + $SQLCommand + ''',$Connection)
$Connection.Open()

$Adapter = New-Object System.Data.SqlClient.SqlDataAdapter $Command
$Dataset = New-Object System.Data.DataSet
$Adapter.Fill($Dataset) | Out-Null

$Connection.Close()
$Dataset.Tables'

The $SQLCommand is the SQL query that I will run against the database, and I do a -replace for all single quotation marks to add two to them, since there's two steps in which this query is passed, first to the $MyCommand and then in the actual process when it runs.

I am fully aware that this opens up a host of different SQL injection problems but that is a security risk we have accepted. Your mileage may vary.

Tanaka Saito
  • 943
  • 1
  • 17
  • 40