{"id":840,"date":"2015-02-24T16:15:25","date_gmt":"2015-02-24T10:15:25","guid":{"rendered":"http:\/\/dotnetsql.info\/net-sql\/?p=840"},"modified":"2015-07-09T14:50:11","modified_gmt":"2015-07-09T08:50:11","slug":"database-mail-setup-for-gmailhotmailyahoo-or-aol","status":"publish","type":"post","link":"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/","title":{"rendered":"Database mail setup for Gmail,Hotmail,Yahoo or AOL"},"content":{"rendered":"<p><strong>Scenario<\/strong><\/p>\n<p>One great feature of SQL Server is the ability to get alerts when there are issues.\u00a0 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.\u00a0 In some cases you may not have a mail server, but still want to setup alerts.\u00a0 In this tip we will walk through how you can setup Database Mail to use email services like Gmail, Hotmail, Yahoo, etc&#8230;<\/p>\n<p><strong>Solution<\/strong><\/p>\n<p>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.<!--more--><\/p>\n<h3><strong>Configuring Database Mail<\/strong><\/h3>\n<p>When navigating through the database mail configuration wizard, you will encounter the below screen wherein the relevant details needs to be entered. Let&#8217;s consider the example of using the SMTP server details for Hotmail.\u00a0 Enter the details as shown below.<\/p>\n<p>Under <strong>&#8216;Outgoing Mail Server (SMTP)&#8217;<\/strong><\/p>\n<ul>\n<li><em>E-mail Address<\/em> &#8211; Enter your Hotmail or Live account<\/li>\n<li><em>Server Name &#8211; <\/em>Enter as shown in screenshot<\/li>\n<li><em>Port number<\/em> &#8211; Enter as shown in screenshot<\/li>\n<li><em>The server requires a secure connection (SSL)<\/em> &#8211; check this box<br \/>\nIf this is left unchecked you will encounter this error message, <em>&#8216;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).<\/em>&#8216;<\/li>\n<\/ul>\n<p>Under <strong>&#8216;SMTP Authentication&#8217;<\/strong><\/p>\n<ul>\n<li>Select &#8216;Basic authentication&#8217;<\/li>\n<li><em>User Name<\/em> &#8211; Enter your Hotmail or Live account<\/li>\n<li><em>Password<\/em> &#8211;\u00a0 Enter password of your Hotmail or Live account.<\/li>\n<li><em>Confirm Password<\/em> &#8211; Enter password of your Hotmail or Live account.<\/li>\n<\/ul>\n<p>The below table outlines the necessary SMTP server details and port numbers for Hotmail, Gmail, Yahoo and AOL.<\/p>\n<table style=\"width: 600px;\" border=\"1\" width=\"600\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td>\n<p align=\"center\"><strong>SMTP Server Details<\/strong><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td>Hotmail\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>SMTP server name<\/strong>: smtp.live.com\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Port number:\u00a0\u00a0 587<\/td>\n<\/tr>\n<tr>\n<td>Gmail\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>\u00a0SMTP server name<\/strong>: smtp.gmail.com\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Port number:\u00a0\u00a0 587<\/td>\n<\/tr>\n<tr>\n<td>Yahoo\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>\u00a0SMTP server name<\/strong>: smtp.mail.yahoo.com\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Port number :\u00a0\u00a025<\/td>\n<\/tr>\n<tr>\n<td>AOL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 <strong>\u00a0SMTP server name<\/strong>: smtp.aol.com\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Port number :\u00a0\u00a0587<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h3><strong>Sending a Test Email<\/strong><\/h3>\n<p>Once your database mail profile has been configured you can send test emails for validation to verify setup is configured correctly.\u00a0 Right click on Database Mail and select Send Test E-Mail as shown below.<\/p>\n<p>You could also execute the Database Mail system procedure <strong>sp_send_dbmail<\/strong> with the appropriate parameters using this sample script below.<\/p>\n<pre>EXEC msdb.dbo.sp_send_dbmail\n\n\u00a0\u00a0\u00a0 @profile_name = <strong>'Enter valid database profile name<\/strong>',\n\n\u00a0\u00a0\u00a0 @recipients = <strong>'Enter Valid Email Address<\/strong>',\n\n\u00a0\u00a0\u00a0 @body = 'This is a test email sent from TEST server',\n\n\u00a0\u00a0\u00a0 @subject = 'TEST EMAIL',\n\n\u00a0\u00a0\u00a0 @importance ='HIGH'<\/pre>\n<h3><strong>Database Mail Troubleshooting <\/strong><\/h3>\n<p>After testing, if you are unable to receive notification emails, you could use the below Database Mail views for troubleshooting.<\/p>\n<pre>select * from msdb.dbo.<strong>sysmail_sentitems<\/strong><\/pre>\n<p>This contains one row for each message successfully sent by Database Mail.<\/p>\n<pre>select * from msdb.dbo.<strong>sysmail_unsentitems<\/strong><\/pre>\n<p>This contains one row for each message that has an unsent or retrying status.<\/p>\n<pre>select * from msdb.dbo.<strong>sysmail_faileditems<\/strong><\/pre>\n<p>This contains one row for each Database Mail message that has a failed status.<\/p>\n<p>Here are some additional views <strong>sysmail_event_log <\/strong>and<strong> sysmail_allitems<\/strong>. There is also a Database Mail system stored procedure <strong>msdb.dbo.sysmail_help_queue_sp <\/strong>which could be used as well.<\/p>\n<h3><strong>Summary<\/strong><\/h3>\n<p>The above steps were performed using SQL Server 2008 R2 and I tested using Hotmail and Gmail.\u00a0 This should work for any version of SQL Server 2005 and greater.<\/p>\n<p>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&#8217;t have access to an SMTP server or if you need to test Database Mail.<\/p>\n<p><strong>Thanks for reading this article,<\/strong><\/p>\n<p><strong>Next steps :<\/strong><\/p>\n<ol>\n<li><strong>Share this with your colleagues because Sharing\u00a0 is Learning<\/strong><\/li>\n<li><strong>Comment below if you need any assistance<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: right;\"><em><strong>Powered by <a href=\"http:\/\/www.codereview.co\">CodeReview &#8211; Let&#8217;s make it Better!<\/a><\/strong><\/em><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-840\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-840\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-840\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-840\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-840-69d2f83615c56' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=840&amp;origin=dotnetsql.info&amp;obj_id=76243027-840-69d2f83615c56' data-name='like-post-frame-76243027-840-69d2f83615c56'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"excerpt":{"rendered":"<p>Scenario One great feature of SQL Server is the ability to get alerts when there are issues.\u00a0 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.\u00a0 In some cases you may not have a mail server, but &hellip; <a href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Database mail setup for Gmail,Hotmail,Yahoo or AOL<\/span> <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n<div class=\"sharedaddy sd-sharing-enabled\"><div class=\"robots-nocontent sd-block sd-social sd-social-icon sd-sharing\"><h3 class=\"sd-title\">Share this:<\/h3><div class=\"sd-content\"><ul><li class=\"share-twitter\"><a rel=\"nofollow\" data-shared=\"sharing-twitter-840\" class=\"share-twitter sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=twitter\" target=\"_blank\" title=\"Click to share on Twitter\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Twitter (Opens in new window)<\/span><\/a><\/li><li class=\"share-linkedin\"><a rel=\"nofollow\" data-shared=\"sharing-linkedin-840\" class=\"share-linkedin sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=linkedin\" target=\"_blank\" title=\"Click to share on LinkedIn\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on LinkedIn (Opens in new window)<\/span><\/a><\/li><li class=\"share-facebook\"><a rel=\"nofollow\" data-shared=\"sharing-facebook-840\" class=\"share-facebook sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=facebook\" target=\"_blank\" title=\"Share on Facebook\"><span><\/span><span class=\"sharing-screen-reader-text\">Share on Facebook (Opens in new window)<\/span><\/a><\/li><li class=\"share-google-plus-1\"><a rel=\"nofollow\" data-shared=\"sharing-google-840\" class=\"share-google-plus-1 sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=google-plus-1\" target=\"_blank\" title=\"Click to share on Google+\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Google+ (Opens in new window)<\/span><\/a><\/li><li class=\"share-pocket\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-pocket sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=pocket\" target=\"_blank\" title=\"Click to share on Pocket\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to share on Pocket (Opens in new window)<\/span><\/a><\/li><li class=\"share-email\"><a rel=\"nofollow\" data-shared=\"\" class=\"share-email sd-button share-icon no-text\" href=\"http:\/\/dotnetsql.info\/net-sql\/index.php\/database-mail-setup-for-gmailhotmailyahoo-or-aol\/?share=email\" target=\"_blank\" title=\"Click to email this to a friend\"><span><\/span><span class=\"sharing-screen-reader-text\">Click to email this to a friend (Opens in new window)<\/span><\/a><\/li><li class=\"share-end\"><\/li><\/ul><\/div><\/div><\/div><div class='sharedaddy sd-block sd-like jetpack-likes-widget-wrapper jetpack-likes-widget-unloaded' id='like-post-wrapper-76243027-840-69d2f8361613f' data-src='\/\/widgets.wp.com\/likes\/#blog_id=76243027&amp;post_id=840&amp;origin=dotnetsql.info&amp;obj_id=76243027-840-69d2f8361613f' data-name='like-post-frame-76243027-840-69d2f8361613f'><h3 class='sd-title'>Like this:<\/h3><div class='likes-widget-placeholder post-likes-widget-placeholder' style='height:55px'><span class='button'><span>Like<\/span><\/span> <span class=\"loading\">Loading...<\/span><\/div><span class='sd-text-color'><\/span><a class='sd-link-color'><\/a><\/div>","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[25],"tags":[124,218,217],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/840"}],"collection":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/comments?post=840"}],"version-history":[{"count":3,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/840\/revisions"}],"predecessor-version":[{"id":989,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/posts\/840\/revisions\/989"}],"wp:attachment":[{"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/media?parent=840"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/categories?post=840"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/dotnetsql.info\/net-sql\/index.php\/wp-json\/wp\/v2\/tags?post=840"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}