Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1295
  • Last Modified:

ASP.NET Read Excel File

I get an "Unspecified Error" from "Microsoft JET Database Engine" when logged in as most users on the deployment server.  When I log in (I am an enterprise admin) I do not get an error, but another user that is trying this gets the error.  I then added the user to the ent admins group and had them log out of thier PC to ensure all IE instances were shut down, go back in and try again, and they still get the error so it does not appear to be permissions.  I have tested to make sure SheetName is getting populated which it is, and the file does exist.  The line that throws the error is "Call da.Fill(xldt)".

        Dim xlcn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FileName & ";Extended Properties=""Excel 8.0;HDR=YES;""")
        Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM [" & SheetName & "$]", xlcn)
        Dim xldt As New DataTable
        Call da.Fill(xldt)
        Call xlcn.Close()

Any idea what the deal is?  The stack at the point the error occurs is below, and while I am not that great at reading the stack it looks to me that it is actually when trying to execute the statement and not on the open step that it is throwing the error.

Stack: at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
0
SkipFire
Asked:
SkipFire
  • 21
  • 15
  • 15
2 Solutions
 
Bob LearnedCommented:
What is the authentication mode for your web site?

Bob
0
 
SkipFireAuthor Commented:
Basic authentication, no anonymous.
0
 
Bob LearnedCommented:
This is an odd problem, because IIS should use the same authentication method for all users, so I don't quite understand yet why you would be able to execute without error, while others can't.  Thinking, thinking, ...

Bob
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
RobertRFreemanCommented:
What is FileName?  Is it a local file, mapped drive, or a network file?

If it's on the network, try copying it to the web server's hard drive and accessing it there.
0
 
SkipFireAuthor Commented:
File name is a full path to a file name on the local hard drive in a subdirectory of the web site.  If it was a problem with the path I would think it would error with the stack ending at OleDbConnection.Open().
0
 
RobertRFreemanCommented:
Do you have impersonation enabled?
0
 
SkipFireAuthor Commented:
Yes, and it works for me so impersonation is obviously working.  It seems it has to be something specific to who is logged in, but elevating someone to Enterprise Admin did not fix the problem so I'm at a loss.
0
 
RobertRFreemanCommented:
Change the permissions on the excel file itself, to allow read to everyone.
0
 
RobertRFreemanCommented:
Since you have impersonation enabled, the ASPNET process will run under the logged in users credentials.  Therefore the logged-in user must have permission to access the Excel file.  

To test permissions, log into the server as the other user.
Then test to see if they can open the excel file using the same path that's in FileName?
0
 
SkipFireAuthor Commented:
I know it isn't a problem of having access to the Excel file as the previous step is using the Excel object to find out the sheetname and that works correctly.
0
 
RobertRFreemanCommented:
Then try this:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;825738

This should apply for Basic Authentication on an Excel file as well.
0
 
SkipFireAuthor Commented:
The folder specified did not exist, so I gave EVERYONE full access to the documents and settings folder but the error still occurs.
0
 
Bob LearnedCommented:
What version of ASP.NET?  1.1 or 2.0?

Bob
0
 
SkipFireAuthor Commented:
ASP.NET 1.1
Office 2000
0
 
Bob LearnedCommented:
When you say that the folder mentioned in the article doesn't exist, are you sure?  Did you have 'Show all files' selected for Windows Explorer?  All instances running ASP.NET 1.1 should have that Temp folder.

Bob
0
 
SkipFireAuthor Commented:
The folder that did not exist was c:\Documents and Settings\ServerName, thus the rest of the tree did not exist.  I always have show hidden turned on and show system turned on, and I could see other hidden folders so I know the setting was definately turned on.
0
 
Bob LearnedCommented:
C:\document and settings\<Machine name goes here>.

Example:

   c:\document and settings\homer

I just wanted to make sure that you understood that.

Bob
0
 
SkipFireAuthor Commented:
Yeah, I was not looking for "ServerName", I was looking for the server's name.  The server's name is LIBDC1 so the actual path would be C:\Documents and Settings\LIBDC1, and that does not exist.c
0
 
Bob LearnedCommented:
What version of IIS are you using?

Bob
0
 
SkipFireAuthor Commented:
5 I think, Windows 2000.
0
 
Bob LearnedCommented:
Another silly question.  Are you checking on the deployment server for that file?  I don't see very many reasons for this failure, so I am trying to dismiss the easy things first.  It doesn't make any sense when you say that you have IIS 5, and you don't see that folder.

Bob
"Mr. Obvious"
0
 
SkipFireAuthor Commented:
Yes I am checking on the deployment server.  I connect via Remote Administrator and look at the local file system.
They only have one server, it is DC, web, sql, file, print, DNS, DHCP, and WINS all rolled into one.
0
 
Bob LearnedCommented:
Can you run the site with Windows Authentication, and not Basic authentication, and see if that helps?

Bob
0
 
SkipFireAuthor Commented:
No change.  
0
 
Bob LearnedCommented:
Double check, because I believe that this will come down to that folder that Robert talked about before.  Search the server for any part of a file named ASPNET.  I found the folder mentioned in the article on my local development machine that way.

Bob
0
 
SkipFireAuthor Commented:
I just searched all local hard drives and the only folder with aspnet in the name is "aspnet_client" inside the web root.  Plus with me having elevated the person with the problem to Enterprise Admin that should pretty much rule out a file system permission issue.
0
 
Bob LearnedCommented:
Yeah, you would think, but security is a tricky issue, and with a combination of policies, you can have a situation where one person is given access while someone else is not.  But, that's neither here nor there, since you can't even find that folder.  

We have had security issue problems here with Excel, since it does need to create temporary files somewhere.  It's that somewhere that we need to find.

Bob
0
 
RobertRFreemanCommented:
Maybe the problem is that you don't have the folder.

I would run aspnet_regiis -i and if that doesn't work, reinstall the .Net framework.
0
 
SkipFireAuthor Commented:
If the problem was not having hte folder at all then it would not work for anyone.
0
 
Bob LearnedCommented:
Are the temporary files getting written to %WINDIR%\System32\InetSrv?

Bob
0
 
Bob LearnedCommented:
How are you specifying file names?  Relative or full path?

Bob
0
 
SkipFireAuthor Commented:
I never see temp files get created so I have no clue where it is happening.
0
 
Bob LearnedCommented:
Are you using impersonation in the web.config?

Bob
0
 
SkipFireAuthor Commented:
I am specifying full path and file name.

It appears I do not have impersonation in the web.config.
0
 
Bob LearnedCommented:
If you use impersonation and Windows Authentication, then the user context would be the authenticated user, and there rights would apply.  Without impersonation, the user context is different.  I am not certain what the context is when opening an Excel worksheet through ADO.NET, though.

Bob
0
 
SkipFireAuthor Commented:
Step 1 is to enable impersonation and see what happens, I'll know in 5 to 10 minutes.
0
 
SkipFireAuthor Commented:
identity impersonate="true" results in no change.
0
 
RobertRFreemanCommented:
Check the Machine.config for impersonation as well, although the web.config will override the machine.config.
0
 
RobertRFreemanCommented:
You need to find the folder as in the article.  Look for "Local Settings" folders.  You should find one for each user that has logged into the server and one for the ASPNET account on the Machine.  Make sure you are searching hidden folders as well.

You may also compare the Machine.config to a standard machine.config to see what else is configured differently.

If you can't find this folder, then you will need to reregister or reinstall .Net to get a "standard" configuration.

Note, enabling an admin role for a user does not ensure that the user will have permissions on a particular file.  It may also require time to replicate or a server reboot.

Are you intending to enable impersonation for a particular reason?
0
 
Bob LearnedCommented:
>>Are you intending to enable impersonation for a particular reason?
Scroll up a little, and look at the comments above ^^^^

Bob
0
 
RobertRFreemanCommented:
I just want to know what the final result should be.  Impersonation enabled/disabled, and why.
0
 
SkipFireAuthor Commented:
The machine.config has never been touched, I am the only admin/developer that has access to the box.

I need to reregister .NET If I can't find what what folder?

There is no replication delay as it is all on the same server, though I did do a server reboot to ensure that the problem was not an existing session, or cached permissions or something.

I believe I needed to enable impersonation to get the Excel objects to work, as for actually doing the select statement I don't need impersonation there.

What specific commnets are you looking at me responding to?
0
 
RobertRFreemanCommented:
You should not need impersonation to use the Excel objects.  Impersonation is only required when you want to specify permissions for application resources on a per folder/file basis.  Or it can be used to access network resources.  If the application resources are on the same box, then you will not need impersonation.

I was referring to the Documents and Settings/MachineName/ASPNET/Local Settings/Temp folder.

Is the web server also the domain controller?
0
 
SkipFireAuthor Commented:
Yes it is a DC.
0
 
RobertRFreemanCommented:
Ouch!  I'm feeling the planets are not aligned in your favor.

The framework has problems creating the ASPNET account on a domain controller.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q315158
0
 
RobertRFreemanCommented:
So, what user is set up in the <processModel> section?
0
 
SkipFireAuthor Commented:
There is no processmodel section.
0
 
RobertRFreemanCommented:
Apperently, the problem was only for 1.0 in 1.1, the DC will use the IWAM_MachineName account.
Try looking for the Documents and Settings for IWAM_MachineName
0
 
RobertRFreemanCommented:
Looking at all the information so far, I have no idea why this would work for you and not for someone else.  
Especially if impersonation was not enabled.

Try denying IWAM_MachineName access to the excel file and see if you can still access it.
That would let you know if the server is using that account.
0
 
SkipFireAuthor Commented:
Not sure what has changed, but it now appears to be working for all users.  Impersonation is not enabled.
0
 
RobertRFreemanCommented:
Nice. :)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 21
  • 15
  • 15
Tackle projects and never again get stuck behind a technical roadblock.
Join Now