Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

Here is where it gets funky: If I run the following in SSMS manually (i.e. not within sp_send_dmail) the query runs fine!

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

So the linked server IS working, its just sp_send_dmail thats having trouble with it.

So next I assumed it was a problem with the credential being used by the SQL Server services (including agent) - both of these are running as a DOMAIN\SQL service account - If I login to MSSMS as that service account and run the query manually - again it works, so the service account definitely has the permissions to run the query over the linked server.

I have double checked the protocols enabled on the native client (on both servers), TCP is definitly enabled on both servers and as I say, it works fine when not ran within sp_send_dbmail.

To confirm sp_send_dmail was executing the query as the service account I ran the following (note the use of SYSTEM_USER):

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

This returned the DOMAIN\SQL credential.

The last part of the error mentions something about the registry but I don't understand why that would only be effecting sp_send_dbmail (nor am I 100% certain on what needs checking in the registry) – the only article I could find was for SQL 2005 and the key location seems to be different in SQL 2008 R2?

My question: how do I get this linked server query to work in sp_send_dbmail when it works fine outside sp_send_dbmail?

PS: articles like this get round the issue by providing different credentials on the linked server - this isnt an option as this Linked Server is used by hundreds of other cross-server queries and they are all working fine.

Similar Issues:

Edit 17/05/2013: We raised a paid support request with Microsoft on this May 3rd, two weeks later and they're still struggling to figure it out, they've now raised it with their "SQL Server Connectivity Team" and ive had to send them SQL traces from both servers in addition to network traffic recordings etc - will update question with answer if & when Microsoft get back to us.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer

Create a temp table on the local server, load the results of the query into the temp table and execute the sp_send_dbmail @query on the temp table.

Edit: Sorry, this is not a solution to get the sp_send_dbmail function to work across the linked servers but could provide a work around.

share|improve this answer

DB mail is processed by an external service: DatabaseMail90.exe. This service connects to the engine using the DOMAIN\SQL credential. This context executes the query specified in your sp_send_dbmail call. As such the linked server is not reached under the service account, but under an impersonation context of the application connected (DatabaseMail90.exe), which happens to be the same account as the service account. I don't know what are the exact settings DatabaseMail90.exe uses, but try peeking with Profiler at how it connects and see if you discover anything relevant.

share|improve this answer
    
We've ran a SQL trace on the remote server filtered to show all transactions where Application name is like DatabaseMail - it traced 100's working calls, but the calls from the linked server was nowhere to be seen, which would suggest the Linked Server is not connecting at all - We have logged a paid support request with Microsoft on this - wasting too much of our time ha-ha - will update when i know more. –  HeavenCore May 3 '13 at 14:02
2  
No, Trace it in your source server, not the target (Linked) server. The error indicates a failure to connect to the target server, so of course you won't see it there, it never gets there. –  RBarryYoung May 3 '13 at 21:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.