Cannot send attachment using sp_send_dbmail

Hi,
I've been scraching my head on this for so long. We have two servers (both
running SQL 2005 64-bit Enterprise Edn SP2 on Windows 2003 Standard 64-bit),
one of the servers is able to send the attachment in the email, whereas the
other is failing with Msg 22051 error:
Msg 22051, Level 16, State 1, Line 0
Attachment file
\\srvr2\data$\Departments\Administration
1\Finance\shared1\New
Test\NewProd\Backup\test.txt is invalid.
The hardware configuration of both of these servers is exactly the same and
the SQL 2005 configuration is also exactly the same. Here is the command
which runs fine on one server but doesn't on the other server:
msdb.dbo.sp_send_dbmail
@.profile_name = N'LOCAL',
@.recipients = N'smarty@.testworld.com',
@.subject = N'TEST',
@.body = N'TEST',
@.file_attachments =
N'\\srvr2\data$\Departments\Administrati
on1\Finance\shared1\New
Test\NewProd\Backup\test.txt'
Database Mail settings on both the servers is also exactly the same. If I
don't specify the attachment, I receive the email in my inbox. I have also
tried mapping the drive, but still the same error. The strange thing is that
it works on the other server. Very odd. Did anybody come across this
situation..'
Thanks,My guess would be that the account that sql server runs under on the server
in which this fails does not have access to that share. Is it a domain
account and does that account have the correct perms to access that share.
Andrew J. Kelly SQL MVP
"Smarty" <Smarty@.discussions.microsoft.com> wrote in message
news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
> Hi,
> I've been scraching my head on this for so long. We have two servers (both
> running SQL 2005 64-bit Enterprise Edn SP2 on Windows 2003 Standard
> 64-bit),
> one of the servers is able to send the attachment in the email, whereas
> the
> other is failing with Msg 22051 error:
> Msg 22051, Level 16, State 1, Line 0
> Attachment file
> \\srvr2\data$\Departments\Administration
1\Finance\shared1\New
> Test\NewProd\Backup\test.txt is invalid.
> The hardware configuration of both of these servers is exactly the same
> and
> the SQL 2005 configuration is also exactly the same. Here is the command
> which runs fine on one server but doesn't on the other server:
> msdb.dbo.sp_send_dbmail
> @.profile_name = N'LOCAL',
> @.recipients = N'smarty@.testworld.com',
> @.subject = N'TEST',
> @.body = N'TEST',
> @.file_attachments =
> N'\\srvr2\data$\Departments\Administrati
on1\Finance\shared1\New
> Test\NewProd\Backup\test.txt'
> Database Mail settings on both the servers is also exactly the same. If I
> don't specify the attachment, I receive the email in my inbox. I have also
> tried mapping the drive, but still the same error. The strange thing is
> that
> it works on the other server. Very odd. Did anybody come across this
> situation..'
> Thanks,
>|||Here is your answer Smarty.
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.
Chris
"Smarty" <Smarty@.discussions.microsoft.com> wrote in message
news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
> Hi,
> I've been scraching my head on this for so long. We have two servers (both
> running SQL 2005 64-bit Enterprise Edn SP2 on Windows 2003 Standard
> 64-bit),
> one of the servers is able to send the attachment in the email, whereas
> the
> other is failing with Msg 22051 error:
> Msg 22051, Level 16, State 1, Line 0
> Attachment file
> \\srvr2\data$\Departments\Administration
1\Finance\shared1\New
> Test\NewProd\Backup\test.txt is invalid.
> The hardware configuration of both of these servers is exactly the same
> and
> the SQL 2005 configuration is also exactly the same. Here is the command
> which runs fine on one server but doesn't on the other server:
> msdb.dbo.sp_send_dbmail
> @.profile_name = N'LOCAL',
> @.recipients = N'smarty@.testworld.com',
> @.subject = N'TEST',
> @.body = N'TEST',
> @.file_attachments =
> N'\\srvr2\data$\Departments\Administrati
on1\Finance\shared1\New
> Test\NewProd\Backup\test.txt'
> Database Mail settings on both the servers is also exactly the same. If I
> don't specify the attachment, I receive the email in my inbox. I have also
> tried mapping the drive, but still the same error. The strange thing is
> that
> it works on the other server. Very odd. Did anybody come across this
> situation..'
> Thanks,
>|||> Database Mail settings on both the servers is also exactly the same. If I
> don't specify the attachment, I receive the email in my inbox. I have also
> tried mapping the drive, but still the same error.
Mapping a drive creates a drive letter for *your* profile. You need to make
sure the SQL service account has access to the share, not you.

> The strange thing is that
> it works on the other server. Very odd.
Most likely the service accounts are different... one is a domain account
thathas access to the share, and the other one is a local account that does
not.|||Thanks Andrew for your answer. The account SQL server runs under is a Domain
Service Account with high-level privileges and it has full permissions on th
e
server. Both the servers run under the same Domain Service Account
"Andrew J. Kelly" wrote:

> My guess would be that the account that sql server runs under on the serve
r
> in which this fails does not have access to that share. Is it a domain
> account and does that account have the correct perms to access that share.
> --
> Andrew J. Kelly SQL MVP
> "Smarty" <Smarty@.discussions.microsoft.com> wrote in message
> news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
>
>|||Hi Chris,
We have Windows Authentication enabled on the server. Currently we are
testing this server and there are no users accessing this server except
myself and I have admin permissions on the box and to the share. I also trie
d
logging in to the server using the Domain Service Account under which SQL
Server and SQL Agent runs, but still I get the same error.
Thanks..
"Chris Wood" wrote:

> Here is your answer Smarty.
> Database Mail uses the Microsoft Windows security context of the current
> user to control access to files. Therefore, users who are authenticated wi
th
> SQL Server Authentication cannot attach files using @.file_attachments.
> Chris
> "Smarty" <Smarty@.discussions.microsoft.com> wrote in message
> news:B0A66154-E4A3-49AA-9729-68C840868A9C@.microsoft.com...
>
>|||Thanks Aaron for your answer. I tried creating mapped drive using my account
and as well as the SQL Service account, but still no luck. The service
account has full access to the share.
The service accounts under which SQL Server and SQL Agent runs are same on
both the servers, and it is a domain accuont with access to the share. I hav
e
checked it so many times, I am going insane.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Mapping a drive creates a drive letter for *your* profile. You need to ma
ke
> sure the SQL service account has access to the share, not you.
>
> Most likely the service accounts are different... one is a domain account
> thathas access to the share, and the other one is a local account that doe
s
> not.
>
>