4

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.

  • 1
    Check this answer for one possible way to use Impersonation to log onto a remote server. – Max Vernon Dec 30 '15 at 21:07
  • I use that method all the time to connect my SSMS on my local machine to my client's SQL Server. Great for "authenticating". Still relies on keeping a password in an encrypted file/store or in plain text somewhere if you want to use it in an automated fashion. – Jonathan Fite Dec 30 '15 at 21:12
  • I'll investigate that as well as a possible solution. Thank you. – Dylan Bacon Dec 30 '15 at 21:14
  • @DylanBacon, I wouldn't go down that route, if you are going to launch a new thread with a different domain credential you can already do that without using the NETONLY methods, it just adds complexity. I find it useful when I'm crossing domains that don't trust each other (as from my company laptop to a customer's SQL Server). – Jonathan Fite Dec 30 '15 at 21:17
  • 1
    FYI, this is the command. runas /user:DOMAIN\Account /netonly "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\ssms.exe" it then prompts for the password for the account you specified. – Jonathan Fite Dec 30 '15 at 21:21
3

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.

| improve this answer | |
  • I should have specified that I'm in an enterprise environment where 'permissions' are a carefully guarded and taped thing, but I will agree that it would be the easiest solution if possible. – Dylan Bacon Dec 30 '15 at 21:05
  • Unfortunately, there isn't much wriggle room on this. The thread has to somehow get authenticated as the desired windows account and there are only a few ways to do that, and most don't store the password securely. Is there a reason they don't want to run the job from SQL Agent? You could setup proxy accounts fairly easily to limit permissions. – Jonathan Fite Dec 30 '15 at 21:08
  • Hah, more along the lines of 'red tape'. Worries about SQL Server account vulnerabilities are the main reason we are trying to avoid them, I believe. But if forcing a form of Windows authentication is circumventing security in other ways (As most solutions seem to need), then the SQL authentication is probably the lesser of evils. Either way, this answer offers suggestions enough that I'll accept it. – Dylan Bacon Dec 30 '15 at 21:12
  • 1
    Probably best to have a windows account created for this task and then have the task scheduler run the job using those credentials. Then give that windows account just the permissions it needs to get the job done. It's a bit of a pain but probably fits in their security review models better than turning on SQL authentication if they are already against it. – Jonathan Fite Dec 30 '15 at 21:19
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.