SQL Server - Linked Server - Using OPENROWSET with windows integrated security

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
---

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

StackExchange Database Administrators Q#93298, answer score: 5