SQL Agent can't run package with WMI Event Watcher Task

1

I created a SSIS package that watches for files being created on another server using a WMI Event Watcher Task. This package works fine when I run it in VS. It works fine when I manually Execute it from the Integration Services Catalog. When I try to run it as a scheduled SQL Agent Job I get Error: Watching for the Wql query caused the following system exception: "Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))". Check the query for errors or WMI connection for access rights/permissions.

My question is why does this not run as a SQL Agent Job when I can run it successfully several different ways? Based on the error code seems to be a COM/DCOM security issue.

So far I have:

  • Tried many web sites most are more or less like this https://docs.microsoft.com/en-us/windows/win32/wmisdk/wmi-troubleshooting?redirectedfrom=MSDN I have followed all the instructions on these websites with no change.
  • Gone back and gave even more permissions then specified by the website to the COM Limits and DCOM Config of the Windows Management and Instrumentation.
  • Have restarted the WMI service on the other server several times after each change.
  • Tried SA and myself as owner of the Job.
  • Tried my usual SQL Agent Proxy account and the default Server Agent Service Account (which is a Managed Service Account) as the Run As for the package.
  • Tried running as a 32 and 64 bit runtimes.
  • Have made the SQL Agent Proxy account and Server Agent Service Account Admins on the other server.
  • Tried hard coding my user name and password into the Connection Manager and used Windows Authentication.

* New tests *

  • Tested the SQL Agent Job using the File System as the package source instead of the SSIS Catalog, still failed with same error.
  • Tested running package using the Execute Package Utility and it worked!
sql-server
ssis
com
wmi
sql-agent
asked on Stack Overflow Oct 22, 2019 by Mrphin • edited Oct 23, 2019 by Mrphin

1 Answer

0

The AgentSqlServer user does not have rights to launch WMI Querys on server, You should create a proxy account and join it à to the step on the SQL Job.

answered on Stack Overflow Jan 22, 2020 by Kamel Jtita • edited Jan 22, 2020 by Dharman

User contributions licensed under CC BY-SA 3.0