Take the following example:
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]' ,
@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 = '[email protected]' ,
@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:
- Recommended adding the credential as local admin & user on server – tried this – didn’t work
- Works around the problem by using different credentials to access remote server - not really an option for us
- Suggests checking the key in question - I don't have this key on my SQL Server
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.
