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!