Sql-server – Error when sending email with attachment using SQL Server login

database-mailSecuritysql server

I'm trying to send email using msdb.dbo.sp_send_dbmail by a SQL Server login.

It works fine if I'm not attaching anything. But if I attach anything I get the following error:

"The client connection security context could not be impersonated. Attaching files require an integrated client login"

so I mapped that SQL login to a credential that is a local Windows account that I'm sure it has full access to the file I want to attach, as follows:
enter image description here

however, I'm still getting the same error.

What is wrong? am I missing something? how can I fix it? how can I allow non-sysadmin SQL Server logins to send emails with attachment?

Note that:

  1. The file is local and not on a network drive
  2. I have Microsoft SQL Server 2008 R2 (SP3) – 10.50.6220.0 (X64) Mar 19 2015 12:32:14 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

Update:

I have also tried to wrap sp_send_dbmail into a stored procedure the EXECUTE AS the windows account which has all the permission on the file, it can execute the wrapping stored procedure, and it can execute successfully sp_send_dbmail with attachement … I've tried something like:

EXECUTE AS LOGIN='MyMachine\SQLMailUser'

 EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQLMail',  
    @recipients = 'myEmail@myDomain.xyz',
    @body ='some text in email body',  
    @subject = 'some text for the subject',
    @file_attachments='D:\test\test.txt',
    @body_format ='HTML';

but same error šŸ™

Best Answer

As per books online:

Database Mail uses the Microsoft Windows security context of the current user to control access to files. Therefore, users who are authenticated with SQL Server Authentication cannot attach files using @file_attachments. Windows does not allow SQL Server to provide credentials from a remote computer to another remote computer. Therefore, Database Mail may not be able to attach files from a network share in cases where the command is run from a computer other than the computer that SQL Server runs on.

I was able to reproduce this using my AD account. While running following code remotely I get the error. Running the same code by logging into the host was successful.

EXEC msdb.dbo.sp_send_dbmail 
@recipients = 'taiob.ali@company.com', 
@file_attachments = 'd:\FailedLogins_0_131397600848650000.xel', 
@subject = 'testing file attachment' 

Msg 229, Level 14, State 5, Procedure sp_send_dbmail, Line 1 [Batch Start Line 0]
    The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.

Now if that is not your case (meaning the file is on a network share) CREDENTIAL should work.

According to this CREDENTIAL did not work. But the 2nd answer did work though it poses security risk.

There is a connect item about this and here is a comment by Aaron Bertrand which supports the answer I refered above.

If it is indeed a case of reduced privileges, you can work around this by routing mail through a wrapper procedure that uses EXECUTE AS (or is signed). Personally, I think that all of your send mail calls should go through a single wrapper procedure anyway (makes modifications, profile changes, etc. much easier to propagate).

Related Question