DBMail fails when using a Linked Server query - by Anuja Malik

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 753426 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 7/12/2012 4:14:09 AM
Access Restriction Public



We recently upgraded all our SQL Server 2008 servers to SP3 (10.0.5500). Post upgradation, we found that DBMail fails when using a Linked Server query. 

We had following Linked Server configuration : 
SQL Server Linked Server 
Security - "Be made using the login’s current security context” 

DBMail query (which was working fine before SP3) : 
EXEC msdb.dbo.sp_send_dbmail 
	@recipients='email address',
	@subject = 'hello',
	@body = 'hello',
                @query='set nocount on select * from [LinkedServer].master.dbo.sysusers',
	@query_result_header= 0,
	@body_format = 'HTML' ;

After SP3, the above query fails with following error : 
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: Msg 7437, Level 16, State 1, Server XXXXXXXXXX , Line 1
Linked servers cannot be used under impersonation without a mapping for the impersonated login.

If I run the Linked Server query outside DBmail, it works fine : 
set nocount on select * from [LinkedServer].master.dbo.sysusers

Currently, we are using following workaround : 
1. Changed Linked Server configuration to use SQL Authentication (Be made using this security context) 
2. Create a system DSN in ODBC and create a Linked Server with "Microsoft OLE DB provider for ODBC drivers" 

We have tested this scenario on several SQL 2008 servers, and found it to be consistent across all 10.0.5500 versions. 

We would like to report this issue to Microsoft as we suspect this issue has arised after SP3 installation. Please let us know if this is a known issue\BUG. 
Sign in to post a comment.
Posted by Razmus on 7/1/2013 at 6:40 AM
Problem also occurs in SQL 2008 R2, when upgraded to SP2.
Posted by Microsoft on 2/26/2013 at 7:25 PM
Thank you for your feedback. As there is a workaround, we have no plans to fix it. With the given resource and schedule, we will look into issues that don't have feasible workarounds. Thanks & Regards, Pooja Harjani, Progam Manager.