Remote clients Can't open XLS file using ASP.NET/ADO

Question

I'm trying to do the following:

1. User goes to web page, uploads XLS file
2. use ADO .NET to open XLS file using JET engine connection to locally uploaded file on web server

This all works fine locally (my machine as the client and the web server) - and in fact is working on the customer's web server with remote clients but is not working when trying to test internally using a remote client.

The error I get is:

TIME:    [10/1/2008 11:15:28 AM]
SEVERITY:   EXCEPTION
PROGRAM:    Microsoft JET Database Engine
EXCEPTION:  Unspecified error
STACK TRACE:       at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
   at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.OleDb.OleDbConnection.Open()

The code generating the error is:

OleDbConnection l_DbConnection;
        OleDbDataAdapter l_DbCommand;
        DataSet l_dataSet = new DataSet();

        l_DbConnection = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; data source=\"" + l_importFileName + "\";Extended Properties=Excel 8.0;");
        l_DbCommand = new OleDbDataAdapter("select * from [Sheet1$]", l_DbConnection);

        //try using provider to read file
        try { l_DbConnection.Open(); }

The call to "Open" is raising the exception above.

The site is using impersonation and all calls are made as the user logged in on the client. What I've done so far to try and get this working:

Followed the steps here http://support.microsoft.com/kb/251254/ and assigned permissions to the TMP/TEMP environment variable directory to the user I am using to test (also assigned permissions to ASPNET and then to "Everyone" as a blanket "is this permissions related?" test).

Ensured that the file is being uploaded and the XLS file itself has inherited the directory permissions that allow the user full access to the file. I also gave this dir permissions to "Everyone" just in case - that also didn't help.

I haven't had to change any environment variables and have, therefore, not restarted after making these changes - but I shouldn't have to for Windows folder/file permissions to take effect.

At this point I'm at a total loss

Answer 1

Ok, figured it out -

turns out that even with IIS using impersonation and the TMP/TEMP environment variables being set to C:\WINDOWS\Temp the ASP.NET process is still running under the ASPNET account and each individual user needed permissions to the Documents and Settings\ASPNET\Local Settings\Temp folder

The other way around this would probably be to create a new app pool and have that app pool run as a user with permissions to the right folder rather than ASPNET

Answer 2

Go to the directory \Documents and Settings\"machineName"\ASPNET\Local Settings\Temp and give the read, write rights to the user "EveryOne" Then it will work fine. Moreover you have to set "" in web.config file