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.

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 JonesHi 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\Mi
crosoft SQL Server\<Instance
Name>\Providers\Microsoft.Jet.OLEDB.4.0
value must not exist or be 1.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\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.

Only the administrator can open the reports

Hi,
I've been struggling with this problem for a couple of days. The RS system
seems to be working fine. However only the administrators can open the
reports. If a user attempts to open a report, a login window pops up.
After pressing OK three times, a message "You are not authorized to view
this page" is displayed. Any ideas on how to fix this? Thanks.
Fabian CevallosMake sure you have given the users permissions to view the report (Browser is
the default access level).
SQLRS Server requires you to authenticate users of a domain. Those admins
that have full permissions on the server itself will have no problems, but
until you 'activate' accounts, all other domain accounts are unknow - and
therefore unauthorized - to access any reports at all.
Now comes the crunch. SQLRS will allow you to create access for domain
accounts. Unfortunately I have not -as yet - found a way to create local
group acconus per se. Therefore, if you want a set number of managers to view
a report, you may need to either set the group up on the domain or manage
them individually on the SQLRS server.
"Fabian Cevallos" wrote:
> Hi,
>
> I've been struggling with this problem for a couple of days. The RS system
> seems to be working fine. However only the administrators can open the
> reports. If a user attempts to open a report, a login window pops up.
> After pressing OK three times, a message "You are not authorized to view
> this page" is displayed. Any ideas on how to fix this? Thanks.
>
> Fabian Cevallos
>
>
>|||The users had permissions to view the report... I ended up removing SQLRS
and installing it again. Now everything works!
"Logicalman" <Logicalman@.discussions.microsoft.com> wrote in message
news:20743E32-2494-4FDE-9ED7-0A34A3A94FD3@.microsoft.com...
> Make sure you have given the users permissions to view the report (Browser
> is
> the default access level).
> SQLRS Server requires you to authenticate users of a domain. Those admins
> that have full permissions on the server itself will have no problems, but
> until you 'activate' accounts, all other domain accounts are unknow - and
> therefore unauthorized - to access any reports at all.
> Now comes the crunch. SQLRS will allow you to create access for domain
> accounts. Unfortunately I have not -as yet - found a way to create local
> group acconus per se. Therefore, if you want a set number of managers to
> view
> a report, you may need to either set the group up on the domain or manage
> them individually on the SQLRS server.
> "Fabian Cevallos" wrote:
>> Hi,
>>
>> I've been struggling with this problem for a couple of days. The RS
>> system
>> seems to be working fine. However only the administrators can open the
>> reports. If a user attempts to open a report, a login window pops up.
>> After pressing OK three times, a message "You are not authorized to view
>> this page" is displayed. Any ideas on how to fix this? Thanks.
>>
>> Fabian Cevallos
>>
>>

Only administrators can connect

We have been having a problem recently where all of a sudden sql starts
denying connections. The errors in the sql log say "login failed for
<user>. only administrators may connect at this time". I've been unable to
find any documentation on the net for what exactly this is and I can't seem
to figure out what's causing it. Any suggestions?Did you by any chance start the server with -m? If so, remove -m will allow
non administrator to connect.
--
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent Mills" <*bcm*@.hotmail.com> wrote in message
news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> We have been having a problem recently where all of a sudden sql starts
> denying connections. The errors in the sql log say "login failed for
> <user>. only administrators may connect at this time". I've been unable
to
> find any documentation on the net for what exactly this is and I can't
seem
> to figure out what's causing it. Any suggestions?
>|||No, that's the weird thing. It works fine for about a week or two and then
all of a sudden it just starts denying connections.
"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:OPZBCl$$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Did you by any chance start the server with -m? If so, remove -m will
allow
> non administrator to connect.
> --
> Gang He
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brent Mills" <*bcm*@.hotmail.com> wrote in message
> news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> > We have been having a problem recently where all of a sudden sql starts
> > denying connections. The errors in the sql log say "login failed for
> > <user>. only administrators may connect at this time". I've been
unable
> to
> > find any documentation on the net for what exactly this is and I can't
> seem
> > to figure out what's causing it. Any suggestions?
> >
> >
>

Only administrators can connect

We have been having a problem recently where all of a sudden sql starts
denying connections. The errors in the sql log say "login failed for
<user>. only administrators may connect at this time". I've been unable to
find any documentation on the net for what exactly this is and I can't seem
to figure out what's causing it. Any suggestions?Did you by any chance start the server with -m? If so, remove -m will allow
non administrator to connect.
Gang He
SQL Server Storage Engine Development
This posting is provided "AS IS" with no warranties, and confers no rights.
"Brent Mills" <*bcm*@.hotmail.com> wrote in message
news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
> We have been having a problem recently where all of a sudden sql starts
> denying connections. The errors in the sql log say "login failed for
> <user>. only administrators may connect at this time". I've been unable
to
> find any documentation on the net for what exactly this is and I can't
seem
> to figure out what's causing it. Any suggestions?
>|||No, that's the weird thing. It works fine for about a week or two and then
all of a sudden it just starts denying connections.
"Gang He [MSFT]" <ganghe@.online.microsoft.com> wrote in message
news:OPZBCl$$DHA.1452@.TK2MSFTNGP09.phx.gbl...
> Did you by any chance start the server with -m? If so, remove -m will
allow
> non administrator to connect.
> --
> Gang He
> SQL Server Storage Engine Development
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Brent Mills" <*bcm*@.hotmail.com> wrote in message
> news:u7MycX#$DHA.1224@.TK2MSFTNGP11.phx.gbl...
unable
> to
> seem
>