Linked Server suggestions not working with OLE DB *Driver* (not Provider)

SoundOfMindSoundOfMind Posts: 4 Bronze 1
edited September 4, 2023 4:22AM in SQL Prompt
Late last year, Microsoft announced that the OLE DB Provider was being stood down, and we should all move back to the OLE DB Driver, which had been undeprecated (Microsoft OLE DB Driver for SQL Server - OLE DB Driver for SQL Server | Microsoft Learn).

I've tried a simple test: created three Linked Servers to the same remote server using the same login:
  1. Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL);
  2. Default settings using the "SQL Server" option in SSMS (I believe this uses the SQLNCLI driver behind the scenes);
  3. Microsoft OLE DB Provider for SQL Server (SQLOLEDB).
SQL Prompt works with (2) and (3) but not with (1), which is the new driver we should be using. This is awkward. Anyone have any thoughts?

Host SQL Server version: 2019 CU22
Remote Server version: 2014 SP3
SQL Prompt version: 10.13.13.2147
SSMS version: 18.12.1
Security Context: specific remote server login mapping
Remote login permissions: VIEW ANY DATABASE, VIEW ANY DEFINITION

Just for completeness, I subsequently granted the remote login sysadmin privileges on the remote server... no change to the above results, so it appears not to be a permissions issue.
Tagged:
· Share on Twitter

Answers

Sign In or Register to comment.