Search code examples
sqloledbazure-sql-database

Connect to SQL Azure DB using OpenRowSet from on-premise SQL Server

I am trying to connect to SQL Azure database from an on-premise SQL Server using openrowset, but it is failing with an error.

My query is

SELECT a.* 
FROM OPENROWSET('SQLNCLI', 'Server=sqlazureserver.database.windows.net;Database=dbname;User ID=username;Password=password;Connection Timeout=30;', 'select * from [dbo].[tablename]') AS a;

and the error I get:

Msg 7399, Level 16, State 1, Line 11
The OLE DB provider "SQLNCLI11" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 11
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "(null)".

I am able to successfully connect using linked server, but I do not want to go that route as my connection strings will be dynamic.

Solution

  • From documentation ,I could see open row set is not supported against SQL Azure database as of now.

    enter image description here

    Instead of openrowset,you can use distributed queries to accomplish the same 

    exec ('select * from table') at linkedserver