I was using office interop assemblies to do some customization on an excel report the process generates, I was able to do everything I wanted with the excel file on development machine and it worked just fine, but when I deployed to production I started getting the error “Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005 Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED)).” When I run the process through a SQL job, I used SSIS 2012 with the project deployment model but this applies to any process using the office interop assemblies.
Although the process was running fine when I login with the same user executing the job and execute it from visual studio I was still getting the error when it runs through a scheduled job, I had to spend a good time researching the issue and found the answer over few forums so I though to combine everything in one place.
Here is what I did to fix the issue, hopefully this works for you as well:
- Install Interop Assemblies: make sure either Microsoft office installed with .NET Programmability Support feature selected or install the assemblies directly from microsoft website.
- Assemblies Version: check the version of the assemblies on development and production machines, the assemblies will be in the GAC, in widows 7 this folder is %windir%\assembly. of course the versions should match.
- Desktop Folder: the service uses the desktop folder under systemprofile so you will need to create this folder if not there, here is the location of the folder:
- For 64 bit applications : C:\Windows\SysWOW64\config\systemprofile\Desktop
- For 32 bit applications : C:\Windows\System32\config\systemprofile\Desktop
- DCOM Running User and Permissions: you need to find the excel application entry in the component services console and set the security and identity for it:
- First if the user running the service is a domain user make sure it’s added as a user on the server machine.
- In the run window type dcomcnfg.
- Expand Component Services –> Computers –> My Computer –> DCOM Config.
- look for Microsoft Excel Application or CLSID {00024500-0000-0000-C000-000000000046}, if you can’t find it then it’s possible that you are running 32 bit office over 64 bit machine, look at the end of the post for steps to find it.
- right click the item and select properties, then select the Security tab.
- select Customize for Launch and Activation Permissions and Access Permission and add the user running the service then give it full access.
- go to the Identity tab and select This User option and add the user running the service then type it’s password.
- click Ok then restart your service.
this step should do it for you, if not you can try the next one.
- COM Security: you need to set the permission explicitly for the user running the process, here is how to do it:
- In the run window type dcomcnfg.
- expand Component services –> Computers then right click on My computer and select Properties.
- Select COM security tab and in the Launch and Activation permissions panel click Edit Defaults.
- add the user executing the service and give it Local Launch and Local Activation permissions.
- Quit Application: this might not be so much relative but make sure you release the instance of Excel.Application object in your code using the Quit() method, this way your process will not lock the file.
*If you couldn’t find the excel application in the DCOM config then try to find it in the 32 bit console as following:
- in the run window type mmc -32.
- go to file and select Add/Remove Snap in…
- Select component services and click Add.
- click OK then continue from point number 2 above.
I might have been very generous with the permissions here but I found out it has to do with the domain security policy and your server setup, so after you get this to work I suggest you go back and narrow down the permissions to the minimum that keeps you application running.
Waheed Rous 