November 6, 2009 at 4:42 pm
Hi everybody. I have a problem I am connecting to Database with user 'rpp' and when execute this code:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Administrator',
@recipients = 'lisset.arce@ewong.com',
@file_attachments = 'D:\SQLoutput\OLD_cobranza.txt',
@subject = 'Prueba RIMAC - Detalle de Cobranza'
Show the follow error:
Servidor: mensaje 22051, nivel 16, estado 1, línea 0
The client connection security context could not be impersonated. Attaching files require an integrated client login
The user rpp have the el role DatabaseMailUserRole.
Maybe I have to activate something?
Thanks for your help
November 7, 2009 at 2:31 am
Lisset (11/6/2009)
Servidor: mensaje 22051, nivel 16, estado 1, línea 0The client connection security context could not be impersonated. Attaching files require an integrated client login
The user 'rpp' is a SQL Server login.
You must use a login mapped to a Windows account - also known as an 'integrated client login'.
Sending mail requires that the server act on behalf of the login, this is only possible with a Windows login - SQL Server logins cannot take part in Windows impersonation.
November 9, 2009 at 8:44 am
Please can you tell me how I can mapping a SQL Server login to Windows account? because I cant change the user 'rpp' for execute the SP.
November 9, 2009 at 8:56 am
Hi,
I don't think this is the problem, as I have just run the same command in my environment....
msdb..SP_SEND_DBMAIL
@RECIPIENTS = 'XXX@XXXXXX.com',
@PROFILE_NAME ='MAIL',
@SUBJECT = 'TEST',
@BODY='TEST',
@BODY_FORMAT = 'HTML',
@file_attachments = 'C:\WWW.sql'
I used a local login and all worked fine.
Have you had a look at your mail profile and sent a test email?
Regards
Peter Gadsby
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 9, 2009 at 8:59 am
In our mail profile we use anonymous authentication... Do you use that?
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 9, 2009 at 9:23 am
Hi Peter:
->;Have you had a look at your mail profile and sent a test email?
Yes and was succesfull. And have anonymous authentication.
November 9, 2009 at 9:26 am
Hi Hmmmmm Did you run the test mail when logged in using your rpp account?
Also did you try running the send mail command from SSMS (also using the rpp account)?
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 9, 2009 at 9:30 am
Also is the D: drive a mapped drive? if so try using the UNC path instead. Also have you checked that the SQL Server service login has permission to look at the directory.
Pete
Peter Gadsby
Business Intelligence Consultant
www.hgconsult.co.uk
November 9, 2009 at 2:29 pm
I has run test mail with rpp logged and this user have permissions in the drive.
November 9, 2009 at 3:20 pm
Guys,
Lisset (11/6/2009)
Servidor: mensaje 22051, nivel 16, estado 1, línea 0The client connection security context could not be impersonated. Attaching files requires an integrated client login.
There's a huge clue right there 😉
Paul
November 9, 2009 at 3:44 pm
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"
November 10, 2009 at 3:09 am
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
November 10, 2009 at 3:33 am
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
November 10, 2009 at 4:00 am
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
November 10, 2009 at 4:26 am
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
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply