Powershell User Issue Through SQL Server Agent

Question

I am currently trying to run a Powershell script through SQL Server Agent, which completes its task without error if I run it through Powershell ISE on the desktop. The simple script is below (it's only being used for testing):

$test = "G:\test.txt"

if (Test-Path $testFile)
{
    Remove-Item $test
}

When I run this through SQL Server Agent, it produces a successful output - no errors whatsoever, but does show that it's being run as a different user in the job history log, for instance domain\localmachine, whereas when I run the script through Powershell ISE, it shows domain\you.

As a note, I can't confirm this manually because what I tried to do was run the below script both locally and through SQL Server Agent in a job to see the output, but the job failed (and thus why I suspect it's a user issue). Therefore, I'm trusting SQL Server Agent as to the domain\locallmachine is running the job (the reason it won't delete the file).

([Environment]::UserDomainName + "\" + [Environment]::UserName) | out-file pssaved.txt
"$env:userdomain\$env:username" | out-file -append pssaved.txt
[Security.Principal.WindowsIdentity]::GetCurrent().Name | out-file -append pssaved.txt

## Locally this produces domain\you
## On SQL Server Agent, I receive the error: The error information returned by PowerShell is: 'SQL Server PowerShell provider error: Path SQLSERVER:\pssaved.txt does not exist. Please specify a valid path.'

Is there a way, through SQL Server Agent to run a job as my domain user, for instance domain\you instead of the domain\localmachine (at least, this would eliminate this possibility of an error)?

You can use the -Credential parameter on remove-item, but that would involve storing a set of credentials in the procedure script. — mjolinor
– mjolinor, Commented Dec 19, 2013 at 1:40
Is there a typo here? The error text says SQLSERVER:\psaved.txt but your script specifies pssaved.txt. — Keith Hill
– Keith Hill, Commented Dec 19, 2013 at 3:05

Ben Thul · Accepted Answer · 2013-12-19 10:52:37Z

4

You can use a proxy for this. Check it out.

answered Dec 19, 2013 at 10:52

Ben Thul

32.9k4 gold badges52 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Question3CPO Over a year ago

This works for building a proxy (and this link helps: mssqltips.com/sqlservertip/1199/sql-server-agent-proxies) and it will execute as the appropriate account. Unfortunately, this still won't delete the local file, so still look for that issue.

Collectives™ on Stack Overflow

Powershell User Issue Through SQL Server Agent

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related