Retirement of SQL Server Native Client (SNAC) OLE DB provider for linked servers in Azure SQL Manage

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

The SQL Server Native Client (often abbreviated SNAC) OLE DB provider with programmatic identifier SQLNCLI is a legacy Microsoft OLE DB provider for SQL Server that was shipped with SQL Server 2005 (9.x) through SQL Server 2012 (11.x) and then deprecated for use by database application connections. Based on the Support Lifecycle exception SQLNCLI continued to be shipped as a component of SQL Server Database engine up until SQL Server 2019 release, for exclusive use by database engine features like linked servers. In 2018 a next generation of OLE DB provider for SQL Server known by programmatic identifier MSOLEDBSQL was released keeping backwards compatibility with SQLNCLI and adding support for features introduced in newer versions of SQL Server. MSOLEDBSQL is still current and recommended Microsoft OLE DB provider for SQL Server. SQLNCLI was finally removed from SQL Server Database engine with SQL Server 2022 release.

In November 2022 we sent email notifications to all Azure SQL Managed Instance customers using linked server feature announcing that SQLNCLI is also going to be deactivated and retired in Azure SQL Managed Instance. Service update retiring SQLNCLI will be rolled out in all Azure regions during late May and early June this year.

No actions needed for existing linked servers

All existing linked servers with SQLNCLI provider specified will be automatically rerouted to use MSOLEDBSQL provider - you do not have to take any manual actions. Please note that sys.servers system view will keep reporting SQLNCLI as provider for existing linked servers. That’s because the underlying metadata remains unchanged and MSOLEDBSQL provider will be used thanks to dynamic rerouting implemented.

Use MSOLEDBSQL when creating new linked servers

After deactivation of SQLNLCI you should create any new linked servers with MSOLEDBSQL provider specified:

EXEC master.dbo.sp_addlinkedserver @server = N'myNewLinkedServer', @srvproduct = N'', @provider = N'MSOLEDBSQL', @datasrc=N'sqlminame.8b522d9b1fc0.database.windows.net,1433'

sp_addlinkedserver stored procedure will return error message when ‘SQLNCLI’ is passed as a @Provider parameter value.

Important: If you prefer using SQL Server Management Studio (SSMS) wizard for creation of linked servers, make sure to download and install the latest version of SSMS.

To create new linked server using SQL Server Management Studio (SSMS) wizard, choose Microsoft OLE DB Driver for SQL Server from the Provider dropdown list:

MSOLEDBSQL provider.png

Several providers will be removed from the Provider dropdown list in SQL Server Management Studio (SSMS) wizard for creating linked server on Azure SQL Managed Instance since they are deactivated, to avoid any confusion:

Microsoft OLE DB Provider for SQL Server

SQL Server Native Client 11.0

SQL Server Native Client 10.0

Programmatic identifiers of the corresponding providers will not be listed in the Object Explorer tree of SSMS under Server Objects -> Linked Servers -> Providers anymore:

Removed providers.png

Next steps

Change any existing transact-SQL (T-SQL) scripts for creation of linked server objects in Azure SQL Managed Instance to provide MSOLEDBSQL as @Provider parameter, instead of SQLNCLI.