OLE DB provider "SQLNCLI11" for linked server "DB_Name" returned message "Client unable to establish connection".

Question

We have a DTS package running on one of the production server. This is very critical package and not suppose to fail. Package running every night and completed most of the days and failed occasionally (1-2 a month) with the following error message. The package running through the application and establishing connection from one production server to other. Need urgent help.

TIA

The package DTSLoad.dtsx failed.
Execution Path: C:\Containter\Package
Error: OLE DB provider "SQLNCLI11" for linked server "DB_Name" returned message "Client unable to establish connection".
Possible failure reason: Problem with the query. "ResultSet" propety not set correctly, parameter not set correctly or connection not established correctly

Answer 1

If the situation is urgent, you should absolutely have included what troubleshooting we have performed.

All we can tell now is that you have a linked server you cannot connect to. Maybe it is that simple that the remote data source is down? I mean that is the first thing that comes to mind. There could also be network issues etc.

You say that the package runs every night. By what means? Agent job? But you say "through the application". But what does that mean?

What does "SELECT @@version" return for the local SQL Server instance? On the remote instance?

Answer 2

Thanks for your response.

Job process - scheduler => job sent to first production server A - calls out SSI package to link with second production server B for acknowledgment and response back. Communication issue when first production server A makes SQL call to database. (Error. SQLNCLI11 " for linked server "DB_Name" returned message "Client unable to establish connection"). Error reported back to scheduler.

I hope that helps.

Server A - SQL 2014 SP3, CUR and GDR
Server B - SQL 2017, RTM, CU28

Answer 3

Hi @HP1979 ，

It is just a rsuggestion.

From the official doc:Support Policies for SQL Server Native Client

SQL Server Native Client 11.0 supports connections to, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012 (11.x), SQL Server 2014 (12.x), and Azure SQL Database.

Just substantial scenarios within SQL Server still depend on OLE DB, only when used by SQL Server components, the SQL Native Client 11.0 provider is supported in SQL Server 2012 through 2019

So, if allowed, it is recommended that use new Microsoft OLE DB Driver for SQL Server(MSOLEDBSQL)

The previous Microsoft OLE DB Provider for SQL Server (SQLOLEDB) and SQL Server Native Client OLE DB provider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work.

And check your TLS setting：https://support.microsoft.com/en-us/topic/kb3135244-tls-1-2-support-for-microsoft-sql-server-e4472ef8-90a9-13c1-e4d8-44aad198cdbe
In some cases, when TLS1.2 updates are missing on the client or the server will experience error

Or you can give error log for more information

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

So a possible reason is that there are some issues in the communication with the AD to validate the login.

But rather than guessing, maybe we should try to find out what more exactly what reason the OLE DB claims for the login failure.

To illustrate, here is a sample message that I get when I'm making a failed attempt to access a linked server on one of my servers:

OLE DB provider "MSOLEDBSQL19" for linked server "SOMMERWALD" returned message "Client unable to establish connection".
OLE DB provider "MSOLEDBSQL19" for linked server "SOMMERWALD" returned message "SSL Provider: The certificate chain was issued by an authority that is not trusted.

".

Msg 7303, Level 16, State 1, Line 25

Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL19" for linked server "SOMMERWALD".

In this case, it is the second message that tells me why the connection fails.

Now, it appears that you only get the first one back. I don't know SSIS, but you could check the logs more carefully to see if there is a second message.

Else an alternative is to set up an extended event session like this:

IF EXISTS (SELECT * FROM sys.server_event_sessions WHERE name = 'Exceptions')
   DROP EVENT SESSION Exceptions ON SERVER
go
CREATE EVENT SESSION Exceptions ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.session_server_principal_name,
           sqlserver.client_hostname,
           sqlserver.client_app_name,
           sqlserver.session_id,
           sqlserver.database_name,
           sqlserver.sql_text,
           sqlserver.tsql_frame,
           sqlserver.tsql_stack)
--    WHERE  severity >= 11
--       AND NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_app_name, 
--                                    '%SQL Server Management Studio%')
)
ADD TARGET package0.event_file (SET FILENAME = N'Exceptions.xel',
                                    MAX_FILE_SIZE = 1, 
                                    MAX_ROLLOVER_FILES = 5)
WITH (MAX_MEMORY             = 512 KB,
      EVENT_RETENTION_MODE   = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY   = 30 SECONDS,
      MAX_EVENT_SIZE         = 0 KB,
      MEMORY_PARTITION_MODE  = NONE,
      TRACK_CAUSALITY        = OFF,
      STARTUP_STATE          = ON)
GO
ALTER EVENT SESSION Exceptions ON SERVER STATE = START

This will capture all exceptions and informational message that are produced on the server, but you could add a filter, for instance for the login in question.

I discuss this session and how you view it in more detail in this article on my web site: https://www.sommarskog.se/Short%20Stories/trace-exceptions.html. In the article, I have a filter on severity, but I removed it here, since the message from the OLE DB providers are not raised as error, but only informational errors.

The article includes a view to make it easy to consume the results of the XE session.