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.
Best Answer
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.
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:
If you see this error, you need to configure Kerberos between the servers:
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.