sql server - sp_send_dbmail executed from job fails with query result attached as file

Question

I have faced with the following issue: when trying to send email with results of query attached as file, using sp_send_dbmail via executing ordinary query everything seems to be working OK.

But if add the same code into JobStep and run the job, it fails.

Error in job history says

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

But when I comment out parameter that refers to file attaching it starts working correctly again.

exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'profile_name', 
    @recipients  = 'some@mail.com',
    @body = 'body',
    @subject = 'subj',
    --Parameters that refers to attached file
    @attach_query_result_as_file = 1, 
    @query_result_header = 0,
    @query_result_no_padding = 1,
    @query = 'select 1',
    @query_attachment_filename = 'test.csv'

Any suggestions?

Answer 1

I've come to workaround of that issue. Don't know why would it work but never the less. :) It is definitely about security.

I've investigated that SQL Agent is running on behalf of domain user, say DOMAINUser. It has full set of admin rights on server ('sysadmin' server role, etc). SQL Server itself is running under that same user.

The step of job that contains call to sp_send_dbmail runs under the same DOMAINUser.

Also I've traced that when running the query part of sp_send_dbmail it tries to execute exec xp_logininfo 'DOMAINUser' to check against Active Directory if that user is OK. And surprise: something is definitely not OK. This check ends up with:

Msg 15404, Level 16, State 19, Server SQLC002INS02SQLC002INS02, Line 1
Could not obtain information about Windows NT group/user 'DOMAINUser.', error code 0x2.

That, with some probability can mean anything about that user's password is expired or user is locked or any other non pleasant things for that guy.

I decided that its to risky to change user for Agent. So I come up to sending mail on behalf of 'sa' which has same 'sysadmin' server role but SQL authorization and omits this AD checking step.

It looks like one user that pretends to be admin to ask the real admin to run dangerous code for him :)

So final code of this job's the first and the only step resembles this:

execute as login = 'sa'
exec msdb.dbo.sp_send_dbmail 
    @profile_name = 'profile_name', 
    @recipients  = 'some@mail.com',
    @body = 'body',
    @subject = 'subj',
    --Parameters that refers to attached file
    @attach_query_result_as_file = 1, 
    @query_result_header = 0,
    @query_result_no_padding = 1,
    @query = 'select 1',
    @query_attachment_filename = 'test.csv'
revert