SQL Server - 連結伺服器 - 使用帶有 Windows 集成安全性的 OPENROWSET

我看過很多關於使用集成安全性（Windows 身份驗證）的 OPENROWSET 的文章，但我無法讓它為我工作。

使用 SQL Server 身份驗證可以正常工作：

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

但是我嘗試使用 Windows 身份驗證執行相同的查詢，但它不起作用：

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

有人可以發布一個有效的例子嗎？

這是一篇描述 OPENROWSET 用法的好文章。

使用 OPENROWSET 的工作腳本範例 - 請閱讀評論

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

聲明變數以獲取目前實例名稱並將值傳遞給OpenROWSet.

啟用即席分佈式查詢

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

OPENROWSET 使用 windows 身份驗證來獲取數據

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

引用自：https://dba.stackexchange.com/questions/93298