XP_Sendmail is giving error in SQL-2005

Question

XP_Sendmail is giving error in SQL-2005

Prasanna Kulkarni-265121

SSC Enthusiast

Points: 155
More actions
April 2, 2008 at 4:04 am

#204992

Hi,
I have migrated from 2k to 2005 and using the same sql 2k procedure. One of the procedure is using xp_sendmail to send mail with attachment. I have a separate login and given the execute permission of xp_sendmail and the calling stored procedure.
The error is as follows:
"xp_sendmail :The client connection security context could not be impersonated. Attaching files require an integrated login."
Thanks in Advance

Viewing 7 posts - 1 through 6 (of 6 total)

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

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 1

Prasanna Kulkarni (4/2/2008)
Hi,
I have migrated from 2k to 2005 and using the same sql 2k procedure. One of the procedure is using xp_sendmail to send mail with attachment. I have a separate login and given the execute permission of xp_sendmail and the calling stored procedure.
The error is as follows:
"xp_sendmail :The client connection security context could not be impersonated. Attaching files require an integrated login."
Thanks in Advance

Sounds like you are being authenticated using a SQL Server login, thus you will not be able to attach files. Would this be the case?

In addition, you may want to start using Database Mail going forward.

Thanks,

Phillip Cox

Prasanna Kulkarni-265121 SSC Enthusiast Points: 155 More actions · Answer 2

Hi again,

Thanx Phillip

Yes..! I am using a SQL Server login.

Can u guide me how can I use Database Mail.

Thanks in advance

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 3

Prasanna Kulkarni (4/2/2008)
Hi again,
Thanx Phillip
Yes..! I am using a SQL Server login.
Can u guide me how can I use Database Mail.
Thanks in advance

Hi Prasanna,

Sorry, but you cannot attach file when using SQL Server authentication.

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. [/u]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.

Cheers,

Phillip Cox

Prasanna Kulkarni-265121 SSC Enthusiast Points: 155 More actions · Answer 4

Hi Phillip

Thanx,

My server can not be the same from where I am running my procedure since it is being run from different location with separate SQL Server login.

What could be the alternative solution?

If any body has experienced the similar situation please let me know...!

Thanks in Advance

Prasanna

Big Data Man SSCrazy Eights Points: 8536 More actions · Answer 5

Prasanna Kulkarni (4/2/2008)
Hi Phillip
Thanx,
My server can not be the same from where I am running my procedure since it is being run from different location with separate SQL Server login.
What could be the alternative solution?
If any body has experienced the similar situation please let me know...!
Thanks in Advance
Prasanna

Hi Prasanna,

Ideally, you should request have a domain or local account setup on environment where server resides and connect via RDP access to server. At this point, as your account will be a windows based account and you are added to appropriate database(s) with relevant permissions, you would be able to execute stored procedure successfully.

Thank you,

Phillip Cox

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

If you are using SQL mail in a SQL job, the account under which the SQL Agent service is running should have access to the file share where the attachments are located.

If, on the other hand, you are sending mail from the database engine, the account under which the SQL startup service is running needs access on the file share.

I would definitely recommend moving to db mail though. It's far more stable than SQL mail, and you don't need OUTLOOK as a client!

See:

http://msdn2.microsoft.com/en-us/library/ms175887.aspx

http://msdn2.microsoft.com/en-us/library/ms187891.aspx

http://msdn2.microsoft.com/en-us/library/ms186358.aspx

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]