PowerShell "Untrusted Publisher" prompt for Trusted Publisher

70 Views Asked by At

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!

0

There are 0 best solutions below