Database Mail on SQL 2005

After I configured Database Mail with a profile MAILPROFILE and using a SQL authentication, I run the following statement with no problem

exec msdb..Sp_send_dbmail
@profile_name = 'MAILPROFILE',
@recipients = 'asher@fluxus.net',
@subject = 'test siebel account',
@body = 'TEST',

If try to run the same thing with an attached file (cf error message) I have an error message

Msg 22051, Level 16, State 1, Line 0

exec msdb..Sp_send_dbmail
@profile_name = 'MAILPROFILE',
@recipients = 'me@test.net',
@subject = 'test message',
@body = 'TEST',
@file_attachments='D:\DBAScripts\test.sql'

While I suspected a file security problem (I gave all possible rights on the file system), I have no problem when I attribute sysadmin right to the SQL account

The Sp_send_dbmail documentation explains that file attachments should not be possible using with a SQL authentication.  Did anybody have a similar problem?  ...I suspect 2005 to have a different security schema for authenticating users and services but I would appreciate some feedback on that.  Thank you...
LVL 23
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
where is the file
'D:\DBAScripts\test.sql'
on your PC or on Sql server?
if it is not on sql server
try to use '\\servername\Sharename\test.sql'
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Also: Have you checked the access of the user? From Books Online -
"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."
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistAuthor Commented:
Thanks EugeneZ for your response...

I was aware that the BOL indicated that SQL authenticated session should not be able to send attachments, but they actually can without even having the sysdba account.  A client of mine succeeded in doing so in Preproduction server.  Now, he tried to deploy the system is Production but did not succeed as he did in Preprod because he has no idea what actually makes the difference.  With no success, I tried running an Error and Warnings trace to trakc the event that may explain the last process running before the exception but I could not find anything.  I need to bring some explanation to my client but I could not reconstitute its success to send a mail with attachment using SQL authentication.  I need to understand what is the difference in the functionality when using either SQL or NT authentication...I was hoping that somebody may have encoutered a similar situation...
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
check-> databasemailuserrole msdb database role

Troubleshooting Database Mail: General steps  
http://msdn2.microsoft.com/en-us/library/ms187540.aspx



Also from
You cannot send query attachments in Database Mail on a named instance
When Database Mail on a named instance sends mail with a query attachment, Database Mail tries to run the query against the default instance. If no default instance exists, the query fails. If a default instance exists, the query may fail. Or, the query may run. However, the query may return incorrect results.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.