MSOLEDBSQL Syntax – Using OPENROWSET in SQL Server

sql server

We have been using this syntax when using OpenRowset to collect data from other SQL Server instances

SELECT a.* 
FROM OPENROWSET('SQLNCLI11', 
'Server=STARDBS1004\T12;UID=user;PWD=pwd;', 
'select * from [MyTable]') as a

Native Client is deprecated and MS advice is to use latest Microsoft® OLE DB Driver 18 for SQL Server®

I am struggling to find the correct syntax for my OPENROWSET query

This I how think it is supposed to be now:

SELECT a.* 
FROM OPENROWSET('MSOLEDBSQL',
'Data Source=STARDBS1004\T12;Initial Catalog=Staging_DWH;User ID=user;Password=pwd',  
'select * from [MyTable]') AS a

But I get these errors:

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message
"Invalid authorization specification".
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message
"Invalid connection string attribute".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "MSOLEDBSQL" for linked server "(null)" reported an
error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".

Obviously the syntax is wrong, so can anybody help in finding the correct syntax?

Best Answer

OLEDB connections using the MSOLEDBSQL driver should use this format:

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=MyServer;Database=tempdb;UID=test;Pwd=Test;'
    , 'SELECT name FROM sys.databases;'
    ) c;

ConnectionStrings.com is invaluable for obtaining valid connection string details.

Related Question