How to use OPENROWSET with MSOLEDBSQL driver?

Question

MSOLEDBSQL driver is installed in my system. I am using the below syntax to connect to a database in a different SQL Server using OPENROWSET:

(in example I am trying to read from the same system)

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=<Computer Name>;Database=<Database Name>;TrustServerCertificate=True;Authentication=ActiveDirectoryIntegrated;'
    , 'SELECT * FROM [database name].[dbo].[table name];'
    ) c;

I am getting the below error:

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Client unable to establish connection".

OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "SSL Provider: The certificate chain was issued by an authority that is not trusted. ".

Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSOLEDBSQL" for linked server "(null)".

Any kind of help is appreciated.

Answer 1

Although, the MSOLEDBSQL documentation recommends Authentication=ActiveDirectoryIntegrated to enable new encryption and certificate validation behavior, I don't think the stricter validation will allow a self-signed certificate, trusted or not.

Try removing the Authentication keyword and instead specify Trusted_Connection=Yes if you are using a self-signed cert.

SELECT c.*
FROM OPENROWSET(
    'MSOLEDBSQL'
    , 'Server=<Computer Name>;Database=<Database Name>;TrustServerCertificate=Yes;Trusted_Connection=Yes;'
    , 'SELECT * FROM [database name].[dbo].[table name];'
    ) c;