Sql-server – SQL Native Client 11 – SQL Server 2017

native-clientoledbsql serversql-server-2017

We currently connect to SQL Server 2014 with the Native Client without any issues.

However, the SQL Server is being upgraded to SQL 2017. I have been told that SQL Native Client will no longer work for connecting, and we need to install the OLE DB driver instead.

Is this correct? I have not found much online other than the Native Client is deprecated and also this article on the lifecycle.

What impact would this have on existing .Net applications and SSIS packages that connect to the database.

Here is a sample connection from an application…

connectionString="metadata=res://*/LoggingModel.csdl|res://*/LoggingModel.ssdl|res://*/LoggingModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=myServer;initial catalog=DBLogging;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient"

Best Answer

Microsoft has deprecated the SQL Server Native Client (NCLI) driver, however SQL Server itself uses a wire-protocol, TDS that is independent of the driver used by the client.

NCLI will continue to work for the forseable future, however no new work will be conducted by Microsoft on the NCLI driver.

Pedro Lopez talks about the announcement, and subtle un-deprecation here. Also included are some sample connection string changes that will help migrate over to the MSOLEDBSQL driver.

Related Solutions

Sql-server – How to prevent or notice automatic connection retry in SQL Server and OLE DB

You can check if the temporary object exists using this:

IF OBJECT_ID('tempdb..#sometablename') IS NULL
    CREATE TABLE #sometablename ...

Another thing you could do is check the connection time for the current connection:

SELECT connect_time
FROM sys.dm_exec_connections
WHERE session_id = @@SPID

However, I'm having a hard time believing that the client disconnects without throwing errors. I would try to catch those errors first.

Sql-server – Reporting Services will not connect to SQL Server

_{Community Wiki answer to document the solution added by the question author in a comment:}

The old SQL Server was listening on a port other than 1433.

Once I used the IP connection string with the right port, all was well.

Best Answer

Related Solutions

Sql-server – How to prevent or notice automatic connection retry in SQL Server and OLE DB

Sql-server – Reporting Services will not connect to SQL Server

Related Question