2

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.

  • This sounds like you don't have the correct bit-version of the ODBC driver installed. When you check the odbc driver using the "ODBC Data Sources" control panel applet, are you using the "64 bit" version, which is the only one supported by SQL Server 2016? – Max Vernon Apr 14 '16 at 1:48
  • @MaxVernon - both the 64-bit odbcad32, and the 32-bit odbcad32 from c:\windows\syswow64 show the ODBC Driver 13 for SQL Server on the Drivers tab with the same version number, which was installed when I installed SQL 2016 RC2. I've updated the question for clarity, thank you. – Anti-weakpasswords Apr 14 '16 at 14:23
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.

  • Thank you! The critical part of this answer is "Use the fully qualified domain name", i.e. "server.domain.tld", not just "server" or the IP address and port (neither of the latter worked at all in my environment). I would note that I use ApplicationIntent=READWRITE, since our linked servers make data changes as well as read. – Anti-weakpasswords Jan 9 '18 at 4:44
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.

  • I did try using the MSDASQL provider name to get to the ODBC driver, and I would be ok with an answer that had an intermediate layer between the linked server itself and the ODBC driver. I want to use it because The commercial release of Microsoft SQL Server, codename “Denali,” will be the last release to support OLE DB. and because Microsoft is abandoning the old Native Client in favor of the new client. I also want to be able to use AE if it's available later – Anti-weakpasswords Apr 14 '16 at 17:56
  • Yeah, that's a weird state. You can file a separate suggestion/request on connect about OLEDB. Until something changes there, you don't really have a lot of options if you need linked servers. It will continue to be available and work for regular TSQL so that part is largely safe. It's the new stuff that is unknown. Also, I would recommend against your linked server use of Always Encrypted. You'll either end up with a lot of extra work to ensure the key really is locked down to the calling instance or you'll have a false sense of security and the key is accessible beyond the calling instance. – SQLmojoe Apr 15 '16 at 0:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.