locked
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server reported an error only when the report was run from remote computers RRS feed

  • Question

  • We're running SQL Server 2008. In SSMS, three Linked Servers were created via GUI, each one linking to an Excel 2000 spreadsheet.

    General Properties:
    Provider: MS Jet 4.0 OLE DB Provider
    Product name: Excel
    Provider string: Excel 8.0

    Security Properties:
    There are two "Local server login to remote server login mappings", with Impersonate ticked while remote user & password are blank.
    For a login not defined in that list above, connection will be "made without using a security context".

    Properties of Microsoft.Jet.OLEDB.4.0:
    Enabled: Dynamic parameter, Nested queries, Allow inprocess & Supports 'Like' operator

    A report was then created in BIDS with a parameterized query.

    Data Source Properties:
    Embedded Connection
    Type: Microsoft SQL Server
    Connection String: Data Source=SQLSERVER;Initial Catalog=master
    Windows Authentication

    After deployment, the report runs fine under SSRS when user logged into the server locally. The report also works when user logged into the server via Remote Desktop Connection.

    However, when the report is run on a remote computer, the following error is generated:

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "OS_MATERIAL_FINER" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "OS_MATERIAL_FINER".

    Please kindly help! Thank you very much in advance.

    Wednesday, May 19, 2010 4:25 AM

Answers

  • We're running SQL Server 2008. In SSMS, three Linked Servers were created via GUI, each one linking to an Excel 2000 spreadsheet.

    General Properties:
    Provider: MS Jet 4.0 OLE DB Provider
    Product name: Excel
    Provider string: Excel 8.0

    Security Properties:
    There are two "Local server login to remote server login mappings", with Impersonate ticked while remote user & password are blank.
    For a login not defined in that list above, connection will be "made without using a security context".

    Properties of Microsoft.Jet.OLEDB.4.0:
    Enabled: Dynamic parameter, Nested queries, Allow inprocess & Supports 'Like' operator

    A report was then created in BIDS with a parameterized query.

    Data Source Properties:
    Embedded Connection
    Type: Microsoft SQL Server
    Connection String: Data Source=SQLSERVER;Initial Catalog=master
    Windows Authentication

    After deployment, the report runs fine under SSRS when user logged into the server locally. The report also works when user logged into the server via Remote Desktop Connection.

    However, when the report is run on a remote computer, the following error is generated:

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "OS_MATERIAL_FINER" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "OS_MATERIAL_FINER".

    Hi,

    I think the issue may be caused by the login does have sufficient permission to open the Excel linked server.

    When running the report on a remote machine, could you please use SQL Profiler to see which login is used to connect to SQL Server?

    Using SQL Server Profiler
    http://msdn.microsoft.com/en-us/library/ms187929.aspx

    Instead of “Be made without using a security context”, could you please try “Be made using the login's current security context” or “Be made using this security context”?

    Linked Server Properties (Security Page)
    http://msdn.microsoft.com/en-us/library/ms188477.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Dan Benediktson Friday, May 21, 2010 4:53 PM
    • Marked as answer by KJian_ Thursday, May 27, 2010 7:11 AM
    Thursday, May 20, 2010 7:27 AM
  • It sounds like you are trying to do Windows authentication, and it is failing when you do a remote connection. This sounds like a classic double-hop problem. Double-hop authentication requires special configuration, so, given your scenario, I'd suggest following Jian's guidance, and use "Be made using this security context" and specifying a specific account to use. You can read more about the configuration by searching "double-hop authentication", if you need to go that route.


    This post is provided 'as is' and confers no express or implied warranties or rights.
    • Proposed as answer by Dan Benediktson Friday, May 21, 2010 4:53 PM
    • Marked as answer by KJian_ Thursday, May 27, 2010 7:11 AM
    Friday, May 21, 2010 4:53 PM

All replies

  • We're running SQL Server 2008. In SSMS, three Linked Servers were created via GUI, each one linking to an Excel 2000 spreadsheet.

    General Properties:
    Provider: MS Jet 4.0 OLE DB Provider
    Product name: Excel
    Provider string: Excel 8.0

    Security Properties:
    There are two "Local server login to remote server login mappings", with Impersonate ticked while remote user & password are blank.
    For a login not defined in that list above, connection will be "made without using a security context".

    Properties of Microsoft.Jet.OLEDB.4.0:
    Enabled: Dynamic parameter, Nested queries, Allow inprocess & Supports 'Like' operator

    A report was then created in BIDS with a parameterized query.

    Data Source Properties:
    Embedded Connection
    Type: Microsoft SQL Server
    Connection String: Data Source=SQLSERVER;Initial Catalog=master
    Windows Authentication

    After deployment, the report runs fine under SSRS when user logged into the server locally. The report also works when user logged into the server via Remote Desktop Connection.

    However, when the report is run on a remote computer, the following error is generated:

    The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "OS_MATERIAL_FINER" reported an error. The provider did not give any information about the error. Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "OS_MATERIAL_FINER".

    Hi,

    I think the issue may be caused by the login does have sufficient permission to open the Excel linked server.

    When running the report on a remote machine, could you please use SQL Profiler to see which login is used to connect to SQL Server?

    Using SQL Server Profiler
    http://msdn.microsoft.com/en-us/library/ms187929.aspx

    Instead of “Be made without using a security context”, could you please try “Be made using the login's current security context” or “Be made using this security context”?

    Linked Server Properties (Security Page)
    http://msdn.microsoft.com/en-us/library/ms188477.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Proposed as answer by Dan Benediktson Friday, May 21, 2010 4:53 PM
    • Marked as answer by KJian_ Thursday, May 27, 2010 7:11 AM
    Thursday, May 20, 2010 7:27 AM
  • It sounds like you are trying to do Windows authentication, and it is failing when you do a remote connection. This sounds like a classic double-hop problem. Double-hop authentication requires special configuration, so, given your scenario, I'd suggest following Jian's guidance, and use "Be made using this security context" and specifying a specific account to use. You can read more about the configuration by searching "double-hop authentication", if you need to go that route.


    This post is provided 'as is' and confers no express or implied warranties or rights.
    • Proposed as answer by Dan Benediktson Friday, May 21, 2010 4:53 PM
    • Marked as answer by KJian_ Thursday, May 27, 2010 7:11 AM
    Friday, May 21, 2010 4:53 PM