How do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server?

Question

On SQL Server 2016, how do I guarantee using Microsoft ODBC Driver 13 for SQL Server in a linked server? I don't mind there being another layer in there, such as the MSDASQL provider, but I do want the ODBC Driver 13 for SQL Server to be what ends up making the connection to the remove instance.

With testing on SQL Server 2016 RC2 on Windows 2016 Technical Preview 4, both fresh installs on a blank VM, I can use odbcad32 to see the "ODBC Driver 13 for SQL Server", version 2015.130.1300.275, with file name MSODBCSQL13.DLL.

The version and the filename are identical in the 64-bit odbcad32 screen as well as the 32-bit odbcad32 screen from c:\windows\syswow64, so I do not believe it to be a 32 vs 64 bit issue at this time (particularly since the driver was installed by the SQL Server 2016 RC2 install).

On SQL 2014, for instance, to use Native Client 11, I would use

EXEC master.dbo.sp_addlinkedserver @server = N'LinkName', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'YourTargetServer'

On SQL 2016 RC2, when I try

EXEC master.dbo.sp_addlinkedserver @server = N'LinkName', @srvproduct=N'sql_server', @provider=N'MSODBCSQL13', @datasrc=N'YourTargetServer'

The linked server creates just fine, but when I try to use it, I get:

Msg 7403, Level 16, State 1, Line 7
The OLE DB provider "MSODBCSQL13" has not been registered.

I didn't have any luck trying provider names of ODBC Driver 13 for SQL Server Microsoft ODBC Driver 13 for SQL Server

or even trying combinations of that that the MSDASQL provider name, from

Using Always Encrypted with the Windows ODBC Driver and sp_addlinkedserver (Transact-SQL)

And even looking through the registry didn't reveal a provider name I recognized.

Note that using odbcad32 to create a System DSN does, in fact, test successfully when I choose ODBC Driver 13 for SQL Server, so I know it can work.

Ideally, I just want a sample sp_addlinkedserver command that specifies the new ODBC driver in it.

Answer 1

It is true that linked servers are dependent on the OLEDB interface but the ODBC driver can be safely used between SQL Server servers. (There's a misconception in the wild that such a configuration is unsupported by Microsoft; that is a myth.)

To use ODBC Driver 11, 13, or 13.1 using Linked Servers.

EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'', @provider=N'MSDASQL', @provstr=N'DRIVER={ODBC Driver 13 for SQL Server};MultiSubnetFailover=Yes;ApplicationIntent=READONLY;Trusted_Connection=Yes;SERVER=FqnServerName;'

The ODBC 13.1 driver is an update and still uses the "ODBC Driver 13 for SQL Server" driver name, not "ODBC Driver 13.1 for SQL Server".

I have found that using the fully-qualified server name seems, in my testing, to be more reliable, especially when connecting to AGs, but you can always try just the server name.

You can test inter-server Kerberos connectivity but using an OPENROWSET. One way I test Kerberos is using a simple query run from the source server:

SELECT * FROM OPENROWSET(N'MSDASQL', N'Driver={ODBC Driver 13 for SQL Server};Server=FqnServerName;Database=master;Trusted_Connection=yes;MultiSubNetFailover=yes;', N'SELECT * FROM [sys].[databases]') AS [Source]

If you see this error, you need to configure Kerberos between the servers:

OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".

The 2012 Native Client is now (over) five years old, has been officially deprecated by Microsoft, and, if you want to align to Microsoft's roadmap, you will want to use ODBC. Consider that in 2014, the 2012 Native Client was only two years old. Now consider all the features added to SQL Server 2016 and SQL Server 2017 that are not supported by the 2012 Native Client, and you have a tremendous use case for using the ODBC Driver 13.1 for SQL Server.

Since middle of last year, my organization has been migrating to SQL Server 2016 and we are testing SQL Server 2017 (CTP 2.1). I have been migrating all of our linked servers (at least those pointing to SQL Server 2016 or SQL Server 2017) to use ODBC Driver 13 (or, more accurately, 13.1) and have yet to see any serious issues in over a year of testing against SQL Server 2016.

(In the interest of full disclosure, there is an issue with Graph Tables using linked servers; I reported the issue to Microsoft based on a linked server using ODBC 13.1, and Microsoft confirmed Graph Tables are not supported over linked servers (of any kind) in SQL Server 2017.)

Let me know if you run into any issues and I'll help you along.

Answer 2

It's not possible to use just ODBC. Linked servers use Distributed Queries which is dependent on the OLEDB interface. This is not an optional or interchangeable API. Below that API can be any OLEDB, ODBC or even JDBC provider so long as its implementation supports the OLEDB interface. For example, there is an OLEDB provider for ODBC which is one of the most common ways to run DQ against non-SQL Server data sources. SQL Server natively supports OLEDB so there's no need for the extra layer. Most/all of this information can be found in Books Online or MSDN.

Why do you need to use only ODBC? If want it for Always Encrypted, suggest you log a feature request via https://connect.microsoft.com/SQLServer/feedback/. I don't know if the scenario is even supported where you run AE through DQ. The "calling SQL Server" essentially becomes the client to the "target SQL Server" but you don't fully control the client since DQ isn't something you can modify.

If it's for some other reason, share details here so folks help come up with alternate solutions.