Cannot send mails to mail server. Mailbox unavailable. The server response was: 5.7.1 Unable to relay. - Database Mail Error in SQL Server 2005 64-bit

Installed Database mail on SQL 2005 64-bit cluster but unable to send mail. Started troublshooting activity and encountered so many errors, check out the stories below and you will be amazed to see what was the actual root cause.

Below are some Error Messages Encountered
Database Mail Error Messages:
Sending Mail using Account 1. Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: 5.7.1 Unable to relay for xyz@abc.com

DBMail shows Activation successful in the log but the all items shows status as "unsent". It was showing status as "Failed" when the user was not part of DatabaseMailUser Role in msdb.

When try to ran sp_readerrorlog, Error Message Returned:
Msg 22004, Level 16, State 1, Line 0
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 0
error log location not found

Restarted SQL Server Agent. Error Message returned:
SQL Server: The logon attempt failed [SQLSTATE HY000] 780, Cannot generate SSPI context [SQLSTATE HY000]

To Verify the Domain account, Logged onto Server using domain account and error message returned was:
Unable to log on Logon failure: user account restriction. Possible reasons are blank passwords not allowed, logon hour restrictions, or a policy restriction has been

Event viewer recorded below message under Database Mail Category:
There was an error on the connection. Reason: Cannot generate SSPI context., connection parameters: Server Name: MDMGRPDB\MDMPRD, Database Name: msdb
Cannot generate SSPI context.

LSA Error:
Event Source: LSASRV
Event Category: SPNEGO (Negotiator)
The Security System detected an authentication error for the server MSSQLSvc/SQLSERVER.company.com:2452. The failure code from authentication protocol Kerberos was "The attempted logon is invalid. This is either due to a bad username or authentication information. (0xc000006d)".

SQL Server Agent Error recorded in Event Viewer:
The SQLAgent$MDMPRD service was unable to log on as Domain\clustuser with the currently configured password due to the following error:
The referenced account is currently locked out and may not be logged on to.
The SQL Server Agent (MDMPRD) service failed to start due to the following error: The service did not start due to a logon failure.

Cluster Error Messages in Event Viewer:
[sqagtres] OnlineThread: Error 42d bringing resource online.
[sqagtres] OnlineThread: ResUtilsStartResourceService failed (status 42d)
[sqagtres] StartResourceService: StartService (SQLAgent$MDMPRD) failed. Error: 42d

Root Cause for all above issues:
Password for domain account with which SQL Server Agent Cluster Services was running got expired.

Corrective Action:
Reset the password for domain account and started services.

Preventive Action:
Disable Password Expiration Policy in AD for Domain Account.

Send mail using smtp.gmail.com from Database mail (DBMail) in SQL Server 2005 2008

Just started research on how to send mail through gmail smtp service from database mail in SQL Server 2005 and 2008 after seeing a request from one of the user in forum.

There are two methods available (Click on name to get details else scroll down)
1. Use SSL Wrapper.
2. Directly configure DBMail to connect to smtp.gmail.com

Method 1:
Step 1: Install stunnel http://www.stunnel.org/
Add below settings to stunnel.conf after installation

service=gmailSmtp
[gmailSmtp]
accept = 127.0.0.1:111
connect = smtp.gmail.com:465 (If 465 is not working then try 587)

2. Change Database mail smtp server to 127.0.0.1 port: 111 (do not check SSL)

Method 2:
In database mail when you create an account, use these values.

Account name: SQL Alerts System
Description: Alerts from SQL Server

Outgoing mail Server (SMTP)

Email id: yourname@gmail.com
Display Name: SQL Alerts
Reply email: yourname@gmail.com
Server name: smtp.gmail.com
Port no: 587 (If this does not work then try 465)
Check : This server requires a secure connection
Check Basic Authentication
username: yourname@gmail.com
password: yourpassword
confirm password: yourpassword
click next and also you need to make it default public profile.

After you do this, you also have to do below change in SQL Server Agent properties
In Properties click alerts system and then select database mail.
Now restart only SQL SERVER AGENT and test your database mail.

Check Status:
Right click database mail, select view database mail logs keep refreshing to see if any error message had occured. mean while look in your inbox if you recieved your email.
Still if you are not receiving email then firewall could be blocking the communication so test it using telnet command as below
1. Go to Start --> Run --> Type "cmd" or "command.com"
2. Now once command prompt is opened then type "telnet smtp.gmail.com 587" without double quotes in the promt and you should get greeting message as "220 mx.google.com ESMTP...." then there is no firewall block so type "quit" and close session. If you are not getting above message then there is some firewall block issue which will show message like "Connecting To smtp.google.com...Could not open connection to the host, on port 587: Connect failed"

Script to Create Database Mail in SQL Server 2005

------------- Enable advanced options --------------
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

----------Configure Mail Account--------------------------
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLAlerts',
@description = 'SQLAlerts',
@email_address = 'sqlserver_dbateam@tunein.in',
@display_name = 'SQLAlerts',
@mailserver_name = 'mailserver.tunein.in'

-----------Creating Profile-----------------------------
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLAlerts',
@description = 'Profile used for sending SQLAlerts'

----------Add the Database Mail account we created in step 2, to the Database Mail profile created in Step3----
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLAlerts',
@account_name = 'SQLAlerts',
@sequence_number = 1

------- Send Test Mails using the profile created------------------
declare @bodymsg varchar(100)
set @bodymsg = 'Server :'+@@servername+ ' First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='sakthi@tunein.in',
@subject = 'Test Mail',
@body = @bodymsg,
@body_format = 'HTML' ;