Microsoft ODBC Driver 13.1 for SQL Server and OPENROWSET

Question

This is a simple question that I've found zero articles on.

How do I utilize the ODBC 13.1 driver using OPENROWSET? I'm running the OPENROWSET on SQL Server 2016 going to SQL Server 2014, and vice versa.

Thank you very kindly!

Answer 1

Have you installed ODBC 13.1?

References: Microsoft® ODBC Driver 13.1 for SQL Server® - Windows

Using OPENROWSET instead of a Linked Server

---

---

Kalman Toth Database & OLAP/AI Architect SQL Server 2016 Database Design
New Book : Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

Answer 2

The answer is that you don't. At least not to access another SQL Server instance.

To wit, while one hand of Microsoft has deprecated access to SQL Server through OLE DB, another hand of Microsoft says that linked server access between two SQL Servers over MSDASQL is not supported.

That is, a linked server always uses OLE DB. To use an ODBC driver with a linked server, you need to use MSDASQL, that is OLE DB over ODBC. So in your case, you could do that, but as I noted, it is unsupported.

Answer 3

My organization is moving away from linked servers as a result of that and toward OPENROWSET, to avoid the overhead of OLEDB and linked servers. And as we use AGs extensively, the ODBC 13.1 driver is the direction we want to take. Thank you for the response!

Answer 4

Thank you. I had missed the fact that MSDASQL could be used without DSN entries. I ended up using Microsoft Excel to build out my connection string. For documentation purposes:

"DRIVER={ODBC Driver 13 for SQL Server};Server=ServerA;Trusted_Connection=yes;ApplicationIntent=READONLY;MultiSubnetFailover=Yes;"

It works perfectly with MSDASQL:

SELECT * FROM OPENROWSET(N'MSDASQL', N'DRIVER={ODBC Driver 13 for SQL Server};Server=ServerA;Trusted_Connection=yes;ApplicationIntent=READONLY;MultiSubnetFailover=Yes;', N'SELECT ...') AS [RemoteQuery];

Answer 5

The only one difference between linked servers and OPENROWSET is that with linked server the details about the remote data source is registered in advance, and with OPENROWSET the information is given ad hoc. Both use OLE DB. Generally, OPENROWSET is considered less secure, since this means that any user can use any OLE DB provider which is installed on the server. 
However, I have been told that to be able to use ApplicationIntent=ReadOnly, you need to use ODBC. But as I said, the DQ engine is not supported with MSDASQL + ODBC for connections to other SQL Server instances.