SQL Server Performance

Problem with Database mail

Discussion in 'SQL Server 2005 General DBA Questions' started by satya.sqldba, Mar 6, 2007.

  1. satya.sqldba New Member

    Hello DBA's<br /><br /> A strange problem is bugging me from the morning. My database mail is not able to send an email out. Even when I try doing a test email it fails.<br /><br />When I look into the sysmail_allitems table to look at he error message, it says:<br /><br />The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-03-06T13:55:0<img src='/community/emoticons/emotion-11.gif' alt='8)' />. Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine). )<br /><br /><br />Any idea what could be the problem<br /><br />Thanks<br /><br />Satya
  2. MohammedU New Member

    Based on the error it is your mail server. Are you able to connect to smtp server?
    Is port 25 opened on the server?

    Check the firewall configuration.
    Try using another mail client like outlook to send the email using smtp server to make sure your smtp server is working properly..


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. satya.sqldba New Member

    Thanks Mohammed

    I checked with Outlook and it works wel. Mails go out from that account.

    COming to port 25, I have already tried using the portqry tool to check if port 25 is open or not. This was the error message I got there.

    Error opening socket: 10053


    A Winsock error has been encountered.
    portqry.exe -n 127.0.0.1 -e 25 -p TCP exits with return code 0x00000063.

    So I guess it is a problem with port 25. Am I correct?

    Thanks
    Satya
  4. satya.sqldba New Member

    MOhammed

    Sorry about the error message regarding the port number, I was checking on the wrong server.

    I checked with the n/w admin and the port is fine on the exchange server and I also tried sending emails from Outlook and it works.

    I am not able to figure out the problem

    Thanks
    Satya
  5. MohammedU New Member

    Check the SQL server service account has the permissions...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  6. satya.sqldba New Member

    Mohammed

    It has sysadmin permissions

    Thanks
    Satya
  7. MohammedU New Member

    sysadmin is local what about the permission on SMTP server?

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  8. satya.sqldba New Member

    Mohammed

    We have an Active Directory and sqlagent is a part of sysadmin group.

    Thanks
    Satya
  9. satya.sqldba New Member

    Mohammed

    Now I have another strange error. I tried deleting the old profiles, accounts and creating them once again.

    In the process of trobleshooting

    when I execute the following query:

    EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail'

    the queue state is 'inactive', where it should have been 'receives occuring'.

    I tried starting the queue once agin by sysmail_start_sp, still the status is 'inactive'

    Any idea why this would happen?

    Thanks
    Satya

  10. satya.sqldba New Member

    The status is back to receives_occuring but the same error keeps repeating in the error log for the mail<br /><br />The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2007-03-06T13:55:0<img src='/community/emoticons/emotion-11.gif' alt='8)' />. Exception Message: Could not connect to mail server. (An established connection was aborted by the software in your host machine). )<br /><br /><br />
  11. MohammedU New Member

    Are there any errors in SQL error log or mail log?

    What is the status when you run the following sp?
    EXECUTE msdb.dbo.sysmail_help_status_sp ;

    Did you through SSMS to remove profiles?

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  12. satya.sqldba New Member

    MOhammed

    The status is 'started when I run the query.

    The error from mail log was the one that I posted before and the sql server log has error message:

    'The messenger service has not been started,netsend notifications will not be sent.'

    Is that related? If not just curious , whats that error in sql server error log mean?

    Thanks
    Satya
  13. satya.sqldba New Member

    One more clarification Mohammed


    You said the sqlagent has to have some permissions on the exchange server. What are the permissions that have to be given to sqlagent on the exchange server inorder for the dbmail to work?

    Thanks
    Satya
  14. MohammedU New Member

    In sql mail configuration you check SMTP mail authentication in "New Database Mail Account" window...
    There will be three options ..

    1. Windows Authentication using Database Engine service credentials
    Connection is made to the SMTP server using the credentials configured for the SQL Server Database Engine.

    2. Basic Authentication
    Specify a user name and password required by the SMTP server, other than the credentials of the SQL Server Database Engine.

    3. Anonymous authentication
    Mail is sent to the SMTP server without login credentials. Use this option when the SMTP server does not require authentication.



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  15. satya.sqldba New Member

    Mohammed

    I have used Windows authentication. I wanted to know if the account has to be given any specific permissions on the exchange server as you said in one of your previous replies.

    Are there any exclusive permisssions that have to be given on the exchange server?

    Thanks
    Satya
  16. MohammedU New Member

    I mean SMTP authentication...
    I believe it is depends on your SMTP configuration...Check with your system/exchange admin...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  17. satya.sqldba New Member

    Mohammed

    The problem is solved. Port 25 was being blocked by anti-virus program. Got hold of n/w team yesterday to resolve this issue.

    Thanks for the help

    Satya
  18. MohammedU New Member

    Cool...
    Good to know the problem solved...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  19. satya.sqldba New Member

    Mohammed

    The story takes a new twist.

    The mails are going out fro the server i.e, the test e-mail is working but the jobs are not firing the emails. When I look at the error log the message is

    Message
    [298] SQLServer Error: 14607, profile name is not valid [SQLSTATE 42000]

    I tried re-dreating the profile (and accounts too) more than 4 times, still the same error pops up whenever a notification has to be sent from jobs

    Thanks
    Satya
  20. MohammedU New Member

    Did you enable mail profile in SQL Agent Mail Session?
    If not right click the SQL Agent/Alert system/Enable Mail profile and restart the sql agent....


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  21. satya.sqldba New Member

    Mohammed

    I have done that too, and even tried re-starting the agent, still it is the same way.

    Thanks
    Satya
  22. satya.sqldba New Member

    Mohammed

    Success atlast. SQL server just sent out email for job notifications. Restarting the agent not once but twice worked out finally.

    Thanks
    Satya
  23. MohammedU New Member

    Are you able see the mail profiles in sql agent drop down list and what is the error you are getting when try to send the test email from SQL Agent properties window?

    MohammedU.
    Moderator
    SQL-Server-Performance.com
  24. satya.sqldba New Member

    Now everything is ok. The error which I got was


    Message
    [298] SQLServer Error: 14607, profile name is not valid [SQLSTATE 42000]


    Then I googled and learnt something abt the error and tried re starting the agent. I executed a job and the same error repeated. The first time it didnt work but when I tried my luck restarting for the second time and executed a job, it sent out an email.

    Thanks
    Satya
  25. ori500 New Member

    Hi
    I've problem with the dbmail basic authentication.
    when I send/check by dbmail to other mail server I get
    "mail server failure,..The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.1 Relaying denied. Please use SMTP Authentication."
    Although I enterd the authentication details and it is correct.
    After speaking with the mail host I guess the problem is that the authentication details are not passing.

    Any Idea ?

    Thanks ,
    Ori
  26. MohammedU New Member

    I don't think "Relaying denied" is SQL issue...

    In Database Mail configuration wizard...
    Check the SMTP authentication...
    If you need to pass different log and password then use BASIC AUTHENTICATION instead of WINDOW or ANONYMOUS....



    MohammedU.
    Moderator
    SQL-Server-Performance.com
  27. satya Moderator

    See thishttp://www.experts-exchange.com/Software/Server_Software/Email_Servers/SendMail/Q_21099793.html post on relaying denied causes and as suggested by Mohammed you have to check the privileges & passage of parameters.



    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page