sp_send_dbmail 中的工作連結伺服器查詢失敗

舉個例子：

EXEC msdb.dbo.sp_send_dbmail 
@recipients = 'me@whatever.co.uk' ,
@query = 'SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE' ,
@attach_query_result_as_file = N'True' ,
@query_attachment_filename = 'test.txt' ,
@subject = 'test' ,
@body = 'test' ,
@body_format = 'HTML' ;

這會出現以下錯誤（即使在對兩台伺服器都具有系統管理員權限的 Windows 憑據下執行）：

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERA" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "LINKEDSERVERA" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correc
t and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
HResult 0xFFFF, Level 16, State 1
SQL Server Network Interfaces: Error getting enabled protocols list from registry [xFFFFFFFF]. 

現在，有問題的連結伺服器配置如下（注意使用@useself& 沒有映射）：

EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVERA', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVERA',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'use remote collation', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'LINKEDSERVERA', @optname=N'remote proc transaction promotion', @optvalue=N'true'

**這是它變得時髦的地方：**如果我在 SSMS 中手動執行以下命令（即不在 內sp_send_dmail），則查詢執行良好！

SELECT TOP 10 * FROM LINKEDSERVERA.DATABASE.dbo.TABLE

所以連結伺服器正在工作，只是sp_send_dmail它有問題。

所以接下來我認為這是 SQL Server 服務（包括代理）使用的憑據有問題 - 這兩者都作為DOMAIN\SQL服務帳戶執行 - 如果我以該服務帳戶身份登錄 MSSMS 並手動執行查詢 - 再次它有效，因此服務帳戶絕對有權在連結伺服器上執行查詢。

我已經仔細檢查了本機客戶端（在兩台伺服器上）上啟用的協議，兩台伺服器上都明確啟用了 TCP，正如我所說，它在不執行時工作正常sp_send_dbmail。

為了確認sp_send_dmail以服務帳戶身份執行查詢，我執行了以下命令（注意使用SYSTEM_USER）：

EXEC msdb.dbo.sp_send_dbmail 
@recipients = 'me@whatever.co.uk' ,
@query = 'SELECT SYSTEM_USER' ,
@subject = 'test' ,
@body = 'test'

這返回了DOMAIN\SQL憑證。

錯誤的最後一部分提到了一些關於系統資料庫的內容，但我不明白為什麼這只會產生影響sp_send_dbmail（我也不能 100% 確定需要在系統資料庫中檢查什麼）——我能找到的唯一一篇文章是關於 SQL 2005 和SQL 2008 R2 中的關鍵位置似乎不同？

**我的問題：**當這個連結伺服器查詢在 sp_send_dbmail 之外工作正常時，如何讓它在 sp_send_dbmail 中工作？

PS：像這樣的文章通過在連結伺服器上提供不同的憑據來解決這個問題 - 這不是一個選項，因為這個連結伺服器被數百個其他跨伺服器查詢使用並且它們都工作正常。

類似問題：

• 建議將憑據添加為伺服器上的本地管理員和使用者 - 嘗試過 - 不起作用
• 通過使用不同的憑據來訪問遠端伺服器來解決這個問題——這對我們來說並不是一個真正的選擇
• 建議檢查有問題的密鑰 - 我的 SQL Server 上沒有此密鑰

2013 年 5 月 17 日編輯： 我們在兩週後的 5 月 3 日向 Microsoft 提出了付費支持請求，但他們仍在努力解決這個問題，他們現在已經向他們的“SQL Server 連接團隊”提出了請求，並且我除了網路流量記錄等之外，還必須從兩台伺服器向他們發送 SQL 跟踪資訊——如果微軟回复我們，將更新問題並提供答案。

好吧，在通過付費支持給 Microsoft 打了十幾通電話，與他們的連接團隊進行了 1.5 小時的對話，以及 3 週的跟踪、procmon 分析以及被告知這是一個已知問題時，我很驚訝：

https://connect.microsoft.com/SQLServer/feedback/details/753426/dbmail-fails-when-using-a-linked-server-query

基本上，微軟建議我使用 Windows 身份驗證和 SQL 本地客戶端創建一個 DSN。然後，我不得不使用沒有安全上下文的 ODBC DSN 創建第二個連結伺服器 - 這有效並且以正確的使用者身份執行遠端查詢。

微軟不會解決這個問題，因為它是大計劃中的一個小問題。

他們說，如果他們找到解決辦法，他們會回复我——但我不會屏住呼吸。

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