Setting up Database Mail and send mail over internet for SQL Server 2008,2005

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

Problem
Many things have changed with SQL Server 2005 and one of these changes is the replacement of SQL Mail with Database Mail.  This is a good thing, because SQL Mail relied on having a MAPI mail client installed such as Outlook in order for it to work.  With SQL Server 2005, this has changed and now the mail services use an SMTP server to send out emails which makes it a whole lot easier to setup and maintain.  So how do you setup Database Mail?

Solution

There are two ways that you can setup Database Mail, either by using the stored procedures that are included with SQL Server 2005 or by using SQL Server Management Studio.  For this exercise we will walk through how to setup Database Mail by using the GUI.
To setup Database Mail, connect to your server and expand the Management node of the tree and then right click on "Database Mail".




Then select "Configure Database Mail' and you will get the following welcome screen and the click "Next".


The following screen will appear and select "Set up Database Mail by performing..." and click "Next".



If Database Mail has not been enabled, you will get this following screen. Just click "Yes" to enable it.  If it has already been enabled this screen will not appear.



Enter in a name for the Profile and also a description and click "Add..."



The following screen will appear.  Fill out the details for your mail account that will be used to send out email from SQL Server.  When you are done click "OK".



After you click "OK" you will be brought back to this screen and the SMTP details will now show for the account you just setup.  Click "Next" to continue.



On the next screen you will see the name of the profile that you just setup.  Click on the checkbox to allow this to be a Public profile and also select "Yes" for the default profile and then click "Next".



The following screen has some additional parameters that can be set to control how mail is sent.  You can make changes or leave the defaults.  When you are done click "Next".



A summary screen will appear that shows you all of the options that were selected.  If everything is correct click "Finish" or click "Back" to go back and make changes.



When you click "'Finish" the next screen will appear that shows you the status of installing Database Mail. When this has finished just click "Close" to close this screen.



To test Database Mail, right click on Database Mail and select "Send Test E-Mail".


Fill in a "To:" email address and change the body of the email if you want and then click "Send Test E-Mail".



After you have sent the email you will get this message box to confirm if the email was received or not.  If it was you can click "OK" to close the screen or click "Troubleshoot" which will launch the help information to see what the issue may be and how it can be resolved.



That's all there is to it.  As I mentioned before this can also be setup by using stored procedures. To look at this approach take a look at this article Database Mail in SQL Server 2005.

Next Steps

• Setting up Database Mail is not that complicated and it is much easier then SQL Mail. Take the time to see how this new approach to mail can work in your environment
• After you setup Database Mail don't forget to setup your operators, alerts and SQL Agent alert settings

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

 

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

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.

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.

• http://support.microsoft.com/kb/287604
• http://support.microsoft.com/kb/2352963

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.

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

• Get familiar with the database mail architecture (Refer to link: http://msdn.microsoft.com/en-us/library/ms189635.aspx)
• Try testing this tip in your own test environment using your personal Hotmail, Gmail accounts