One of the ways I practice the principal of least privilege is by logging into my computer as a domain user who is also a standard local user. I typically run PowerShell as a domain user who is a local admin and elevate on a per command basis using a domain account with more access only when absolutely necessary. The problem I’ve run into is neither the account I’m logged into my computer as or the one I’m running PowerShell as has the ability to execute SQL queries that I need to run against various SQL servers in my environment.
I’ve installed both the SQLServer and BetterCredentials PowerShell modules from the PowerShell Gallery which are used thoughout this blog article. Windows PowerShell version 5.0 or higher (which ships by default with Windows 10) is required to run some of the commands shown in this blog article.
1 | Install-Module -Name SqlServer, BetterCredentials -AllowClobber |
I don’t want to run PowerShell with elevated domain credentials <period>. Unless I’m missing something, the Credential parameter for all of the various SQL cmdlets seems to want SQL authentication credentials, not Windows authentication ones.
At first, I tried kicking off another PowerShell process from within PowerShell as an elevated user to run the SQL commands from. The problem is the process started, ran, returned the results, and exited when completed.
1 2 3 4 | Start-Process -FilePath "$env:windir\System32\WindowsPowerShell\v1.0\powershell.exe" -ArgumentList " Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query ' select * from sys.databases' " -Credential (Get-Credential -UserName mikefrobbins\administrator -Store) |
Start-Process also has a Wait parameter so I tried it to see if it would cause the PowerShell process that’s spawned to wait until it was manually closed. No such luck as it also exited automatically when completed.
1 2 3 4 | Start-Process -FilePath "$env:windir\System32\WindowsPowerShell\v1.0\powershell.exe" -ArgumentList " Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query ' select * from sys.databases' " -Credential (Get-Credential -UserName mikefrobbins\administrator -Store) -Wait |
The only way to make this option work is to pipe the results to Out-GridView with its Wait parameter. This keeps the spawned PowerShell process open along with the grid view window until it’s manually closed.
1 2 3 4 5 | Start-Process -FilePath "$env:windir\System32\WindowsPowerShell\v1.0\powershell.exe" -ArgumentList " Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query ' select * from sys.databases' | Out-GridView -Wait " -Credential (Get-Credential -UserName mikefrobbins\administrator -Store) |
A better option is to simply run the command as a PowerShell job using the Start-Job cmdlet since Invoke-Sqlcmd doesn’t have an AsJob parameter.
1 2 3 4 | Start-Job -ScriptBlock { Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query ' select * from sys.databases' } -Credential (Get-Credential -UserName mikefrobbins\administrator -Store) |
Get-Job is used to determine the state of the job. As you can see in the following results, the job has completed.
1 | Get-Job -Name Job1 |
Receive-Job is used to return the results of the job. Note, the first time the Keep parameter is not specified, the results will be removed from the job.
1 | Get-Job -Name Job1 | Receive-Job -Keep | Format-Table |
Remove-Job is used to remove the actual job.
1 | Get-Job -Name Job1 | Remove-Job |
If there’s a different way to accomplish this task, I’d love to hear about it via a comment to this blog article.
µ
Hi,
What about starting a new console running as a different user and running there the Invoke-Sqlcmd cmdlet ?
I use the following:
[sourcecode language=”powershell”]
$c= Get-Credential
$HT = @{
FilePath = ‘C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe’;
Credential = $c
LoadUserProfile = $false;
WindowStyle = ‘Normal’;
ArgumentList = ‘ & { Start-Process -FilePath C:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe -Verb RunAs }’
}
Start-Process @HT
[/sourcecode]
So the first question runs “Why don’t you just start a fresh shell with the right AD user for SQL”, but the generic problem is you want to run two commands in the same shell (a) can only be run by user 1 and (b) can only be run by user 2 and they need to pass information to each other. Right ?
What you want there is an end point.
$mycreds = Get-Credential -Message “Creds for SQL”
Register-PSSessionConfiguration -Name “SQL” -RunAsCredential $myCreds -ShowSecurityDescriptorUI
Use the security descriptor UI to grant your (non-local admin) account rights to connect to the endpoint and use the creds.
$s = New-PSSession -ComputerName localhost -ConfigurationName SQL
Import-Module -PSSession $S -Name GETSQL
(or which ever SQL module you use)
How you have proxy commands in your session for comands which run in another session with different creds.