Troubleshooting: 'SQL Server Native Client 11.0' Not Found During Linked Server Creation

pankaj · July 11, 2024, 11:39am

Problem

Sometimes, when creating a linked server through the UI, ‘SQL Server Native Client 11.0’ is missing from the Linked Server Provider options, or when attempting to create a linked server using the SQL query, it will throw an error.

Cause

Typically, this indicates that the SQL Server Native Client (SNAC) is not installed or not properly registered on the server where you are trying to create the linked server.

As the SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS)

OLE DB - SQL Server Native Client | Microsoft Learn

The SQL Server Native Client (often abbreviated SNAC) has been removed from SQL Server 2022 (16.x) and SQL Server Management Studio 19 (SSMS). Both the SQL Server Native Client OLE DB provider (SQLNCLI or SQLNCLI11) and the legacy Microsoft OLE DB Provider for SQL Server (SQLOLEDB) are not recommended for new development. Switch to the new Microsoft OLE DB Driver (MSOLEDBSQL) for SQL Server going forward.

Resolution:

While creating Linked Server using UI

While creating Linked Server using UI instead of using SQL Server Native Client 11.0 we now need to use the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL). All other steps remain the same, and you will still be able to generate the linked server.

Create Linked Server using Code

While creating a linked server using code, instead of using SQLNCLI11, you now need to use MSOLEDBSQL, you can use the following SQL script.
Note: In the code below, Odata_Json is the name of my dataset in the Data Gateway.

--///////////////////////////////////////////////////////////////////////////////////////
--Run below code in SSMS to create Linked Server and use ZappySys Drivers in SQL Server
--///////////////////////////////////////////////////////////////////////////////////////
USE [master]
GO

--1. Configure your gateway service as per this article https://zappysys.com/links?id=10036
--2. Make sure you have SQL Server Installed. You can download FREE SQL Server Express Edition from here https://www.microsoft.com/en-us/sql-server/sql-server-editions-express

--EXEC master.dbo.sp_dropserver @server=N'Zs_Odata_Json', @droplogins='droplogins'

--3. Create new linked server
EXEC master.dbo.sp_addlinkedserver 
	    @server = N'Zs_Odata_Json'  --Linked server name (this will be used in OPENQUERY sql
	  , @srvproduct=N''	
	  , @provider=N'MSOLEDBSQL'
	  , @datasrc=N'LOCALHOST,5000' --//Machine / Port where Gateway service is running
	  , @provstr=N'Network Library=DBMSSOCN;'
	  , @catalog=N'Odata Json' --Data source name you gave on Gateway service settings

--4. Attach gateway login with linked server
EXEC master.dbo.sp_addlinkedsrvlogin 
	    @rmtsrvname=N'Zs_Odata_Json'  --linked server name
	  , @useself=N'False'
	  , @locallogin=NULL
	  , @rmtuser=N'admin' --enter your Gateway user name
	  , @rmtpassword='########'  --enter your Gateway user's password
GO

--5. Enable RPC OUT (This is Optional - Only needed if you plan to use EXEC(...) AT YourLinkedServerName rather than OPENQUERY
EXEC sp_serveroption 'Zs_Odata_Json', 'rpc', true;
EXEC sp_serveroption 'Zs_Odata_Json', 'rpc out', true;
--Disable MSDTC - Below needed to support INSERT INTO from EXEC AT statement
EXEC sp_serveroption 'Zs_Odata_Json', 'remote proc transaction promotion', false;
--Increase query timeout if query is going to take longer than 10 mins (Default timeout is 600 seconds)
--EXEC sp_serveroption 'Zs_Odata_Json', 'query timeout', 1200;
GO

--6. Run some sample query (use OPENQUERY) - Advantages >> FROM clause allowed so can join with other tables, SELECT INTO allowed so you can create new table and insert in one step. Disadvantages = Variables not allowed in SQL so you have to use ugly Dynamic SQL approach to use variables.
--SELECT * FROM OPENQUERY([Zs_Odata_Json],'select * from $')

--OR--

--6. Dynamic query approach (use EXEC AT) - Advantages >> More than 8000 chars allowed in SQL sent to driver. Also you can use dynamic SQL (build variable) 
--DECLARE @sql nvarchar(max)
--SET @sql='select col1, col2 from MyTable' --//add dynamic stuff here -- Comment INSERT clause just to execute without loading into table
--INSERT INTO (col1, col2)
--EXEC(@sql) AT [Zs_Odata_Json]

--You can create table structure using below technique (Use TOP 0 with SELECT INTO)
--SELECT top 0 col1, col2 into some_table FROM OPENQUERY([Zs_Odata_Json],'select col1, col2 from $') --this is the quickest way to create new table to match query (we do not recommend select * for all columns rather than use column names so order is retained. (i.e. select col1, col2). To generate column list you can Edit Data source on Gateway UI > Go to Preview Tab > Select Table from drop down to auto generate query with column names

Conclusion

This comprehensive guide should help you create the linked server in SQL Server using the Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) provider. For further assistance, please contact our support team via chat on our website.