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.

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.

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.

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.

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.

Get-Job is used to determine the state of the job. As you can see in the following results, the job has completed.

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.

Remove-Job is used to remove the actual 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.

µ

2 Comments

  1. _Emin_

    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]

    Reply
  2. jamesone111

    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.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: