Execution Powershell in SQL Server Agent as a user

Question

Hello everybody,

I am trying to execute a PowerShell Skript using SQL Server Agent, which imports a Module (ReportingServicesTools).
There seems to be some permission issue and I am not sure whether the cause is in SQL Server Agent, or PowerShell.

To simply everything I set up a clean VM running Windows Server 2022 Standard, SQL Server 2022 Developer.
Then I installed the Module using Windows Powershell 5.1.

This is my PowerShell Code, I have saved to C:\test\test.ps1:

if (-not (Get-Module ReportingServicesTools)) { Import-Module ReportingServicesTools -WarningAction SilentlyContinue -DisableNameChecking }
Connect-RsReportServer -ComputerName localhost -ReportServerInstance MSSQLSERVER
$ReportServerUri = "http://localhost/reportserver"
$catalogItems = Get-RsCatalogItems -RsFolder / -Recurse -ReportServerUri $ReportServerUri

It works fine under these conditions:

• Execute locally from PowerShell 5.1 command line (local Admin or normal user)
• Execute from SQL Server (using SQL Server Agent job step) running under SQL Server Agent Account or a local Administrator as a proxy account

It does not work in this case:

• Execute from SQL Server (using SQL Server Agent job step) running under local user as a proxy account

Since we do not want our scripts to run as admin to follow the principle of least privilege, I like to find out, what we not do to get a PowerShell Skript running as a local user or AD Service account.

My error message is (partly in German):

Ausgeführt als Benutzer: ''WIN-FAAUJQ8NNM0\Mark''. Der Typ [Microsoft.ReportingServicesTools.SqlServerVersion] wurde nicht gefunden. In C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.7.3\Functions\Utilities\Connect-RsReportServer.ps1:67 Zeichen:9 + [Microsoft.ReportingServicesTools.SqlServerVersion] + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (Microsoft.Repor...qlServerVersion:TypeName) [], RuntimeException + FullyQualifiedErrorId : TypeNotFound Der Typ [Microsoft.ReportingServicesTools.ConnectionHost] wurde nicht gefunden. In C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.7.3\Functions\Utilities\New-RsWebServiceProxy.ps1:62 Zeichen:24 + ... edential = ([Microsoft.ReportingServicesTools.ConnectionHost]::Creden ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (Microsoft.Repor....ConnectionHost:TypeName) [], RuntimeException + FullyQualifiedErrorId : TypeNotFound Es ist nicht möglich, eine Methode für einen Ausdruck aufzurufen, der den NULL hat. In C:\Program Files\WindowsPowerShell\Modules\ReportingServicesTools\0.0.7.3\Functions\CatalogItems\Get-RsFolderContent.ps1:85 Zeichen:17 + $Proxy.ListChildren($Item, $Recurse) + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : InvokeMethodOnNull. Prozessexitcode 1. Fehler bei Schritt.

So it seems the user cannot read the library.ps1 file from the ReportingServicesTools module, although all users have read and execute permissions in C:\Program Files\WindowsPowerShell\Modules.
There must be something else.

Anyone any idea?

I would be glad for any help :)

Mark

Answer 1

Hello everyone,

meanwhile we found a solution for this.
The cause for the TypeNotFound-Exception is, that PowerShell can't compile the ReportingServicesTools-Module under SQLServerAgent-context, since it overrides the TMP&TEMP environment variables to C:\Windows\Temp

The solution is to chance in PowerShell in the first lines of code these variables to a folder, where the local Windows user, used as a proxy user, can read, write & execute.

In my case I have added just these two lines:

$Env:TEMP="C:\SomeFolder"
$Env:TMP="C:\SomeFolder"

Thanks for all your hints and help,
Mark

Answer 2

Hallo @Kramomat

I am not sure, but usually, the PoSh command line, which is started by the SQL Agent, is not 100% the same (at least not the same version) as you run from cmd (or START)...

So I recommend reading this documentation as it might lead you to a solution... (you haven't posted a screenshot from your job step)

You have to use a cmdexec step and not the PowerShell step and then start the correct version.

https://learn.microsoft.com/en-us/sql/powershell/run-windows-powershell-steps-in-sql-server-agent?view=sql-server-ver16#CmdExecJob

Answer 3

It does not work in this case:

• Execute from SQL Server (using SQL Server Agent job step) running under local user as a proxy account

Here is what I would try....

RDP to the server and open an admin command prompt. Use the runas.exe program to launch Powershell as the proxy account. Try running the script from there.

Also check the system path and PS module path environment variable to see if the proxy account has something different from an admin account.

($env:path).split(";")  
($env:PSModulePath).split(";")  

For "strange problems", my goto tool is Process Monitor.

https://learn.microsoft.com/en-us/sysinternals/downloads/procmon

This traces all file/network/registry access. Look for "access denied" and repeated "not found" errors in the Result column.