Using sp_send_dbmail with SQL Authentication to Send Attachments from UNC Path

credentialssql-server-2016

I'm trying to send emails with attachments using a SQL authentication account that I have granted credentials to.

The login used in the stored Credentials is a domain account that has permissions to read from the share. 

EXEC msdb.dbo.sp_send_dbmail 
    @recipients = 'me@abc.xyz.nz', 
    @profile_name = 'Alarms', 
    @subject = 'Test with Attachment', 
    @body = '*** Alarms ***' , 
    @file_attachments = '\\Servername\Share\Images\TestImage.png'

But when I use the SQL login I get the following error:

Msg 22051, Level 16, State 1, Line 26 The client connection security
context could not be impersonated. Attaching files require an
integrated client login

What have I missed? how can I test that the credentials have been applied correctly? Does this only work for a local share?

Edit: I granted my SQL login Sysadmin privs and it now works with both a local share and with a remote share. So what privs does sysadmin have that allows this to work?

Best Answer

I managed to get this working eventually in my Dev environment.

  1. Enable xp_cmdshell
  2. Add proxy account DomainLogin
  3. grant execute on xp_cmdshell to SQLLogin
  4. Grant logon as a batch job on Server On server open Secpol. > Security Settings > Local Policies – User Rights Assignment node. Add DomainLogin
  5. Create SQL Credential "CredForMail" using DomainLogin
  6. Grant Credential to : ALTER LOGIN SQLLogin ADD CREDENTIAL CredForMail
  7. ALTER DATABASE DatabaseName SET TRUSTWORTHY ON

The end result is that I have a non-sysadmin user that can send emails with attachments it has picked up from a share on a remote server.

I think the proxy account and Credential may be overlapping and both may not be necesary. I have tried so many things I am no-longer sure of the bare minimum required to make this work. (and that bothers me)

Because of the complexity to set this up, enabling features that could be seen as a security risk, I am going to back all of this out, and instead will separate out this application function and give it it's own domain account.

Related Question