Home Dashboard Directory Help
Search

DBMail fails when using a Linked Server query by Anuja Malik


Status: 

Closed
 as Won't Fix Help for as Won't Fix


4
0
Sign in
to vote
Type: Bug
ID: 753426
Opened: 7/12/2012 4:14:09 AM
Access Restriction: Public
1
Workaround(s)
view
3
User(s) can reproduce this bug

Description

Hello,

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',
    @profile_Name='MSSQL',
    @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.
Details
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
Hi,
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.
Sign in to post a workaround.
Posted by Anuja Malik on 7/12/2012 at 4:14 AM
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"