Sending remote attachments using sp_send_dbmail

Question

Sending remote attachments using sp_send_dbmail

Decommissioned_Account_02

Ten Centuries

Points: 1093
More actions
August 17, 2012 at 10:13 am

#260782

Hi,
I am using Windows Authentication to send email using sp_send_dbmail.
Sending email works if I do not try to add a remote attachment. It also works if I try to send email with a local attachment (on the SQL Server machine). It works for remote attachments (as \\remote_server\share_name\test.txt) **if** I am connected to SQL Server as a sysadmin (Windows Authentication test logon in the sysadmin role). However, if I try sending email with a remote attachment as a non-administrative user, the attachment fails.
The error message I receive is:
Msg 22051, Level 16, State 1, Line 0
Failed to open attachment file '\\ remote_server\share_name\test.txt'. Access denied.
The Windows Authentication test logon is in the DatabaseMailUserRole Database Role and has full control of the remote share. 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 am running SQL Server 2008 R2 (64-bit) on Windows 2008 R2 Server (64-bit). Also the Windows Authentication test login I am using has full control of the remote share.
Thanks!
DV

Viewing 15 posts - 1 through 15 (of 15 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

dba0419 (8/17/2012)
Hi,
I am using Windows Authentication to send email using sp_send_dbmail.
Sending email works if I do not try to add a remote attachment. It also works if I try to send email with a local attachment (on the SQL Server machine). It works for remote attachments (as \\remote_server\share_name\test.txt) **if** I am connected to SQL Server as a sysadmin (Windows Authentication test logon in the sysadmin role). However, if I try sending email with a remote attachment as a non-administrative user, the attachment fails.
The error message I receive is:
Msg 22051, Level 16, State 1, Line 0
Failed to open attachment file '\\ remote_server\share_name\test.txt'. Access denied.
The Windows Authentication test logon is in the DatabaseMailUserRole Database Role and has full control of the remote share. 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 am running SQL Server 2008 R2 (64-bit) on Windows 2008 R2 Server (64-bit). Also the Windows Authentication test login I am using has full control of the remote share.
Thanks!
DV

Shouldn't need to give everyone sysadmin rights. It looks like tey need appropriate permissions on to access the files on '\\ remote_server\share_name\'.

Decommissioned_Account_02 Ten Centuries Points: 1093 More actions · Answer 2

Thanks Lynn. However, the Windows Authentication test login I am using has full control of the remote share (read, write, create, delete).

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

dba0419 (8/17/2012)
Thanks Lynn. However, the Windows Authentication test login I am using has full control of the remote share (read, write, create, delete).

And the other windows logins that need to send files doesn't have the necessary privledges needed to acces and send those files. Not a SQL persmissions issue.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

It has nothing to do with the caller, and everything to do with the SQL Server service account. When you try using Database Mail the attachments need to be accessible by the service account.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Decommissioned_Account_02 Ten Centuries Points: 1093 More actions · Answer 5

There is only 1 login being used (for testing this issue) and that login has full control of the network share and is in the DatabaseMailUserRole Database Role.

Example:

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseGoogleMailProfile',

@recipients = 'xyz@abc.com',

@subject = 'TESTING2',

@body = 'This is a new test message2.',

@file_attachments = '\\remote_server\share_name\test.txt'

go

When I grant the 1 login that I am using for testing SA rights in SQL Server I can attach and send files from a remote share.

Decommissioned_Account_02 Ten Centuries Points: 1093 More actions · Answer 6

The SQL Server service account that is running all the SQL Server services on this SQL Server is a Domain Admin on our network.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

dba0419 (8/17/2012)
There is only 1 login being used (for testing this issue) and that login has full control of the network share and is in the DatabaseMailUserRole Database Role.
Example:
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DatabaseGoogleMailProfile',
@recipients = 'xyz@abc.com',
@subject = 'TESTING2',
@body = 'This is a new test message2.',
@file_attachments = '\\remote_server\share_name\test.txt'
go
When I grant the 1 login that I am using for testing SA rights in SQL Server I can attach and send files from a remote share.

Whether the login is a sysadmin or not does not make all the difference in terms of the attachment. Consider that a SQL Login is ~~not~~ restricted to only sending emails without attachments and that the SQL Login executing sp_send_dbmail does not have a Windows context to use.

Confirm the non-sysadmin user has permissions to the Mail Profile you're using.

sysmail_add_principalprofile_sp (Transact-SQL)

edit: strike 'not', a key word to the meaning of post

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

As a side note, having your SQL Server run as a Domain Admin is quite dangerous considering you're about to open this up for users to send emails with attachments. Essentially this means anyone can send an email and attach any file on the network for which they know the UNC path to an email and send it to themselves.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

How is it going?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Eric Stimpson SSChasing Mays Points: 649 More actions · Answer 10

This has been one of the more frustrating issues I've tried to resolve and I've basically gotten nowhere. If someone has it solved, I'd love for them to do a write up of their solution. We have SQL server 2012 running on Windows 2008 R2, under a Managed Service Account. We have the file share on an Windows 2008 R2 machine where we have granted access to the Domain users and service account for SQL Server to the files and folders that need to be attached. And here's the results that we get:

If I log onto the database using SA or a Domain Admin account, I can send attachments using the file share and UNC.

If I log onto the database using a User Account, I can send email but I get "Access denied" using the file share for an attachment (UNC path). I can, however, attach a local file if and only if the service account has access to the file/folder.

I can use a FileTable with either a Domain Admin account or a User Account connecting to SQL Server, so this is the workaround we chose, but I'd really like to understand why nothing worked trying to let a user send email from the file share on the file server. I even tried escalating the user account to the sysadmin role on the server just as a test, and it made no difference.

Half of what I'm seeing would lead me to believe that SQL is using the logged in user's credentials to try and read the file (i.e. I can do it when I log into the database with a domain admin account), while the other half of what I see leads me to believe that it is trying to use the service account (i.e. I can only attach a file on the SQL Server machine if I grant access to the service account).

Perplexing

Lowell SSC Guru Points: 323518 More actions · Answer 11

It sounds like the account used in Services has access to the shares(because that's what sa /sysadmin's would use for credentials outside of SQL server, but that the proxy_account is not the same credentials.

or if the above was blank, the account in services:

this is a common security misconception/"gotcha". The problem is that when you access any resource OUTSIDE of SQL server, like network shares, local hard drives and folders,xp_cmdshell,bcp with a "trusted" connection, sp_OA type functions etc.

it doesn't matter what YOUR credentials are. Whether you are Domain Admin,Local Admin , logged in as sa, administrative login on a laptop, etc, because SQL will not carry those credentials to the "outside of SQL" security context.

when sa or a sysadmin tries to accesss items outside of SQL, it always uses the account running the service.

when a normal user tries to access the same resources, it uses the proxy account, and if that is blank, it uses the same services account.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 12

Good call, I didn't think to ask about the server proxy. It is worth checking in the instance as it could be the root cause of the behavior. By default it is not set so if it is set there was some motivation to change the default of the instance. When not set sysadmins will auth to the external resources as the SQL Server service account.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 13

From what I have read the Server Proxy applies only to xp_cmdshell but the documentation is in lack of clarity in terms of whether the proxy also pertains to external activities not related to xp_cmdshell as well. One thing is for sure though, regardless of whether the proxy is set or not sysadmins will authenticate to the OS as the SQL Server service account when invoking xp_cmdshell. Lowell, do you have any definitive info on whether the Server Proxy extends to other external operations besides xp_cmdshell? I would be interested in a reference to help get clarification around that because I came up empty when searching.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Lowell SSC Guru Points: 323518 More actions · Answer 14

opc.three (11/1/2012)
Lowell, do you have any definitive info on whether the Server Proxy extends to other external operations besides xp_cmdshell? I would be interested in a reference to help get clarification around that because I came up empty when searching.

nothing definitive at this time, sorry my friend. The only external access I can envision starts with xp_cmdshell; nothing else comes to mind as far as accessing alternate external operations that would carry the service credentials.

I'll throw some time into researching it , it's one of those good to know things, for sure.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!