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
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.