November 2, 2012 at 10:07 am
OK, I think I arrived at a possible explanation of what is going on:
Chances are the Server Proxy is not involved. I think we can attach a remote file when logged in as a sysadmin as long as the service account has access to the remote file. This is because the service account will be the security context SQL Server will use when a sysadmin is executing the call to sp_send_dbmail.
When we make a call using a non-sysadmin Windows-based Login the specific Windows user credential is what Database Mail will want to access the remote file as, and by that I mean it will spawn a thread as the service account and then try to impersonate the non-sysadmin Windows user. However, even though the Windows user may have been granted access to the remote file, because SQL Server has a rule about not attempting to impersonate a credential to facilitate accessing a remote computer the attempt to attach the remote file fails.
This only holds up if we assume the thread asked to attach the file is running in the context of the service account initially, then in the case of a sysadmin making the call there is technically no impersonation needed whereas with a request from a non-sysadmin the request requires that the thread running as the service account try impersonating the logged in user which is not something SQL Server will honor when accessing a remote computer.
I am trying to figure how to test this, but I think it will require a bit more wrangling in the area if Windows instrumentation then I have in my toolkit.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply