SQLNCLI syntax error when using OPENROWSET in SQL Server 2019

Question

SQLNCLI syntax when using OPENROWSET

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

SELECT a.* FROM OPENROWSET('SQLNCLI', 'Server=MyServer;Database=Tasks; User Id=sa;Password=myPassword;','SELECT * FROM Patients') As a;

I am facing following some errors.

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid authorization specification". OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Invalid connection string attribute". The OLE DB provider "SQLNCLI" for linked server "(null)" reported an error. Authentication failed. Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "(null)".

Answer 1

I resolved my error using following query.

I replaced SQLCLI with MSDASQL and wrote driver name as SQL Server.

select a.* from openrowset('MSDASQL', 'Driver={SQL SERVER}; Server=MYINSTANCE;UID=mySQLUser; PWD=*******;', 'select * from sys.databases') as a