Problems Calling PowerShell from SSIS via a Proxy Account

I ran into a weird problem recently which I couldn't find a solution for online, at all - although I did find plenty of other people suffering the same or similar problems.

My situation was a PowerShell script being called via an Execute Process Task in SSIS. This worked fine locally on the developer's PC, and worked fine when deployed to our old SSIS server and called from a SQL Server Agent Job, but after being migrated to the new SSIS server it was failing.

The difference was that on the old server, most jobs were run as the SQL Server Agent Account. When I set up the new server, I wanted to go with best practice - so I set up a proxy in the agent to run SSIS packages. This worked fine for every other package we migrated over.

The first error

The error being produced initially was AuthorizationManager check failed, so we had the sense that this was some kind of permissions issue. However, the script could be successfully run as the AD account being used by the proxy. I also found that if I set up another proxy using the AD account that we use as the agent service account, and ran the SSIS package as that proxy, the same error was produced.

At this point we came to the conclusion this was somehow related to impersonation. Scouring event viewer, I eventually realised that every time we ran the failing package, we were getting a WMI error related to that AD account not having remote enable privileges.

My fix

Sure enough, granting this did resolve the AuthorizationManager error - although read on, because we found another problem afterwards.

Here's how to grant the WMI priveleges:

  • Go to Computer Management, expand Services and Applications.
  • Right click on WMI Control and select Properties.
  • In the WMI Control Properties window that opens go to the Security tab, and expand Root. The namespace you need should be in the error - the default is CIMV2. Select the namespace and then press the Security button in the bottom right.
  • In the Security for ROOT\CIMV2 window that opens add the account you need, then select the relevant permissions - I gave my proxy account the same permissions held by Authenticated Users (Execute Methods, Provider Write, and Enable Account), and then also gave it Remote Enable.

WMI-Control

The second error

After doing this, our SSIS package was still failing - but with the error The term 'x' is not recognized as the name of a cmdlet, function, script file, or operable program. The script was calling a function from a built-in MS module, which was definitely available to the AD account in question.

My fix

My quick fix to this was to simply import the module within the script.


If anyone has any insight into what's causing these problems, or a better solution to either, I'd be happy to hear it. It seems like there's something not happening correctly when an account is being impersonated - but I'm not clear whether this is a problem with impersonation in general, a problem with SSIS, a problem with SQL Server Agent, or something specific to my environment. In the meantime, I hope this helps someone else get around this irritating problem.

Comments