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) :
@subject = 'hello',
@body = 'hello',
@query='set nocount on select * from [LinkedServer].master.dbo.sysusers',
@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.