27910 views

Troubleshooting linked server

Problem
This article guides you through the process of troubleshooting linked server issues. It provides steps to help eliminate common causes for your problems by verifying that the configuration of your networking is correct. 

 

Symptoms
Symptoms may include:
  • Some properties are not editable during configuration.
  • There are error messages received when running queries.
  • The queries against the linked server timeout.
  • The user cannot create a linked server.
  • The user cannot query the linked server.
  • The connector timed out when trying to run a scheduled job.
 
Resolution
Determine whether any of the troubleshooting steps below are true for the environment. The steps provide links to articles that help eliminate possible causes and take corrective action as necessary. 
    1. Verify that MS SQL Server Management Studio is running as an administrator. For more information, see this Microsoft article.
    2. When testing the connection, it works properly, but when running queries, this error message is received: 

      Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server " SERVICENOW" reported an error. Access denied. Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "SERVICENOW"

      Microsoft Data Access Components (MDAC) may not be working properly. For more information, see this Microsoft article.

    3. When running queries against the linked server, this error message is received:

      OLE DB provider "MSDASQL" for linked server "SERVICENOW" returned message "[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]java.net.SocketTimeoutException: connect timed out[1001]". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVICENOW"

      A firewall may be blocking traffic. Verify firewall settings.

    4. When trying to query the linked server, this error message is received: 

      OLE DB provider "MSDASQL" for linked server " SERVICENOW" returned message "[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK Client]Cannot open the configuration file.". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "`SERVICENOW". 

      The user may not have the privileges to set up the linked server. For more information, see this Microsoft article or watch this video tutorial on troubleshooting permissions issues in Microsoft SQL Linked Server.

    5. When trying to query the linked server, this error message is received:

      OLE DB provider "MSDASQL" for linked server " SERVICENOW" returned message "[DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Method failed: (https://demo.service-now.com/sys_user.do?SOAP&displayvalue=all&redirectSupported=true)HTTP/1.1 401 Unauthorized with code: 401[1001]". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVICENOW".

      This indicates that the user and/or the password is incorrect. Make sure to use the correct credentials.

    6. When using a wrong bit level of a driver (for example, 64-bit driver with 32-bit MSSQL Server), this error message is received:

      The test connection to the linked server failed.
      An exception occurred while executing a Transact-SQL statement or batch.
      (Microsoft.SqlServer.ConnectionInfo)
      Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SERVICENOW". OLE DB provider "MSDASQL" for linked server "SERVICENOW" returned message "Specified driver could not be loaded due to system error  126:The specified module could not be found, (ServiceNow ODBC Driver 32-bit C:\Program FIles (x86)\ServiceNow\ODBC\bin\O7oal26.dll).". Microsoft SQL Server, Error: 7303) 

    1. When trying to query the linked server no data returned, only column headers.

      Make sure you are not using 1.0.7.1 version. If so, upgrade to 1.0.7.3.

    1. Determine if an upgrade is needed to resolve ODBC query issues. For more information, see KB0542680: Determining if an upgrade is needed for ODBC.

  1. When trying to configure Microsoft SQL Server Integration Services (SSIS) to work with ODBC. Ensure that you are using the 32-bit version of the ODBC driver regardless of the OS bitness.
  2. Ensure the user account used to run the queries is a local user, not a domain user, on the computer running the ODBC Driver. For more information, see KB0547776: Reporting services cannot connect.

Note: If the problem still exists after trying the steps in this article, submit an incident to Technical Support and note this Knowledge Base article ID (KB0538992) in the problem description. For more information, see Customer Support. 

 

Article Information

Last Updated:2016-09-13 16:10:01
Published:2014-06-20