1

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 :(

  • Did you get to the bottom of this? I am facing the same issue. I can't believe that the only way I can send with attachment is to use a sysadmin account. – Pete W Jan 10 at 17:29
2

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).

  • Thanks. But the file is local and not on a network drive. And again, I want to send email with attachment using a non-sysadmin SQL login that is mapped to a credential, which (the credential) has full access to the local file which I want to attach, however still I'm getting that error..... I'm not sure if what went successfully with you is similar to my case and setup. what is that AD account?! is that a domain account, local account, or you've meant sql server admin account?! ... I think I've made it clear what is my setup and what I'm intended to do, aint?! – abutmah Jul 2 '17 at 4:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.