Allowing Excel privileges to SQL server agent [closed]

Question

I have a SSIS package deployed to SQL 2012. I can run the package if I do a right click and execute, but not if I run it through a SQL Server agent job. The SSIS package makes use of Excel interop to make some changes to the file and then save it. My guess is that SQL Agent account is not allowed to do such operations (Opening the excel application). I have created a proxy for admin and selected the package to be run as that proxy. This also does not work. Anyone knows how to achieve what I want? TYA

Answer 1

Try this:

Create the following folders:

C:\Windows\SysWOW64\config\systemprofile\Desktop
C:\Windows\System32\config\systemprofile\Desktop

Explanation:

Microsoft does not recommend to automate Excel or other Office applications via scripts, because Office applications are designed to be used by a human being, not a machine.

As a consequence, Excel is expecting a user profile to exist when the application is run. The SQL Server Agent service does not use an existing user profile (even when launched under a proxy user). When Excel is automated and launched via the agent, Excel is looking for that user profile information and the desktop folder.

The agent uses the systemprofile folder as its "user" directory.

For some reason, the automated Excel process will fail if the Desktop folder does not exist in the systemprofile folder. As a consequence, creating a desktop folder for both the 32bits and 64bits system profiles will solve your issue.

Answer 2

I had this problem with a SSIS Package using a Script Task that made use of Excel Interop; the package would execute in BIDS (as this was executed under my user account), yet when the same package was run as a SQL Agent job step it would throw an error (due to being executed under the SQL Agent account or 'LOCAL SYSTEM').

The error I received for this problem was:

System.UnauthorizedAccessException: Retrieving the COM class factory for component 
with CLSID {00024500-0000-0000-C000-000000000046} 
failed due to the following error: 80070005.

In order to fix this I created a Credential (SSMS > Security > Credentials) for a known user account (best to create a user account with minimal permissions for production purposes).

Then I created a Proxy (SSMS > SQL Server Agent > Proxies > SSIS Package Execution) which was mapped to the credential created above, ensuring that "SQL Server Integration Services Package" is selected under the 'Active to the following subsystems' list.

Finally, open up the properties of the agent job, select 'Steps', edit the step that executes the offending SSIS package and select the proxy that I just created under 'Run as:'.