In an earlier article I covered how to generate Excel reports on the fly using the Interop.Excel Namespace.
This is a really handy technique that also gives you full control over the minutia of the document you are creating. However the catch in using this technique is that you will need to update the configuration of your Web server to allow Web users to trigger Excel on your Web server. This can mean some pretty drastic security changes and possible loopholes in your Web server’s security so you should do a risk analysis before choosing this method.
In this article I will review the Web server security updates that need to be made to allow using the Interop.Excel Namespace to generate Excel documents for your Web site.
Please note that since I am developing this site for an Intranet, I am not as concerned with locking down the server. If you are working with a server that is exposed to the Web then you will want to review these security changes much more thoroughly.
What we will need to accomplish is to allow someone browsing our Web site to invoke Excel on the Web server so that the server can create and serve up the document.
At a more detailed level, the default ASP.NET worker process will need to be able to invoke the correct COM object on the server. Then, when the object has been invoked, the worker process will have to impersonate a local system User account on the server in order for Excel to allow the file to be created and saved to the Web server.
This requires significantly more configuration than a simple Web site setup in IIS, but may be worthwhile due to the extreme control that the Interop.Excel namespace gives you.
First Step: Install MS Office
The first thing you will need to do is to make sure that MS Office is installed on your Web server. The standard Interop.Excel invocations refer to MS Office components that must be running on the server and from my testing requires a complete installation of MS Office.
In my system setup I was dealing with a Windows 2003 server running IIS6 and pretty much nothing else, so I had to get my hands on MS Office 2007 to install on the server. Installing it went like clockwork so I was quickly on to the next step.
Step Two: Add your Web Application to IIS
So for the second step I added my Web application to IIS. I won’t go into the details of setting up a site in IIS, but I should mention some of the settings I ended up using.
- I left the Execute Permissions option set as Scripts
- In directory security I disabled Anonymous access
- I enabled Integrated Windows Authentication
Step 3: Try running your site
Now to check that everything was working I opened up a browser and pointed it to my new site. As expected the site ran quite nicely. However when I tried generating the Excel document I would click the Web page button to do so and I would either see an error on the page, or else the page would just end up doing nothing.
Step 4: Check the Server Event Logs
Whichever behaviour you end up seeing, go to your Administrative Tools and check your server’s Event Viewer. Then click to view your System logs. You should see an error with EventId: 10016 for the user account NT AUTHORITY\NETWORK SERVICE. This should originate from the source: DCOM. The details of the error should indicate that there is a permission settings issue for getting local activation to a COM Server application.
Step 5: Grant Permission to the Network Service Account
Please note that these steps will vary depending on the version of Windows that your server is running. The steps in Windows Server 2003 are quite straightforward, but I believe that in later versions this may be more onerous. Here are the steps I took in Windows Server 2003:
- On the Start menu, click Run and then type dcomcnfg and click ok
- the Component Services management tool will appear
- expand the Component Services node under Console Root. Then expand the Computers and subsequently the My Computer sub-nodes.
- Right-click the My Computer node and click Properties
- In the Properties popup click the COM Security tab
- In the Launch and Activation Permissions section click the Edit Default button
- Under the list of user accounts with launch permissions, you will want to add the default ASP.NET account Network Service.
- To do so, click the add button. then type network under the object names to search box and change the location to the local server’s name.
- Then click the Check Names button. Browse through the list of names until you see Network Service
- Select Network Service and click OK
- Click the next OK button also and then update the checkbox options for the Network Service account. In my case I set everything to allow, but you may be able to further refine this.
- Finally click OK to save your changes and then exit the Component Services interface.
Checking the Impact of your Changes
At this point if you test your Export to Excel functionality you will not see any errors, but the document will also not generate. If you go to your Server’s Event Viewer and click on Microsoft Office Sessions you will see notifications generated each time you try to generate an Excel document from your Web site.
The notifications indicate that your ASP.NET service is activating the COM object it needs, but that it can’t go any further due to permission issues. This is related to user accounts on the server. Basically the process trying to invoke and save your Excel documents is not running with sufficient permissions on your Web Server to do so. To correct this you will need to create a new user account on your Web server, preferably with minimal permissions so as not to endanger your server too much.
Step 6: Setting up your new User Account
- Click on your Start menu and then on Administrative Tools and then Computer Management.
- In the Computer Management tool expand the System Tools node and then the Local Users and Groups node.
- Click on the Users folder to view the list of local system users
- Right Click on the Users pane and select New User
- Add a new user account and password. Make sure you uncheck the User must change password at next logon option. and set the password to never expire. Finally click the Create button to create your new user.
- Now click the Groups folder and locate the Guests group in the list of groups.
- Right Click the Guests group and select the Add to Group option.
- In the Guests Properties popup window click the Add button to add your new user to this group. Once you have done so click the OK button to save your changes.
At this point you have your new limited access user account to use for generating Excel documents. If your server has been properly configured, then the Guests group will have severely limited access, but it should still be enough to generate and save new documents.
Step 7: Telling ASP.NET to Use as your New Server Account
The final step of this process is to tell ASP.NET that it should run as the new local server account that you have set up. Thankfully this is quite simple to do:
- Open up your site’s Web.config file
- Browse to the system.web identity element in your Web.config
- Set impersonate to true and add the user name and password to your new Server account.
So to summarize, change the following identity tag:
<authentication mode="Windows"/> <identity impersonate="false"/>
<authentication mode="Windows"/> <identity impersonate="true" userName="testUser" password="testUser"/>
At this point you have all of the parts in place to allow your ASP.NET site to instantiate the Excel COM components it needs via a DCOM Web request.
Once instantiated, your Web.config file is set up to impersonate a limited permissions user account that it can use to create the necessary Excel file to serve up your Web site’s users.