Ygthkb

I'm attempting to make a Powershell script that needs to interact with SQL Server, so part of the script will involve invoke-sqlcmd. However, the script can't be scheduled as a job in SQL Server and thus make use of the Agent to act as the Windows account used for authentication. Instead, it is going to be run using a different service under an arbitrary Windows account. The documentation for invoke-sqlcmd states that the -Username and -Password parameters are for SQL Server authentication, not Windows, and that it defaults to using the Windows account that is running the script for authentication if no SQL Server parameters are provided.

Is there any way to allow invoke-sqlcmd to use an arbitrary Windows account for authentication instead of the one it's being run under? I am aware that a possible solution would be to make accounts on the SQL Server instances that I want to run the script for and just use the SQL Server authentication option on the command, but I would ideally not want to have to do that. Some other research has also shown that a certificate could be an option to pursue, but I don't think that invoke-sqlcmd can take in certificates or otherwise utilize them like suggested. I could be wrong here though.

You could grant the account that is going to run the job the necessary permissions onto the SQL Server to run the query. That would be the easiest. If you are going to use Task Scheduler, it even gives a method for specifying the account to use.

The next best would be to use SQL Authentication.

Finally, you can use PowerShell to invoke a new thread to run under a different windows account, that thread then to use Invoke-sql. However, the password then needs to be stored somewhere somehow (usually encrypted in a file). This is not the best solution and not one that I would recommend using.

An alternative, I created a wrapper for Invoke-Sqlcmd for my libraries to use which makes use of Start-Job:

function Invoke-InternalSqlcmd {

    [CmdletBinding()]

    param (

        [Parameter(Mandatory=$true)][string] $Query,

        [Parameter(Mandatory=$true)][string] $Database,

        [Parameter(Mandatory=$false)][System.Management.Automation.PSCredential] $Credential

    )



    if (-not $Credential) {

        return Invoke-SqlCmd -Query $Query -Database $Database

    }

    else {

        $ScriptBlock = {

            param($Query,$Database) 

            $ErrorActionPreference = 'Stop';



            return @{ Result = Invoke-SqlCmd -Query $Query -Database $Database }

        }



        $Job = Start-Job -Credential $Credential -ScriptBlock $ScriptBlock -ArgumentList $Query,$Database

        $JobResult = Wait-Job $Job;



        if ($JobResult.State -eq 'Completed') {

            return (Receive-Job $Job).Result

        }



        throw [Exception]::new("Error occurred while executing sql as $($Credential.UserName) $([System.Environment]::NewLine)$($JobResult.ChildJobs[0].JobStateInfo.Reason)", $JobResult.ChildJobs[0].JobStateInfo.Reason)

    }

}

Pros

• Works!


• Does not use WinRM


• Returns the exact result that Invoke-Sqlcmd returns


• Returns errors

Cons

• Running PS jobs has a nasty overhead. Anywhere between 2-10 seconds. This might not be a big deal but it depends on how long the code you are executing will run.


• Doesn't respect ErrorActionPreference and will always throw on error. Can be fixed if needed.

I am running in a locked down environment that does not have access to WinRM, and my tasks are long running so this suits my needs. If you want better performance then you could easily replace the job code with Runspaces, as long as you are happy to use WinRM.