question

thennarasu avatar image
thennarasu asked

send attachment through db_mail in shared location using non admin login in sql server

I am using Windows authentication to send mail using sp_send_dbmail. Sending the mail works if I do not try to add an attachment. It also works if I try to send the mail with a local attachment (on the SQL Server machine). It works for remote attachments (as \\server\share_name\file.xls)

*if* I am connected to SQL Server as a sysadmin (Windows logon in the sysadmin role). However, if I try send with a remote attachment as a non-administrative user (Windows logon not in the sysadmin role), the the attachment fails. The error message I recieve is "Failed to open attachment file '\\server\share\file.pdf'. Access denied."

It has nothing to do with the permissions on the file or remote server itself, as to prove this theory I put my windows test account into the sysadmin role on the development SQLServer. Doing this made the attachment work without making any changes on the remote server. This account has been in the DatabaseMailUserRole, and sending e-mail worked fine when the user was not an admin as long as I didn't try to add an attachment.

It would appear there is some SQL Server permission setting that I'm missing to allow any user to add remote attachments. Obviously I can't just give everyone sysadmin rights on the server in order to let them include attachments in e-mail. Can anyone shed some light on this?

I'm using SQL Server 2012 SP 1 and running on Windows 2012 Server.

Thanks.

sql serversql server 2012
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Ange avatar image
Ange answered

Hi,

I think it might be using a SQL service account rather than the account you are logged in with. The SQL service accounts are local only so you cannot give them permissions on another server.

I came across this issue using a shared server for backups. My solution was to create an active directory server group and put the servers in that group. Then give this group permissions to the share.

Might not be the right way to do it but we spent alot of time trying to figure it out and this was the only solution I could come up with. Any time we spin up a new server it is added to the server group and hence has permissions to the share.

Obviously if you only have the one DB server, you could just give this machine permissions on the share and cut out the middle man. We have quite a few servers so the security group was a better solution for me.

Hope that helps

Ange

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.