February 3, 2019 at 2:18 pm
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.
Jeff
February 4, 2019 at 7:01 am
Below is the error when testing 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 is appreciated.
Jeff
February 4, 2019 at 1:01 pm
I just found out that one instance connects just fine using the ODBC 17 for SQL Server Driver. I have begun comparing the two instances to see the differences, but I installed them both and they should be exactly the same
Jeff
February 4, 2019 at 3:18 pm
jayoub - Monday, February 4, 2019 1:01 PMI just found out that one instance connects just fine using the ODBC 17 for SQL Server Driver. I have begun comparing the two instances to see the differences, but I installed them both and they should be exactly the same
Are the instances on different servers?
Sue
February 5, 2019 at 4:29 pm
Thanks for replying
Yes they are on different servers. I actually have two servers\instances that do not work and one that does work and each is a different server.
I started comparing the difference and found
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.
Any feedback would be appreciated.
Jeff
February 5, 2019 at 4:38 pm
jayoub - Tuesday, February 5, 2019 4:29 PMThanks for replyingYes they are on different servers. I actually have two servers\instances that do not work and one that does work and each is a different server.
I started comparing the difference and found
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.Any feedback would be appreciated.
There are different patches, updates needed at the OS level for it to work so you probably want to check that as well. Don't assume they are okay.
Sue
February 5, 2019 at 5:34 pm
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.
Jeff
February 6, 2019 at 3:05 pm
Yes thanks I will check the Windows Patch level. We are experiencing yet another issue.
Developers are now complaining that while using sqlplus or Toad and the new ODBC driver the XML columns are not returning wen they do select * from table - 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.
He is showing me the error in Toad at this point.
Any feedback is appreciated.
Jeff
February 6, 2019 at 4:02 pm
jayoub - Wednesday, February 6, 2019 3:05 PMYes thanks I will check the Windows Patch level. We are experiencing yet another issue.
Developers are now complaining that while using sqlplus or Toad and the new ODBC driver the XML columns are not returning wen they do select * from table - 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.
He is showing me the error in Toad at this point.
Any feedback is appreciated.
ORA errors are Oracle errors so I would guess Its really not related to the SQL Server driver. They wouldn't be using a SQL Server drivers with something like SQL Plus to connect to Oracle.
The invalid identifier error can come up if the column name is a reserved word or some tools can't handle binary xml and you need to cast those to clobs, etc. Xmltype also had some odd issues. And that's all I care to remember about it.
Sue
February 6, 2019 at 6:38 pm
Thanks for the feedback,
In this case we are querying an SQL Database from an Oracle database
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 and 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.
Jeff
February 6, 2019 at 6:53 pm
jayoub - Wednesday, February 6, 2019 6:38 PMThanks for the feedback,
In this case we are querying an SQL Database from an Oracle databaseThe 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 and 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.
not quite - when going from Oracle to SQL you are using the SQL Server ODBC driver - from SQL to Oracle you are using the Oracle driver - not the same thing at all.
Toad also has issues with XML types - there are workarounds but I don't have time to search for them.
And over a dblink there are also issues - see https://stackoverflow.com/questions/6268136/moving-xml-over-a-dblink from some possibilities
February 8, 2019 at 1:30 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.
Thanks to: Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
for the information
Jeff
February 8, 2019 at 3:50 pm
Thanks for the help on the XML datatype issue.
Any thoughts on why the Linked server would work for only about 10 minutes and quits when using the ODBC Driver 17. is there anything you can think of that i could compare. I do have one instance that does not have that condition.
Your feedback is appreciated.
Jeff
February 27, 2019 at 9:36 am
So I am OK with the XML issue, but I still have problems using a linked server with the ODBC Driver 17.
Recap: When I create a linked server from SQL Server to an ODBC Driver Version 17 it works only after restarting the instances and only works for about 15 minutes - The error is in the initial post.
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 update and insert 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 while allow inprocess is checked, 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.
Jeff
February 27, 2019 at 12:36 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.
Jeff
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply