none
ODBC Driver 17 for SQL Server - Linked Server Fails RRS feed

  • Question

  • ODBC Driver 17 for SQL Server  - Linked Server failure

    I have a problem where my linked server works for about 12 minutes and then fails.  Sorry I do not have the failure message at this point, but I can get it tomorrow

    I have a Windows 2012 Server and have SQL Server 2014 SP3 Installed.

    I have a requirement to provide a linked server to another SQL Server instance and the way I had to set it up was to create a system DSN connection using the original “SQL Server” ODBC drivers on my server connecting to the remote server and then I created a linked server using the Microsoft OLE DB Provider for ODBC provider to that system DSN

    This was working fine for many years until TLS 1.2 became required.  To deal with TLS 1.2 I installed the ODBC Driver 17 for SQL Server.  I then created a new DSN connection and restarted the instance and the linked server tested fine. 

    The problem is that after about 12 minutes the Linked server fails – sorry I do not have the error message, but its something about cannot connect using the ODBC driver 17 for SQL server.  If I restart the instance the linked server will work again for about 10 minutes and fail. 

    The way I fixed it was I created the DSN connection using the ODBC Driver 11 for SQL server – it supports TLS 1.2 just fine and the linked server did not fail after 10 minutes.  As a matter of fact, I could get the linked server to work without even having to restart the instance. 

    My question is why does the linked server fail after 10 minutes using the ODBC Driver 17 for SQL server. 

    Any help is appreciated.   

    Sunday, February 3, 2019 9:14 PM

All replies

  • Below is the error when trying to test the linked server

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------
    The test connection to the linked server failed.
    ------------------------------
    ADDITIONAL INFORMATION:
    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 "LinkedServerNamehere".
    OLE DB provider "MSDASQL" for linked server "LinkedServerNamehere" returned message "[Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
    OLE DB provider "MSDASQL" for linked server "LinkedServerNamehere" returned message "[Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection".
    OLE DB provider "MSDASQL" for linked server "LinkedServerNamehere" returned message "[Microsoft][ODBC Driver 17 for SQL Server]Encryption not supported on the client.". (Microsoft SQL Server, Error: 7303)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.6024&EvtSrc=MSSQLServer&EvtID=7303&LinkId=20476

    Any help would be appreciated.  

    Monday, February 4, 2019 1:58 PM
  • I just found out that one instances connects just fine using the ODBC 17 for SQL Server DSN connection.  I will not begin to compare the two
    Monday, February 4, 2019 8:04 PM
  • Hi ayoubjeff,

     

    From the error message, it seems that the linked server can't be found via the server name. Could you please try to ping the hostname or the IP for the linked server directly after the failed connection to ensure that target is allowed.

     

    It seems that the question is also related to the DSN configurations. Please check the DSN configurations.

     

    >>I just found out that one instances connects just fine using the ODBC 17 for SQL Server DSN connection.

     

    Do you also create the linked server to other instance from the same source instance and machine.

     

    Best Regards,

    Emily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, February 5, 2019 7:45 AM
  • Thank you very much for the feedback and this is very interesting 

    Can you please add some detail

    From the error message, it seems that the linked server can't be found via the server name.

    So are you saying the linked server I set up cannot be found from the SQL Instance?  Or are you saying the linked server cannot find the destination server configured in the DSN connection?  The ODBC connection to the destination server worked fine every time even while getting the error, but I will double check. 

       

    Could you please try to ping the hostname or the IP for the linked server directly after the failed connection to ensure that is allowed.  

    Are you saying from my Windows Server can I ping the destination server?  Please help me here I don't understand

     It seems that the question is also related to the DSN configurations. Please check the DSN configurations. 

    I did and they are exactly the same

     

    I just found out that one instance / Server works just fine using the ODBC 17 for SQL Server DSN connection.

    Do you also create the linked server to other instance from the same source instance and machine 

    No I have three separate servers\instances - that connect to the same destination - two of them fail and one works just fine using the 17 Drivers.  

    Thank you very much for the reply and I look forward to hearing from you again



    • Edited by ayoubjeff Wednesday, February 6, 2019 12:26 AM
    Wednesday, February 6, 2019 12:18 AM
  • I have begun comparing the failed and successful instances and found the following

    A: They using the same provider
    B: The Provider Settings are the same
    C: The linked Server settings are the same
    D: The DSN connections are exactly the same
    E: The SQL Version patch levels are exactly the same
    F: I am not so sure about the Windows patching, but they should be the same
    G: The ODBC Drivers are exactly the same. 
    H: The only difference is that the server\instance that works is one I installed just one month ago. The other instances have been installed for a few years. 


    Wednesday, February 6, 2019 12:28 AM
  • Tomorrow, when I get to work, will check the TLS settings 

    I wonder if there is a miss match in the TLS setting of my server and the destination server.  Maybe the version 11 driver works with TLS 1.1 or 1.0 and the version 17 driver does not. 


    • Edited by ayoubjeff Wednesday, February 6, 2019 12:32 AM
    Wednesday, February 6, 2019 12:32 AM
  • The error message you get means what it says: the remote server cannot be found.

    You say that the linked server works for 10-12 minutes and then starts failing with this message. That's, well, interesting. That error is normally quite binary.

    If it works on one machine, but not the other, I would look for aliases and entries in the hosts file. An outgoing firewalls could possibly be an issue, or something else on the network.

    I find it difficult to believe that the ODBC version or TLS has anything to do with it. It if was TLS, I would expect a different error message.

    By the way, people I know at Microsoft tells me that using ODBC for linked servers to SQL Server is not supported.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, February 6, 2019 10:32 PM
  • Thank you very much for your reply. 

    I will give it a try tomorrow when I get back to the office.

    I have another strange issue that popped up after switching to the new ODBC 17 Driver Developers are now complaining that while using or Toad and the new ODBC driver the XML columns are not returning when they do select * from - it returns all the columns except the XML column.

    To elaborate on the issue the developer is using either Toad or sqlplus and connecting from an Oracle Schema. When the developer executes select * from table it returns all the columns except the XML column. When the develop execute select xml_column from table he gets the error

    [Error] Execution (1.8): ORA-00904: “XML_COLUMN”: invalid identifier.

    In this case we are querying an SQL Database from an Oracle database and the way we have it set up is

    A: I create an ODBC connection on my SQL server that connects to the instance
    B: The Oracle team creates a DBLink in the schema that connects to my ODBC connection.

    The developer uses the DBLink to query SQL data. The interesting part is that it worked with the original SQL Server ODBC driver on the box, but we had to update the driver to support TLS 1.2 now it will not return XML columns. I tried to use the Version 11 SQL ODBC driver and same results. I am trying to figure out if there is a setting in the DBLink or in the ODBC connection

    I also performed the following test. I created a linked server in a different SQL instance using the same ODBC connection with Version 17 driver. In SSMS I performed a select ...Openrowset (linkedserver, select * from...) and it returned all the columns. In essence, I am using the same ODBC Connection with a linked server and it worked. That would suggest the ODBC connection will actually return XML data.
    Thank you very much and any additional feedback is appreciated.

    • Edited by ayoubjeff Thursday, February 7, 2019 4:05 PM
    Thursday, February 7, 2019 3:23 PM
  • I think I understand why you were able to retrieve the XML column with the old ODBC driver, but not the newer drivers.

    The old ODBC driver does not know about the XML data type, so SQL Server therefore returns the XML data as ntext. The newer drivers knows about the XML data type, so the data is returned as data type.

    I have absolutely no knowledge of the Oracle DBlink, but it seems that it simply removes columns of data types it does not understand.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Proposed as answer by Abbottee Monday, February 11, 2019 9:39 AM
    Thursday, February 7, 2019 10:08 PM
  • Thank you very much for the information - I will work with this and let you know what happens
    Friday, February 8, 2019 6:03 PM
  • I am interested in your update.
    Monday, February 11, 2019 9:39 AM
  • Thanks for your help.

    Topic: XML Column - I discussed with the developers and they have updated their code to deal with the XML column.  I think they rewrote the query to NOT return the XML data

    Topic: Linked Server Failure - I have not been able to address the RCA of the problem, since I have it working in production using an older driver version.  I am now trying again.  

    Recap - in my situation using the ODBC Driver 17, I have the Linked Servers that work in one instances but not in two others.  In the non working instance the linked server is working fine with an older version of the ODBC driver.  Also on both of the non working instances the linked server works for about 15 minutes after the instance reboots.

    I compared the ODBC settings in both the working and non working instance and both are exactly the same.  I will now see if there is any difference in the SQL Shared components that are installed.  

    Any additional help would be appreciated.  

    Monday, February 25, 2019 9:51 PM
  • Do you try to use another drivers?
    Tuesday, February 26, 2019 4:55 PM
  • Thanks for the reply

    Yes, I tried a different Driver and it worked - The linked server continued to work after 15 minutes, but the application threw errors

    I did find a fix, but need to figure out the root cause.  I unchecked Allow Inprocess and now the linked server works and stays working. Problem is the limitation on being able to updates and inserts of Long columns like (Text, ntext and image) could cause the application to fail.  

    The interesting thing is that I have two servers.  One server works with the ODBC 17 Driver just fine, but the other does not.  On the server that works, there are multiple instances and each one works fine.  On the server that does not work there are multiple instances and none of them work, so I know its at the server and not the instance level.  At this point I am comparing the MSDASQL Driver versions to see if the version is different

    Any feedback is appreciated.  

    Wednesday, February 27, 2019 4:30 PM
  • I checked the MSDASQL.DLL Driver versions on both servers and they are exactly the same. 

    There are MSDASQL.DLL files in both the (x86) and 64 folders on the C: drive and all them are 6.3.9600.17415 on both the working and non working server

    My System administrator thinks it might be a local security policy and is willing to compare the two and let me know.  I will let you know

    Any feedback is appreciated.

    Wednesday, February 27, 2019 7:34 PM
  • Thank you.

    Thursday, February 28, 2019 9:42 AM
  • I have given up trying to figure out why the MSDASQL provider only works on one of three servers using the ODBC Version 17 driver. I know its a windows thing, but do not have the time.

    I will propose to management to use the MS recommended SQL Native Provider instead.

    Thanks for the help

    
    Wednesday, March 13, 2019 4:28 PM
  • Also I have converted the DSN to using ODBC Driver 11 or 13 whichever is highest on the box
    Wednesday, March 13, 2019 4:50 PM