Error when sending email with attachment using SQL Server login 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:


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:


The file is local and not on a network drive
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 :(
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).