RE: OpenDataSource "Unspecified Error" when connecting to local Excel

From: Frank Jones (frank_at_bogusfiosincfake.com)
Date: 12/15/04

  • Next message: Kevin McDonnell [MSFT]: "RE: SQLSTTE = 08001 Microsoft OLE DB Provider for SQL Server [DBNM" 
    Date: Wed, 15 Dec 2004 11:01:02 -0800

    Still having this issue.
    Are there any another hidden permissions required for the OpenDataSource
    command to work? Any other files on the filesystem or access rights that a
    local administrator account does have that a local user account would not
    have to be able to make this work?

    Thanks for any information.

    "Frank Jones" wrote:

    > OpenDataSource gives "Unspecified Error" when connecting to local Excel
    > spread*** 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 spread*** 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
    >
    >
    >

  • Next message: Kevin McDonnell [MSFT]: "RE: SQLSTTE = 08001 Microsoft OLE DB Provider for SQL Server [DBNM"