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.
Best Answer
[Not really an answer as I'm just stating the obvious but I don't have enough rep to comment.]
The four driver files are the ODBC driver, the JDBC driver, and the 32 and 64-bit OLEDB drivers. The ODBC driver is of course the correct one but there's a small check box in one of the installer dialogs which allows you to choose 32-bit and/or 64-bit. Did you select the correct one?
I've just tried this on Windows Server 2012 and Windows 7 and both work exactly the same for me without any extra steps. The driver I have is called
nsqlodbc.dll, version7.0.0.27274, inC:\Windows\System32(and I also have a 32-bit version inC:\Windows\SysWOW64). I can connect from both the 32-bit and 64-bit ODBC Data Source Administrator tools although I got my password wrong several times so that's certainly worth checking.Have you also checked that the advanced options are the same, SSL for instance?
Possibly there's a security setting on your Server box that's preventing the connection but on 2012 Server there didn't appear to be anything by default.