I have a SQL Agent job invoking SSIS package - Powershell script that behaves differently depending on whether the service account running the job as is logged on the remote server or logged off the server.
The Symptom
SQL Agent job runs as a proxy account associated with a service account.
Succeeding case:
When the service account is logged in the server(remote desktop), the SQL agent job succeeds and the script actually does the job.
Problematic case:
However when the service account is logged off the server(remote desktop), the SQL agent job succeeds (no error) but the script actually doesn't complete the job (or partially run, if you like) In the below script, the copying part is done, but uploading part is not done.
The Script
The powershell script runs perfectly fine when run manually on the server itself.
It copies files from a network drive and then load them into the on-prem Power BI reporting server.
The stripped down version of the script which replicates the issue is as below:
#Copying Files
$exe = "robocopy.exe"
$roboflags = "/NFL /NDL /NJH /NJS /nc /ns /np"
$destdir = "e:\import\spfiles"
$srcdir = '"\\xxxxxxxx"'
Remove-Item "e:\import\spfiles\*.*"
& $exe $srcdir $destdir
#Uploading File to the reporting server
$localfile = e:\import\spfiles\test.jpg
$rsfolder ="/Test Folder"
$session = New-RsRestSession -ReportPortalUri http://serverhostname/reports
Write-RsRestCatalogItem -WebSession $session -Path $localfile -RsFolder $rsfolder -Overwrite
The Service Account
The service account is AD based and has Administrator privileges in the server where the script runs (For the sake of investigation), and has the permissions required for a service account at the domain controller level.
- Log on as a service (SeServiceLogonRight)
- Replace a process-level token (SeAssignPrimaryTokenPrivilege)
- Bypass traverse checking(SeChangeNotifyPrivilege)
- Adjust memory quotas for a process(SeIncreaseQuotaPrivilege)
- Permission to start SQL Writer
- Permission to read the Event Log service
- Permission to read the Remote Procedure Call service
This service account (and the proxy account associated with it) works fine with most of our database related SQL agent jobs, but this particular job dealing with Reporting Server. So I assumed this could be something to do with Reporting server.
Can anyone advise on how to resolve this problem or even suggest some action items for further investigation?
I need to run this script as a scheduled job without having to log on to the server remotely.
