I'm having an issue with PowerShell steps in a SQL Agent job - specifically:
Problem
When running a SQL Agent job with a PowerShell step using a Proxy/Credential, the job throws an "AuthorizationManager check failed" exception.
Detail
The entirety of the exception is as follows:
A job step received an error at line 1 in a PowerShell script.
The corresponding line is 'import-module SQLPS -Version 15.0'. Correct the script and reschedule the job.
The error information returned by PowerShell is: 'The following error occurred while loading the extended type data file:
Microsoft.PowerShell, G:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\sqlprovider.types.ps1xml: The file was skipped because of the following validation exception: AuthorizationManager check failed..`
I can reproduce this error by running SQLPS.exe in a command prompt running as the Proxy Account:
PS Cert:\CurrentUser> sqlps
Microsoft (R) SQL Server (R) PowerShell
Version 15.0.2000.5
Copyright (c) 2019 Microsoft. All rights reserved.
Do you want to run software from this untrusted publisher?
File G:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\sqlprovider.format.ps1xml is published by CN=Microsoft Corporation, OU=MOPR, O=Microsoft Corporation, L=Redmond, S=Washington, C=US and is not trusted on your system. Only run scripts from trusted publishers.
[V] Never run [D] Do not run [R] Run once [A] Always run [?] Help (default is "D"):
As you can see, the module and code signing cert in question are components of SQL Server itself.
For reference, the ExecutionPolicy is AllSigned.
Troubleshooting
First step is to check the signature of that ps1xml file:
PS Cert:\CurrentUser\> Get-AuthenticodeSignature "G:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\sqlprovider.types.ps1xml"
Directory: G:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS
SignerCertificate Status Path
----------------- ------ ----
BC0B6D0D7398035FCFBE8CC1AD8724A23A3A89DB Valid sqlprovider.types.ps1xml
When I browse certs via MMC - the thumbprint corresponds to a Microsoft Code Signing cert - which is expired, but my understanding is that this will only prevent signing, not execution. So I take it a step further and directly compare to the cert store in PowerShell
PS Cert:\CurrentUser\> Get-ChildItem -Path "Cert:\*$((Get-AuthenticodeSignature 'G:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\SQLPS\sqlprovider.types.ps1xml').SignerCertificate.Thumbprint)" -Recurse
PSParentPath: Microsoft.PowerShell.Security\Certificate::CurrentUser\TrustedPublisher
Thumbprint Subject
---------- -------
BC0B6D0D7398035FCFBE8CC1AD8724A23A3A89DB CN=Microsoft Corporation, OU=AOC, O=Microsoft Corporation, L=Redmond, S=Wa...
PSParentPath: Microsoft.PowerShell.Security\Certificate::LocalMachine\TrustedPublisher
Thumbprint Subject
---------- -------
BC0B6D0D7398035FCFBE8CC1AD8724A23A3A89DB CN=Microsoft Corporation, OU=AOC, O=Microsoft Corporation, L=Redmond, S=Wa...`
And this demonstrates that the cert is in both the current user (Proxy account) and local machine TrustedPublisher stores.
Questions
- Why I getting prompted to allow this publisher when a trust ostensibly already exists?
- Does PowerShell maintain some intermediary list of trusts?
- If this prompt is by design, is there a way to indicate an "Always Run" preference non-interactively?
Any assistance or insight would be greatly appreciated!