Database mail question

Question

Database mail question

Animal Magic

SSChampion

Points: 13964
More actions
June 17, 2010 at 3:25 am

#143818

Morning all,
I had an issue yesterday where one of my mail procedures was failing because some new code had been added to add a file attachment. I've removed the code for now and know what the issue is, its because of the security context in which the procedure is being run and it cant add the file attachment.
The attachment is on a remote server: ps-fs01
The sql box is called ps-sql
The code thats causing the issue is this....
declare @Attach varchar(300),
@email varchar(100)
set @Attach = '\\ps-fs01\AutomatedEmailAttachments\ExpensesMadeEasy.pdf'
set @Email = 'john.morris@paystream.co.uk'
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'NewBusiness',
@recipients=@email,
@subject = 'Submitting your expenses online',
@body = @HTML,
@file_attachments = @attach,
@body_format = 'HTML' ;
I found on BOL the following statement
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.
If i run the code from the sql box with windows account (sysadmin) it runs fine, as expected
If i run the code from the sql box with sa account it runs fine, i wasnt expecting this as it goes against what the BOL message says
I run the code from my laptop using sa and again it works fine.
The only time i can get it to fail is by setting up a test user and giving it the DatabaseMailUserRole in the msdb database. This gives the following error on execution
The client connection security context could not be impersonated. Attaching files require an integrated client login
The application in question which was hanging due to this issue runs under the sa security context (i know, i know, its in the pipeline to be changed) yet the code doesnt work. Why would it not work under the application context running as sa but it will for me using sa on the server or my laptop?
I think the answer to my problems is queue the emails to be sent and then use a sql job to run the emails (the same procedure that is failing in the application runs fine through a sql job)
ps, sorry for the long post 🙂

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 1

Could it be that your application is accessing sql under your sa user but overall running as a different user?

We had a similar issue a while ago pertaining to multiplel hops and authentication. We got round it Kerberos, but i dont guess that would be of much use with sql accounts.

Adam Zacks-------------------------------------------Be Nice, Or Leave

Animal Magic SSChampion Points: 13964 More actions · Answer 2

It could well be, however im not 100% sure how to check for that. The only thing i can think of is that the windows user account running the app is somehow being used when its trying to run the procedure that sends the mail. However when you run profiler the login name of all the commands is sa. :unsure:

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 3

oh, well that was going to be my next suggestion, profiler. If you look at the app server services you would see the service account (which wont be sa), or if it doesnt use a service it must, in someway be using NT auth to run the app on whatever client its on (cant run the app as sa).

If it turns out to be a service, just find a way to give that account access to do the attachement (local rights on the remote server/volume). The otherway (local users) im not sure.

I dod tend to think it cant be that bad if it can be done through ssms. Out of curiosity why are you using sa and not some sort of app AD user?

Adam Zacks-------------------------------------------Be Nice, Or Leave

Animal Magic SSChampion Points: 13964 More actions · Answer 4

I dod tend to think it cant be that bad if it can be done through ssms. Out of curiosity why are you using sa and not some sort of app AD user?

Historic thing from 3rd party devs and its hardcoded all over the place. its a nightmare 🙁 it is to be changed soon though 🙂

ps, ill check out your other suggestions now

thanks

Animal Magic SSChampion Points: 13964 More actions · Answer 5

Schadenfreude-Mei (6/17/2010)
oh, well that was going to be my next suggestion, profiler. If you look at the app server services you would see the service account (which wont be sa), or if it doesnt use a service it must, in someway be using NT auth to run the app on whatever client its on (cant run the app as sa).
If it turns out to be a service, just find a way to give that account access to do the attachement (local rights on the remote server/volume). The otherway (local users) im not sure.
I dod tend to think it cant be that bad if it can be done through ssms. Out of curiosity why are you using sa and not some sort of app AD user?

Where abouts is this found Adam?

Thanks in advance

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 6

If you goto services.msc on the app server ther should be some service (maybe) related to the app (for example 'ThinkFolio' on ours shows as 'thinkServerPROD'). When you find that, the right most colum shows the logon account.

Adam Zacks-------------------------------------------Be Nice, Or Leave