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.
Best Answer
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 specifyTrusted_Connection=Yes
if you are using a self-signed cert.