Problem with Database mail

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

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

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

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

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


MohammedU.
Moderator
SQL-Server-Performance.com

Mohammed

It has sysadmin permissions

Thanks
Satya

sysadmin is local what about the permission on SMTP server?

MohammedU.
Moderator
SQL-Server-Performance.com

Mohammed

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

Thanks
Satya

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

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 />

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

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

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

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

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

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

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

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


MohammedU.
Moderator
SQL-Server-Performance.com

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

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

Mohammed

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

Thanks
Satya

Mohammed

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

Thanks
Satya

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

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

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

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