Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Setup SQL Server Database Mail to use a Gmail, Hotmail, Yahoo or AOL account

MSSQLTips author Mohammed Moinudheen By:   |   Read Comments (31)   |   Related Tips: More > Database Mail
Problem

One great feature of SQL Server is the ability to get alerts when there are issues.  The alert process can send email notifications using Database Mail after you have configured your SMTP settings and setup your jobs and alerts to use this account.  In some cases you may not have a mail server, but still want to setup alerts.  In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc...

Solution

For this example, I have a SQL Server test environment configured and I want to test the alert mechanism using Hotmail. The following outlines the settings to do this.

Configuring Database Mail

If you are not familiar with setting up Database Mail you can refer to this link: http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/.  This will work for later versions of SQL Server as well.

When navigating through the database mail configuration wizard, you will encounter the below screen wherein the relevant details needs to be entered. Let's consider the example of using the SMTP server details for Hotmail.  Enter the details as shown below.

setup database mail wizard

Under 'Outgoing Mail Server (SMTP)'

  • E-mail Address - Enter your Hotmail or Live account
  • Server Name - Enter as shown in screenshot
  • Port number - Enter as shown in screenshot
  • The server requires a secure connection (SSL) - check this box
    If this is left unchecked you will encounter this error message, 'The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 4 (2011-12-14T23:36:13). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must issue a STARTTLS command first).'

Under 'SMTP Authentication'

  • Select 'Basic authentication'
  • User Name - Enter your Hotmail or Live account
  • Password -  Enter password of your Hotmail or Live account.
  • Confirm Password - Enter password of your Hotmail or Live account.

The below table outlines the necessary SMTP server details and port numbers for Hotmail, Gmail, Yahoo and AOL (see this for more info http://support.microsoft.com/kb/2352963).

SMTP Server Details

Hotmail        SMTP server name: smtp.live.com                    Port number:   587
Gmail           SMTP server name: smtp.gmail.com                  Port number:   587
Yahoo          SMTP server name: smtp.mail.yahoo.com          Port number :  25
AOL             SMTP server name: smtp.aol.com                    Port number :  587

Here are some additional links you can refer to that show how to configure Microsoft Outlook to connect to Gmail, Hotmail etc... which is similar to what needs to be done for Database Mail.

Sending a Test Email

Once your database mail profile has been configured you can send test emails for validation to verify setup is configured correctly.  Right click on Database Mail and select Send Test E-Mail as shown below.

send database mail test email from ssms

You could also execute the Database Mail system procedure sp_send_dbmail with the appropriate parameters using this sample script below.

EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'Enter valid database profile name',
   @recipients = 'Enter Valid Email Address',
   @body = 'This is a test email sent from TEST server',
   @subject = 'TEST EMAIL',
   @importance ='HIGH'

Database Mail Troubleshooting

After testing, if you are unable to receive notification emails, you could use the below Database Mail views for troubleshooting.

select * from msdb.dbo.sysmail_sentitems 

This contains one row for each message successfully sent by Database Mail.

select * from msdb.dbo.sysmail_unsentitems 

This contains one row for each message that has an unsent or retrying status.

select * from msdb.dbo.sysmail_faileditems 

This contains one row for each Database Mail message that has a failed status.

Here are some additional views sysmail_event_log and sysmail_allitems. There is also a Database Mail system stored procedure msdb.dbo.sysmail_help_queue_sp which could be used as well. 

For detailed steps on troubleshooting database mail issues, refer to this link: http://msdn.microsoft.com/en-us/library/ms188663.aspx

Summary

The above steps were performed using SQL Server 2008 R2 and I tested using Hotmail and Gmail.  This should work for any version of SQL Server 2005 and greater.

Note: If your company has an SMTP server I strongly urge that you use your companies SMTP server and only use this where you don't have access to an SMTP server or if you need to test Database Mail.

Next Steps


Last Update: 1/6/2012


About the author
MSSQLTips author Mohammed Moinudheen
Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, January 06, 2012 - 7:59:20 AM - rajendra Read The Tip

Thanks for the Tip.


Friday, January 06, 2012 - 8:08:01 AM - Jason Yousef Read The Tip

Nice one, Thanks!


Friday, January 06, 2012 - 10:31:14 AM - Alen Teplitsky Read The Tip

nice article but a potential security hole. if you open up outbound smtp from any endpoint behind your firewall then hackers/spammers can use this to send out spam.


Saturday, January 07, 2012 - 12:00:13 PM - Patel H Read The Tip

I tried using yahoo account but get below error in the database mail logs. 

 

select

*from msdb.dbo.

sysmail_faileditems  - can see here the unsent messages..


Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2012-01-07T16:55:58). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 98.139.212.139:25).

I am not sure what is missing..as I have done exacty what been said in the article..plz advise..

 


Saturday, January 07, 2012 - 1:51:47 PM - Jason Yousef Read The Tip

You need to pay to get a smtp access for yahoo. unless you use http://ypopsemail.com/.

 


Monday, January 09, 2012 - 7:01:48 AM - Mohammed Moinudheen Read The Tip

@Rajendra:Thanks for your comments

@Alex: I agree, Thanks for your comments.

@Jason: Thanks for mentioning this link:http://ypopsemail.com. While testing I had used only hotmail and gmail. So I was not aware about the problem faced by Patel when he used his yahoo account.

 


Monday, January 09, 2012 - 7:45:59 AM - Patel H Read The Tip

Blocked by port blocking rule (Anti-virus Standard Protection:Prevent mass mailing worms from sending mail).  -  This error I see in my db mail logs which maks me think that anti virus on my machine may be blocking this...

is that so??

If not what the other ways of getting round using yahoo mail plz?


Monday, January 09, 2012 - 8:09:25 AM - Patel H Read The Tip

I get same anti virus error mentined in my last comment even when trying with hotmail..


Monday, January 09, 2012 - 8:10:57 AM - Mohammed Moinudheen Read The Tip

@Patel: I guess due to this antivirus setting you would face this error irrespective of whether you are using your yahoo, gmail or hotmail accounts. By the by, which anti virus are you using. You might require to disable this port blocking rule.


Monday, January 09, 2012 - 8:13:34 AM - Mohammed Moinudheen Read The Tip

@Patel: Just to remind, I would not recommend to change anything on your company laptop.


Monday, January 09, 2012 - 11:18:28 AM - Ankit Shah Read The Tip

nice article which i was looking for to setup on my local machine and you comes out with this.

Great.

thanks


Monday, January 09, 2012 - 11:53:29 AM - Patel H Read The Tip

Its McFee -  I know I wouldnt remove antivirus on my work machine....I think I have answered my own question...but thanks..


Wednesday, January 11, 2012 - 6:56:35 AM - bojanna mk Read The Tip

Good article Moinu.Really Intersting!


Wednesday, January 11, 2012 - 7:27:58 AM - Srinath Read The Tip

Thank you for such a Very nice article...Keep going Moinu...!!


Friday, March 09, 2012 - 9:36:57 AM - ron Read The Tip

What does " @Profile_name " refer to in your sample script?

thanks


Friday, March 09, 2012 - 11:20:30 AM - Mohammed Moinudheen Read The Tip

Ron,

@profile_name refers to your valid mail profile. You can get details of the available profiles in your instance by executing this procedure.

EXECUTE msdb.dbo.sysmail_help_profile_sp;

Thanks


Sunday, March 11, 2012 - 10:42:27 AM - Ameer Khan Read The Tip

Good Article Moin


Thursday, May 17, 2012 - 4:57:40 AM - Ganpat Sharma Read The Tip

 

Hi everyone. i am sending email from sql server 2008 r2.   database mail configuration details set as : -------------------------------------------------------------- Server Name : smtp.gmail.com Port No : 587 Set Basic Authentication : User Name, Password   i tried more but email sending is failed and error occur is-:   The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 2 (2012-05-03T15:46:30). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 173.194.79.108:587). )   Thanks and Regards: Ganpat Sharma

Monday, July 02, 2012 - 10:13:54 AM - john the rule Read The Tip

Super ...........


Monday, July 02, 2012 - 12:28:21 PM - JIT Read The Tip

 

I Configured Database Mail.It worked fine then our organization changed the smtp server.

After Configuring Database Mail With New smtp Server , Database Mail Log is Showing Error

Exception Message: Cannot send mails to mail server. (Failure sending mail.)

We Are Using Port 465  & I tried telnet also.It is working fine but still database mails are failed

Plz help me its very urgent !!!!!!!!!

 


Monday, July 02, 2012 - 11:55:22 PM - Mohammed Moinudheen Read The Tip

@JIT,

Did you try restarting the SQL Server Agent?


Tuesday, July 03, 2012 - 1:53:58 AM - Jit Read The Tip

 

Ya I Restarted SQL Server Agent Also. But Still the same error


Wednesday, July 25, 2012 - 1:26:22 AM - Nauman Ikram Read The Tip

Sir, if I configured any gmail or hotmail account, I only want to get my emails with their attachments in sql server. Furthere I want to parse the attachment and save the data in database tables.

Is it possible to get the mails in sql server ?

 


Tuesday, July 31, 2012 - 3:34:50 AM - sandeep Read The Tip

Thanks its working your explanation is so clear thanks for posting


Wednesday, April 17, 2013 - 5:44:37 PM - karthik Read The Tip

Hi,

I have scheduled Database backup by using sql server management plan for the time mentioned. The backup is started & completed in the given time. Once the backup is completed i wanted to send email alert that backup is completed to the specific account. Can you please guide me what are the steps that I need to configure this activity. 

Please send me the details how to do this activity as soon as possible.

My email address is kvallamp@gmail.com

 


Wednesday, July 10, 2013 - 2:27:16 AM - sainadh Read The Tip

Superb article.. 


Tuesday, August 20, 2013 - 2:54:28 AM - SQLDBA Read The Tip

Hi,

Please check following error come.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-08-20T12:17:17). Exception Message: Could not connect to mail server. (No connection could be made because the target machine actively refused it 173.194.79.108:587).
)

 

Regards,

SQLDBA


Thursday, August 29, 2013 - 7:22:58 AM - vys Read The Tip

 

Thanks , Nice article ,its helped me a lot - keep updating 


Sunday, September 22, 2013 - 1:14:48 AM - SQLDBA Read The Tip

Hi,

I AM getting following error.

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-09-03T23:19:40). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.7.0 Must issue a STARTTLS command first. xl3sm23771591pbb.17 - gsmtp).
)

 

 


Sunday, September 22, 2013 - 1:16:36 AM - SQLDBA Read The Tip

Hi,

 

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-09-03T23:30:45). Exception Message: Cannot send mails to mail server. (The SMTP server requires a secure connection or the client was not authenticated. The server response was: 5.5.1 Authentication Required. Learn more at).
)


Sunday, September 22, 2013 - 1:29:34 AM - SQLDBA Read The Tip

hi,

 

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2013-09-22T10:56:05). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond 106.10.150.171:25).
)

 

 



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.