[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Excel Linked Server Permissions Issue

I am running an SQL server where all users connect using NT Security.

I have created a Linked Server to an excel spreadsheet.

This works perfectly for any user who is part of the machine administrators group.
This fails for everyone else.

It is not permissions to the file. I have tested that.

Any help that would allow me to fix this problem would be greatly appreciated.

Regards, James Mayfield
0
JamesMayfield
Asked:
JamesMayfield
  • 3
  • 3
1 Solution
 
nmcdermaidCommented:
Most likely the user doesn't actually have access to the Excel driver. What is the error message?

Try running FileMon (http://www.sysinternals.com/Utilities/Filemon.html) on the server. It will tell you all the file access that is going on.

You'll probably find that the user is effectively trying to access an Excel driver in a windows folder.
0
 
JamesMayfieldAuthor Commented:
It isn't a file access issue to do with the excel file, as the excel file can be opened by this user using other means.

The error is.....

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.  
[OLE/DB provider returned message: Unspecified error]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005:   ].

Will the filemon utility tell me anything useful about the user having access to the Excel Driver? (This is just the Jet ODBC drive.)

Thanks,
     -James
0
 
nmcdermaidCommented:
Its the actual driver file (i.e. ODBCJT32.DLL) that might be the access issue.

If that is the issue Filemon will indicate that access was deined on that particular file when you capture the access. I know its a pain having to use another tool to troubleshoot but Filemon comes in very handy for lots of other things as well.


Actually it might be quicker to find the ODBCJT32.DLL file on the server and see if either user can open that directly too.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JamesMayfieldAuthor Commented:
WINNER!!!

It wasn't actually the ODBC driver at all.

Would you believe.....

What was causing the issue, was the fact that SQL Server, when creating the ODBC connection to an excel file, creates a file in the TEMP folder of the user that is running SQL server. If the user does not have permissions to that folder (in my case, this was c:\documents and settings\mysqloperationsdomainaccount\local settings\temp), then it fails.

Filemon let me trace what happened when I successfully opened the file, then trace back to look for what failed when the user tried the operation.

I have now moved the system users temp folder to a different location (requires a reboot to take effect), and gave my user permissions on that folder.

Thanks heaps for the quick response and the useful tip. It was an EXCELLENT help.
0
 
nmcdermaidCommented:
Fantastic. Once again that Filemon program proves it's worth a million bucks!
0
 
JamesMayfieldAuthor Commented:
I'm glad it didn't cost me that though!!! That's more than my entire annual IT budget!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

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