JamesMayfield
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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\mysqloperationsdo
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.
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\mysqloperationsdo
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.
Fantastic. Once again that Filemon program proves it's worth a million bucks!
ASKER
I'm glad it didn't cost me that though!!! That's more than my entire annual IT budget!
ASKER
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