0

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?

  • What account did you define in your profile? – Stijn Wynants Aug 29 '18 at 6:15
  • @StijnWynants In the email profile I was using anonymous login. I tried using "windows authentication using db engine service credentials" but it made no difference. (also tried providing basic logins with no luck.) – Sir Swears-a-lot Aug 31 '18 at 1:53
  • Question edited. Have found this works with syadmin privs. But I don;t want that acct to have sysadmin. So what specific privs do I need to grant to the sql login? – Sir Swears-a-lot Aug 31 '18 at 2:48
  • Are you sending the email inside a job? or you run the sp_send_dbmail manually after you log in as that sql login account? – jyao Aug 31 '18 at 3:35
  • @jyao For testing/debugging I am running it from SSMS – Sir Swears-a-lot Aug 31 '18 at 4:54
0

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.

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.