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.

Install-Module -Name SqlServer, BetterCredentials -AllowClobber

I don’t want to run PowerShell with elevated domain credentials . 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.

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.

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.

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.

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.

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.

Get-Job -Name Job1 | Receive-Job -Keep | Format-Table

Remove-Job is used to remove the actual job.

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.

µ