SQL Server 2008, connecting via SQL Server Authentication.
I have a stored procedure in
DatabaseA which calls
msdb to send an email with a file attachment. The file is on the db server, not on a remote fileshare.
The SQL Server account being used is not sysadmin, but does belong to the
Sending an email without an attachment is fine, but when an attachment is present I get an error:
The client connection security context could not be impersonated. Attaching files require an integrated client login
There are a few articles/posts about this out there, but some seem to say conflicting things. I've been looking into impersonation, and one thing that does work is in the stored procedure in
DatabaseA, to do the following:
EXECUTE AS LOGIN = 'sa' -- or any account with sysadmin privileges EXECUTE msdb..sp_send_dbmail .... REVERT
I wasn't expecting this to work as I thought to send attachments, you needed to use Windows Authentication. However it does work, but it means the lower privileged SQL Server account needs to be granted permission to IMPERSONATE the sa (or other sysadmin account).
Doing my due diligence as a dev before unleashing a DBA's nightmare into the wild...
My question is: what is a good/safe way of allowing a user authenticated via SQL Server (non sysadmin) to send email attachments from the local db server disk without opening up a security hole?
I've created a new Windows Login, created credentials for that account via SSMS, mapped those credentials to my limited privileges SQL Server account. I get the error:
Msg 22051, Level 16, State 1, Line 0
The client connection security context could not be impersonated.
Attaching files require an integrated client login
I must be missing something!