Reading Excel File using ASP problem only IIS

[msghaleb]

Hi all,

I've created an asp page which is reading an Excel file, the code is working ok only not on IIS !!

here is the code:

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _
"DriverId=790;" & _
"Dbq=C:\inetpub\wwwroot\ads\NOS_Holidays_2007.xls; " & _
"DefaultDir=C:\inetpub\wwwroot\ads"

It's working perfectly fine on "Abyss Web Server" but not on IIS6 or IIS7

I'm getting the following Error on the IE

HTTP 500 (when using friendly Errors)

OR

An error occurred on the server when processing the URL. Please contact the system administrator

I think there is something wrong between the Excel Driver and IIS.

I would be thankful if you can help me finding what's wrong.

Thanks.

[msghaleb]

Hi Here is more information:

I have the same problem in reading ACCESS database as well, also only with IIS.

The Error is details is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Excel Driver] Disk or network error.

/ads/update_holidays.asp, line 11

here is line 11:

oConn.Open "Driver={Microsoft Excel Driver (*.xls)};" & _

Thanks in Advance

[jhardman]

Try this slightly different syntax:

File = "ads\NOS_Holidays_2007.xls"
oConn.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source="&_
   server.mapPath(File)&"; Extended Properties=Excel 8.0;"
 

You also might want to try connecting with a DSN, sometimes this seems like a big hassle to me, but it does take out some of the guesswork.

Jared

[msghaleb]

Hi Jared,

Thanks for the reply, no luck :( now I got the following Error:

Microsoft JET Database Engine error '80004005'

Unspecified error

/ads/update_holidays.asp, line 12

here is line 12

12 oConn.Open "Provider = Microsoft.Jet.OLEDB.4.0;Data Source="&_
13 server.mapPath(File)&"; Extended Properties=Excel 8.0;"

I rolled back the changes, but is there is anything needed to prepare IIS to work with mdb and xls files? becuaes it's working ok with other webservers on the same PC.

Also I would be thankful if you can give me some links about the other method.

Thanks in Advance.

[msghaleb]

Hi here is the solution which I found, thanks to Bill - it works for me regardless the IIS vr.

loadUserProfile and IIS7 - understanding temporary directory failures
Posted Oct 18 2006, 08:47 AM by bills
Filed under: IIS7, Troubleshooting, Compatibility
I've noticed quite a few people running into temporary directory permission issues. The PHP blog post I made last month is one example of this issue, there are also several forum posts related to ASP & Access database failures, where the reported failure is:

Microsoft JET Database Engine error '80004005'
Unspecified error

Not a very helpful error, to be sure. It turns out all of these cases have a similar underlying cause: the effective user of the web application (including the anonymous user for unauthenticated requests) can not effectively write to the temporary directory. What changed in IIS7 and why did this always work before?

With IIS6, all worker processes, regardless of which the process identity was configured, used to C:\windows\temp as the temporary directory. More specifically, none of the worker processes loaded their 'user profile' by default, causing all of them to use c:\windows\temp as a temporary directory. Windows allows all users read/write/creator privledges on this directory, which allowed things to 'just work'. The negative side effect of this is that all AppPools are effectively sharing the same temporary directory by default, which could lead to cross-appPool information disclosure.

With IIS7, we've chosen a more secure default and now load user profile by default for all application pools. Unfortunately, the temporary directory underneath the user directory (for example - %windir%\serviceprofiles\networkservice\AppData\Lo cal\Temp for the default NetworkService identity we use for DefaultAppPool) is not writable by anyone other than NetworkService by default.

There are two ways to workaround this. First, I recommend that ACL the NetworkService temporary directory to allow whichever users read/write/creator privs that you want to allow access so that you still have the benefits of loading a user profile and separating temp directories per appPool. This can easily be done on the command line like this:



icacls %windir%\serviceprofiles\networkservice\AppData\Lo cal\Temp /grant Users:(CI)(S,WD,AD,X)

icacls %windir%\serviceprofiles\networkservice\AppData\Lo cal\Temp /grant "CREATOR OWNER":(OI)(CI)(IO)(F)

This allows every user to create files and directories (WD = Write to Directory, AD = Add Directory, X = Execute, S = Synchronize). The user who creates them will be the “CREATOR OWNER” of the file/directory.

The “CREATOR OWNER” ACE allows this user to do whatever he wants with the file. Other users can’t access these files/directories because they are not “CREATOR OWNER” of them.



The other less favorable workaround is to disable the loadUserProfile setting on a per-appPool basis. loadUserProfile is a boolean property on an AppPool section, and can easily be set on the command line (for defaultAppPool) like this:



%windir%\system32\inetsrv\appcmd set config -section:applicationPools /[name='DefaultAppPool'].processModel.loadUserProfile:false



hope this helps -



bill

[jhardman]

I'm glad you found an answer. Thank you for posting your solution.

[gloyboy]

Wow - I spent a whole weekend trying to narrow this problem down - I have been reading Excel into web pages since IIS3, and couldn't figure out why this broke so badly in IIS7 (Vista). Many, many thanks to msghaleb for posting this reply from "bills" - changing permissions on this temp file made everything work again! Great information - thanks!

[taiyoonouta]

I did try the solution on setting the permission on the TEMP files.. but I encounter problem. Not too sure if you can help.

After i execute :
icacls c:\windows\serviceprofiles\networkservice\AppData\ Local\Temp /grant Users:(CI)(S,WD,AD,X)

it tells me "access is denied". Successfully processed 0 files. Failed processing: 0 files.

Do you have any idea how to solve this? I have tried to search more info on icacls, but I can't find any.

THanks in advance.

[jhardman]

I did try the solution on setting the permission on the TEMP files.. but I encounter problem. Not too sure if you can help.

After i execute :
icacls c:\windows\serviceprofiles\networkservice\AppData\ Local\Temp /grant Users:(CI)(S,WD,AD,X)

it tells me "access is denied". Successfully processed 0 files. Failed processing: 0 files.

Do you have any idea how to solve this? I have tried to search more info on icacls, but I can't find any.

THanks in advance.

Give a little more detail, exactly how are you processing your file, and show some of the code that opens it.

Jared