1

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.

1
  • The middle message in that list is your key: OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "SSL Provider: The certificate chain was issued by an authority that is not trusted. " This error can occur for a number of reasons, but the core is that (1) you are using encrypted communications, and (2) the certificate you are using does not meet the security criteria SQL Server requires. Likely, this was a local, self-issued certificate, which SQL Server is reluctant to trust, but more detail on the certs and settings would be needed. Sep 23, 2019 at 20:50

1 Answer 1

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;
6
  • This is what I get after executing the below script: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSOLEDBSQL" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "MSOLEDBSQL" for linked server "(null)".
    – kish
    Sep 24, 2019 at 10:40
  • @kish, the query works for me. What SQL version? Are you running the query as a sysadmin role member?
    – Dan Guzman
    Sep 24, 2019 at 11:15
  • I have Microsoft SQL Server 2014 (SP1-GDR) (KB3194720) - 12.0.4232.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1). The user running the query has public and sysadmin roles. Am I missing something else?
    – kish
    Sep 25, 2019 at 6:24
  • @kish, I just successfully tested with SQL 2014 SP3 (doubt if the SP matters). Run a trace on the remote server to capture error_reported event. Maybe that will provide more error details. For troubleshooting, you could also try query like SELECT @@SERVERNAME; to rule out a problem with the query itself.
    – Dan Guzman
    Sep 25, 2019 at 11:52
  • I checked the servername, thats correct in the query. I am still getting the same error. Is it because SSMS is 32 bit in my system and I have the 64 bit version of MSOLEDBSQL installed in my system? Tried installing 32 bit driver in my system, looks like not supported.
    – kish
    Sep 26, 2019 at 9:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.