Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Error using sp_send_dbmail Expand / Collapse
Author
Message
Posted Monday, November 9, 2009 3:44 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
I agree with Paul. Your error seems to indicate that you need a Windows login to send the mail and not a SQL login. Can you add a Windows login to your instance and test it and let us know if it works or not?

Joie Andrew
"Since 1982"
Post #816201
Posted Tuesday, November 10, 2009 3:09 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 9:11 AM
Points: 138, Visits: 305
I dont understand why we don't get the error 'files requires an integrated client login.'

We can use a local login to send an email with an attachment!

Pete


Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
Post #816377
Posted Tuesday, November 10, 2009 3:33 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:07 PM
Points: 9,926, Visits: 11,183
Peter Gadsby (11/10/2009)
I dont understand why we don't get the error 'files requires an integrated client login.'
We can use a local login to send an email with an attachment!

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.


http://msdn.microsoft.com/en-us/library/ms190307.aspx

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #816387
Posted Tuesday, November 10, 2009 4:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 9:11 AM
Points: 138, Visits: 305
Hmmm still don't understand....

I am on a Citrix server, connecting to SQL Server remotely using a local SQLSERVER user.

My NT account has NO permissions to the SQLSERVER box, I can't even login to the server.

When I call the command ....

EXEC msdb..SP_SEND_DBMAIL
@RECIPIENTS= 'XXX@XXX.com',
@PROFILE_NAME = 'MAIL',
@SUBJECT = 'TEST',
@BODY = 'TEST',
@BODY_FORMAT = 'HTML',
@file_attachments = 'C:\New Text Document (2).txt'

The email is successfully sent

I can also send an email using an attachment using a UNC path....


EXEC msdb..SP_SEND_DBMAIL
@RECIPIENTS= 'XXX@XXX.com',
@PROFILE_NAME = 'MAIL',
@SUBJECT = 'TEST',
@BODY = 'TEST',
@BODY_FORMAT = 'HTML',
@file_attachments = '\\comdata01\Shared\orchard.log'

What am I missing?


Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
Post #816393
Posted Tuesday, November 10, 2009 4:26 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:07 PM
Points: 9,926, Visits: 11,183
Peter,

Are you able to run:

select net_transport, protocol_type, auth_scheme from sys.dm_exec_connections where session_id = @@spid;
select [host_name], login_name, original_login_name from sys.dm_exec_sessions where session_id = @@spid;

...please?

The results may help explain things.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #816397
Posted Tuesday, November 10, 2009 4:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 9:11 AM
Points: 138, Visits: 305
Nop still don't understand, perhaps I'm having a blonde day!

The output shows....

TCP TSQL SQL

COMWTSV06 DATA_LOAD DATA_LOAD


DATA_LOAD being the local user login....

Listen don't worry, it's working for me I was just trying to help the original poster......


Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
Post #816402
Posted Tuesday, November 10, 2009 5:04 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:07 PM
Points: 9,926, Visits: 11,183
I meant the output might help explain it to me!

It didn't. I suppose your SQL Server team might have hacked sp_send_dbmail - the default procedure has an EXECUTE AS 'dbo' on the procedure, but an EXECUTE AS CALLER wrapped around the bit that attaches the file.

I wonder if they have changed that to EXECUTE AS SELF/OWNER/a user mapped to a windows login and removed the EXECUTE AS CALLER bit...? Wouldn't be supported, but it's an interesting idea. I might try it later just to see.

Paul




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #816410
Posted Tuesday, November 10, 2009 5:07 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 9:11 AM
Points: 138, Visits: 305
Nothing has changed to the SP they are using the standard one.

Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
Post #816411
Posted Wednesday, November 11, 2009 4:55 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 11, 2012 9:32 AM
Points: 92, Visits: 341
Paul,
How I can vinculate a SQL Server account to Windows account?

Thanks by your help,
Post #817551
Posted Tuesday, January 8, 2013 7:56 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 10:33 PM
Points: 69, Visits: 142
Try to use SA account as a Test
Post #1404524
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse