0

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)".

Saqlain Mushtaq
  • 153
  • 3
  • 9
  • 2
    Out of interest, why are you using SQLNCLI when it is deprecated? Why not use MSOLEDBSQL? – Thom A Oct 07 '21 at 09:12
  • 2
    /waveshand Those are not the [connection string parameters](https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/using-connection-string-keywords-with-sql-server-native-client) you're looking for. Try `UID` and `PWD` instead of `User ID` and `Password`. – AlwaysLearning Oct 07 '21 at 09:23

1 Answers1

0

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
Saqlain Mushtaq
  • 153
  • 3
  • 9