2

I have seen many articles regarding OPENROWSET using integrated security (Windows Authentication), but I could not make it work for me.

It is working fine using SQL Server authentication:

 select *
 FROM
 OPENROWSET('SQLOLEDB',
 'myserver';'monitor';'#J4g4nn4th4#',
 'SELECT GETDATE() AS [RADHE]')

But the same query I am trying to run using windows authentication, and it is not working:

select *
 FROM
 OPENROWSET('SQLOLEDB',
'myserver';'Integrated Security=SSPI',
 'SELECT GETDATE() AS [RADHE]')

Can someone please post an example that works?

Here is a good article that describes OPENROWSET usage.

Examples of working scripts using OPENROWSET - please read comments

 ----------------------------------------------------------------
 -- this works - linked server REPLON1
 ----------------------------------------------------------------

select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=REPLON1;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

 select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=REPLON1;Trusted_Connection=yes;',
 'SET FMTONLY OFF select * from sys.dm_exec_requests')


 SELECT a.*
FROM OPENROWSET('SQLOLEDB', 'server=replon1;Trusted_Connection=yes;', 'SET
FMTONLY OFF select * from sys.dm_exec_requests') AS a
WHERE a.session_id > 50
ORDER BY a.start_time desc



  ----------------------------------------------------------------
 -- this does not work - when using windows authentication
 -- apparently because windows server 2003 and windows server 2012 have problems connecting - related to SID
 -- it works fine using SQL Server Authentication
 ----------------------------------------------------------------

 select *
 FROM
 OPENROWSET('SQLOLEDB',
'Server=SQLREPLON1\REP;Trusted_Connection=yes;',
 'SELECT GETDATE() AS [RADHE]')

-- Msg 18456, Level 14, State 1, Line 1
--Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.



 ----------------------------------------------------------------
 -- this works - linked server SQLREPLON1\REP
 ----------------------------------------------------------------

 select *
 FROM
 OPENROWSET('SQLOLEDB',
 'SQLREPLON1\REP';'monitor';'#J4g4nn4th4#',
 'SELECT * from SAReporting.DBO.tblStockLedgerMovement')
  • Error you are facing ? – aasim.abdullah Feb 19 '15 at 11:24
  • So what happens if you use the first query in the article you have linked to? select a.* from openrowset('SQLNCLI', 'Server=myserver;Trusted_Connection=yes;', 'SELECT GETDATE() AS [RADHE]') as a – Mikael Eriksson Feb 19 '15 at 12:16
  • @MikaelEriksson Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. – Marcello Miorelli Feb 19 '15 at 12:23
  • I guess you have got the typical problem with Kerberos: double hop. – Jens W. Feb 21 '15 at 9:29
3

Declare variable to get current instance name and pass value to OpenROWSet.

Enable Ad Hoc Distributed Queries

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

OPENROWSET using windows authentication to fetch data

DECLARE @InstanceName VARCHAR(200),
    @sql NVARCHAR(MAX)
SELECT  @InstanceName = ( SELECT @@servername  )


SELECT  @sql = 'select a.* from openrowset(''SQLNCLI'', ''Server='
        + @InstanceName
        + ';Trusted_Connection=yes;'', ''select * from Databasename.dbo.TableName'') as a'

EXEC sp_executeSQL @sql

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.