Excel InterOp/Automation Issues

| By: Admin

I recently faced a lot of issues when trying to perform Excel automation on the server where I wanted to create Excel files, read\write data, through code. In the end, I figured out that it is an InterOp permission issue that is preventing my SQL Server package to write to/read from the Excel file.

Here is how I resolved that issue;

  • Start –> dcomcnfg.exe
  • Select Computers –> My Computer –> DCOM Config
  • Click “Microsoft Excel Application”. Right Click –> Properties
  • Choose the “Identity” tab. Select “Interactive User”.
    If you are running your application under any special account that you created, select “This user” option and provide user id / password;

  • Choose the “Security” Tab. In the first section labeled “Launch and Activation Permissions”, click “Customize”. Edit and add permissions to the user whom you are running the SQL package under.
  • In the second section labeled “Access Permissions”, select “Customize”. Edit and add permissions to the user whom you are running the SQL package under. 
  • In the final section labeled “Configuration Permissions”, select “Customize”.
    Edit and add permissions to the user whom you are running the SQL package under.
  • If you want everyone to have permission, you can select “Everyone” in above 2 steps. Please note that selecting “Everyone” is not a good practice.