Run SQL Server PowerShell Cmdlets as a Different User
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.
1Install-Module -Name SqlServer, BetterCredentials -AllowClobber
I don't want to run PowerShell with elevated domain credentials 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.
1Start-Process -FilePath "$env:windir\System32\WindowsPowerShell\v1.0\powershell.exe" -ArgumentList "
2 Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query '
3 select * from sys.databases'
4" -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.
1Start-Process -FilePath "$env:windir\System32\WindowsPowerShell\v1.0\powershell.exe" -ArgumentList "
2 Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query '
3 select * from sys.databases'
4" -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.
1Start-Process -FilePath "$env:windir\System32\WindowsPowerShell\v1.0\powershell.exe" -ArgumentList "
2 Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query '
3 select * from sys.databases' |
4 Out-GridView -Wait
5" -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.
1Start-Job -ScriptBlock {
2 Invoke-Sqlcmd -ServerInstance sql16 -Database master -Query '
3 select * from sys.databases'
4} -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.
1Get-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.
1Get-Job -Name Job1 | Receive-Job -Keep | Format-Table
Remove-Job is used to remove the actual job.
1Get-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.
µ