OpenDataSource "Unspecified Error" connecting to Excel file

OpenDataSource gives "Unspecified Error" when connecting to local Excel
spreadsheet file with account not in local administrators group, on
Microsoft Windows 2003 / Microsoft SQL Server 2000 sp3a.
I am having a permissions problem using the OpenDataSource command to open
an Excel spreadsheet located on our Sql Server. The query is as follows:
SELECT * FROM opendatasource('Microsoft.Jet.OLEDB.4.0', 'Data
Source="C:\myPath\tempExcel.xls";User ID=Admin;Password=;Extended
properties=Excel 8.0')...['US Mail$'] tblImport
As you can see, the file is on the local Sql Server. All domain users have
full control to the myPath directory. The Sql Server service (and Sql
Server agent service) is running as the domain administrator account. We
are using integrated security to authenticate with the Sql Server. I even
tried setting up the proxy account as the domain administrator thinking that
maybe the opendatasource would use that.
The error I get when running this query on a remote machine as a domain user
through Query Analyzer is "Unspecified Error" in the Jet Initialization.
Even if I add domain users to the server administrator role, I still get the
error. However, if I add that domain users account to the local
administrators group on the Sql Server, the query works. I know this is not
an NTFS permission issue, as I have auditing turned on and there are no
object security failures in the event logs.
So my question is: what specific permissions are required for a domain user
to run an OpenDataSource query like this without having to add them to the
local administrators group on the Sql Server? Having to add the domain
users that need to run this query to the Sql Servers local administrators
group is simply unacceptable...
Thanks,
Frank Jones
Hi Frank,
From your descriptions, I understood that your non-admin users are not able
to execute OpenDataSource with the error message "Unspecified Error" while
only those who have system admin permissions could do it. Have I understood
you? Correct me if I was wrong.
First of all, please make sure your domain users have the access permission
to the following Registry Key
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ Providers\Microsoft.Jet.OL
EDB.4.0
DisallowAdhocAccess = 0 should be added to allow access.
NOTE that you *must* Reboot the server for these registry changes to take
effect.
Secondly, please make sure your users have access permission to
C:\WINDOWS\system32\msjet40.dll and its folder
If all above does not work, I would like to suggest you using Regmon to
monitor which registry key was checked and denied for your domain users
with the following steps
1. Select the correct version for your Regmon and then download it from the
web site below
http://www.sysinternals.com/ntw2k/source/regmon.shtml
2. Install Regmon
3. Login as member of Local Administrator,
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file1
4. Login as member of non-Administrator
Start Regmon
Run the T-SQL command
Stop Regmon
Save the file as file2
Compare the difference between file1 and file2, give the permissions to
those ACCESSDENY keys to non-admin users.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.