Can't Read Excel File (OleDb) w/ ASP.NET Impersonation

[cmay]

Problem:
When impersonation is set in the web.config file, I cannot establish an
OLEDB connection to the database from my ASP.NET v1.1 application.
(Unspecified Error. Stack trace shows it happens on the ProcessResults
method within the OleDB Provider)

Cause:
Both of these KB articles from MS describe the problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;827190
http://support.microsoft.com/default.aspx?scid=kb;EN-US;825738

The problem, is that on my development machine, there is no
C:\Documents and Settings\ServerName folder (the folder they say needs
to have security changes made to it).

I am not using impersonation w/ Windows authentication, I am using
forms auth, and I don't impersonate the logged in user, I supply the
user account to impersonate in the web.config file:

<identity impersonate="true" username="asdf" password="fdsa" />


Anyone know what I can do to fix this? My only work around is to turn
off impersonation for the pages that need to access this resource, but
that is a poor solution.

 

[Norman Yuan]

Is the username in <identity.../> is a valid username on the network
domain/the IIS computer? I think you need prefix the username with
domain/computer name: "MyDomain\asdf" or "MyComputer\asdf".

 

[cmay]

Yes the identity being used is a valid domain user and the account is
entered as "domain\user".

This is working as expected because the website needs to access various
resources that are only available to the specified domain account, and
it does this without any problem.

 

[Paul Clement]

¤ Problem:
¤ When impersonation is set in the web.config file, I cannot establish an
¤ OLEDB connection to the database from my ASP.NET v1.1 application.
¤ (Unspecified Error. Stack trace shows it happens on the ProcessResults
¤ method within the OleDB Provider)
¤
¤ Cause:
¤ Both of these KB articles from MS describe the problem:
¤ http://support.microsoft.com/default.aspx?scid=kb;en-us;827190
¤ http://support.microsoft.com/default.aspx?scid=kb;EN-US;825738
¤
¤ The problem, is that on my development machine, there is no
¤ C:\Documents and Settings\ServerName folder (the folder they say needs
¤ to have security changes made to it).
¤
¤ I am not using impersonation w/ Windows authentication, I am using
¤ forms auth, and I don't impersonate the logged in user, I supply the
¤ user account to impersonate in the web.config file:
¤
¤ <identity impersonate="true" username="asdf" password="fdsa" />
¤
¤
¤ Anyone know what I can do to fix this? My only work around is to turn
¤ off impersonation for the pages that need to access this resource, but
¤ that is a poor solution.

The account you are impersonating will require full access to the folder where the file is located.
Have you set up the appropriate permissions for this account?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[cmay]

Yes I did this as well.

I gave full rights to the excel file, and the folder that it is in.
I even gave full rights to "Everyone" to see if that mattered.

The site can create the excel file (from an upload) and it can delete
the file, it just can't connect to it via OleDb.

 

[Paul Clement]

¤ Yes I did this as well.
¤
¤ I gave full rights to the excel file, and the folder that it is in.
¤ I even gave full rights to "Everyone" to see if that mattered.
¤
¤ The site can create the excel file (from an upload) and it can delete
¤ the file, it just can't connect to it via OleDb.

Check to see who the authenticated user is with
System.Security.Principal.WindowsIdentity.GetCurrent().Name.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[cmay]

When I look for that it shows the account I am using for impersonation
in the web.config file, in the format of domain\username.

When I check the effective permissions on the folder:
C:\Documents and Settings\username\Local Settings\Temp (which is the
closest folder I have to the one they describe in the KB article), it
shows that "username" has full rights.

 

[Paul Clement]

¤ When I look for that it shows the account I am using for impersonation
¤ in the web.config file, in the format of domain\username.
¤
¤ When I check the effective permissions on the folder:
¤ C:\Documents and Settings\username\Local Settings\Temp (which is the
¤ closest folder I have to the one they describe in the KB article), it
¤ shows that "username" has full rights.

As I mentioned before the permissions need to be set on the folder where the file is located. Ignore
the folder in the KB article.


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[cmay]

I gave full permissions to both the user that is the impersonating
account as well as the "Everyone" group to the folder w/ the excel
file, but I still get the same error.

 

[Paul Clement]

¤ I gave full permissions to both the user that is the impersonating
¤ account as well as the "Everyone" group to the folder w/ the excel
¤ file, but I still get the same error.

Could you post a sample of the code and identify where the error is occurring?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[cmay]

Here is the section of code where it errors.
This function is getting the excel file from an upload, saving it to
the file system, and trying to open it.
When I turn off impersonation, this works w/o any problem.

sFileName = sTempFileName & sFileExtension
sPhysicalFolder = PATH_ROOT &
"ProjectData\Revenue\TempExcelFolder"
sPhysicalPath = sPhysicalFolder & "\" & sFileName

'*** check if the folder exists
If Not System.IO.Directory.Exists(sPhysicalFolder) Then

System.IO.Directory.CreateDirectory(sPhysicalFolder)
End If

'*** save the file
Me.File1.PostedFile.SaveAs(sPhysicalPath)


''*** Create connection string variable.
sConnectionString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPhysicalPath & ";" &
"Extended Properties=Excel 8.0;"

''*** Create connection object by using the preceding
connection string.
oConn = New OleDbConnection(sConnectionString)
oConn.Open()
'*** error on the line above

 

[Paul Clement]

¤ Here is the section of code where it errors.
¤ This function is getting the excel file from an upload, saving it to
¤ the file system, and trying to open it.
¤ When I turn off impersonation, this works w/o any problem.
¤
¤ sFileName = sTempFileName & sFileExtension
¤ sPhysicalFolder = PATH_ROOT &
¤ "ProjectData\Revenue\TempExcelFolder"
¤ sPhysicalPath = sPhysicalFolder & "\" & sFileName
¤
¤ '*** check if the folder exists
¤ If Not System.IO.Directory.Exists(sPhysicalFolder) Then
¤
¤ System.IO.Directory.CreateDirectory(sPhysicalFolder)
¤ End If
¤
¤ '*** save the file
¤ Me.File1.PostedFile.SaveAs(sPhysicalPath)
¤
¤
¤ ''*** Create connection string variable.
¤ sConnectionString =
¤ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPhysicalPath & ";" &
¤ "Extended Properties=Excel 8.0;"
¤
¤ ''*** Create connection object by using the preceding
¤ connection string.
¤ oConn = New OleDbConnection(sConnectionString)
¤ oConn.Open()
¤ '*** error on the line above

I don't see any issues with the code.

I'm beginning to wonder whether you have a permissions issue with the Jet Database Engine
components. Do you know what version you have and have you tried updating to the latest Jet SP?

BTW, you're not running Commerce Server are you?

http://support.microsoft.com/default.aspx?scid=kb;en-us;883671


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[cmay]

Can you tell me where I check the version of Jet that I have on my
machine?

Not running commerce server, just XP Pro SP2.

 

[Paul Clement]

¤ Can you tell me where I check the version of Jet that I have on my
¤ machine?
¤
¤ Not running commerce server, just XP Pro SP2.

Look in your Windows /System32 folder for the file MSJET40.DLL and check the version number in the
properties. The version number and corresponding SP would be listed in the following article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;239114


Paul
~~~~
Microsoft MVP (Visual Basic)