Working Linked Server Query fails in sp_send_dbmail

Take the following example:

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

This is giving the following error (even when ran under a windows credential that has sysadmin privileges to both servers):

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

Now, the linked server in question is configured as follows (note the use of @useself & no mappings):

```
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 @serv

Well, after a dozen phone calls to Microsoft via Paid Support & a 1.5 hour conversation with their connectivity team & 3 weeks of traces, procmon analysis and what not i was AMAZED to be told that this is a known issue:

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

Basically, Microsoft advised me to create a DSN using windows authentication & the SQL native client. I then had to create a 2nd linked server using that ODBC DSN using NO security context - this works & somehow executes the remote query as the correct user.

Microsoft won’t be fixing this as it’s a small issue in the grand scheme of things.

They said that they'll get back to me if they find a fix - but i won’t be holding my breath.

StackExchange Database Administrators Q#41470, answer score: 4