Database mail sending fails with SP_Send_DBmail.

Question

Database mail sending fails with SP_Send_DBmail.

Jigneshal

SSC Eights!

Points: 942
More actions
June 4, 2013 at 12:27 am

#274597

Hi,
I am stuck in below problem.
I have two servers; ServerA and ServerB. I have linked serverA to serverB by
sp_addlinkedserver @server = 'serverB',@provider = 'SQLNCLI',@datasrc = 'ServerB',
@srvproduct = '' on serverA. Now I am querying on ServerA and retriveing data from ServerB's Table.
The Query is :
EXEC msdb.dbo.sp_send_dbmail @recipients = 'myname@myDomain.com',
@subject = 'MySubject',
@body = 'This Is Test.',
@body_format = 'Text', @profile_name = 'Profile1',
@query = 'set nocount on
SELECT * FROM [ServerB].[Database].dbo.myTable AS MT
WHERE DATEADD(DAY,0,DATEDIFF(DAY,0,Date_Col)) = DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))',
@execute_query_database = 'master',
@query_result_separator = ' ',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'myFile.csv'
I have created an proc on serverA's master database and calling this proc into a job.
This job throws an error:
" Executed as user: ServerA\sqlAgent. Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [SQLSTATE 28000] (Error 18452). The step failed. "
Can Anybody help in solving this??
It would be a great help.
Thanks and Regards,
Jignesh
Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎

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

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

Ness SSCarpal Tunnel Points: 4282 More actions · Answer 1

Check the account that the SQL Agent is running under and check that it has permissions to the server that you are trying to query.

SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk

Jigneshal SSC Eights! Points: 942 More actions · Answer 2

Hi Ness,

Where can I check the account under which SQL Agent is running? How to grant permissions to the Agent Account if the permissions are not granted?

Thanks.

Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎

jnuqui SSCommitted Points: 1585 More actions · Answer 3

hi Jigs,

SQL Server agent is located on SQL SERVER Configuration manager. Click SQL Server Services then click on SQL Server Agent, Right click and click properties. The account specified to run the SQL Server Agent is there.

NOTE: that the account should be a member of sql server of sysadmin fixed server role.

Hope this helps.

JM

===============================================================

"lets do amazing" our company motto..

Jigneshal SSC Eights! Points: 942 More actions · Answer 4

Hi JM,

Thanks. I also Asume this is the issue of SQL server Agent Account. Error indicates the server/sqlAgent login failed. I need to check for the sql Agent Account on my server.

Thanks again.

🙂

Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎

Jigneshal SSC Eights! Points: 942 More actions · Answer 5

Hi,

The issue is solved.There was a authentication problem for ServerA/SQLAgent.

I have mapped both the user; i.e user from ServerA and user from ServerB using

following system SP. In my addlinkedserver @useself was true bydefault.

We need to make it false and map both the server's user.

EXEC sp_addlinkedsrvlogin

@rmtsrvname = 'LinkedServerName',

@useself = 'false',

@locallogin = 'localLoginName',

@rmtuser = 'RemoteLoginName',

@rmtpassword = 'RemoteLoginPassword';

It worked for me.

Now I am able to send DB mail from serverA with attachment of Data from ServerB.

Cheers..!!

Regards,
Jigneshal
" Stretch Your Limit Little Beyond Your Limit....! "
😎