从作业执行的 sp_send_dbmail 失败，查询结果附加为文件

2021-11-30 00:00:00 attachment sql-server-2008 sql-server sp-send-dbmail

我遇到了以下问题:当尝试将查询结果附加为文件发送电子邮件时，通过执行普通查询使用 sp_send_dbmail 似乎一切正常.

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.

但是如果将相同的代码添加到JobStep中并运行该作业，则会失败.

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

工作记录中的错误说

格式化查询时出错，可能参数无效 [SQLSTATE 42000](错误 22050).步骤失败.

但是当我注释掉引用文件附加的参数时，它又开始正常工作了.

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'

有什么建议吗?

推荐答案

我想解决这个问题.不知道为什么它会起作用，但永远不会更少.:)这绝对是关于安全的.

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

我已经调查过 SQL 代理正在代表域用户运行，例如 DOMAINUser.它在服务器上拥有全套管理员权限('sysadmin' 服务器角色等).SQL Server 本身也在同一用户下运行.

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.

包含调用 sp_send_dbmail 的作业步骤在同一 DOMAINUser 下运行.

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

我还发现，在运行 sp_send_dbmail 的查询部分时，它会尝试执行exec xp_logininfo 'DOMAINUser' 以检查 Active Directory 是否该用户正常.令人惊讶的是:有些事情绝对不好.此检查结果为:

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.

我认为将用户更改为 Agent 是有风险的.所以我代表'sa'发送邮件，它具有相同的'sysadmin'服务器角色但SQL授权并省略了这个AD检查步骤.

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

相关文章