Some might say that Database Mail is one of the best features shipped in SQL Server since SQL Server 2005. It allows you to send mails without writing a single line of script. However, there is a general perception that you must have use an internal mail server to configure Database Mail in SQL Server. However, one of the benefits of SQL Server Database mail is that is is not mandatory that you must have your own mail server. You can easily configure, test, and implement it on any free email servers like Gmail, Yahoo, and Hotmail.
You just need to configure an email account as normal, and use the proper settings when you get to the correct property page during the wizard.
- Create a free email account using Gmail, Yahoo, or Hotmail.
- Configure the target server to use that email account
- Test the server by having it send you an email to the free email account
Create a free email account
Go to gmail.com, yahoo.com, or hotmail.com and register for a new account. Make sure you pick an easy to remember email address as you will have to spell it correctly later.
Configure SQL Server
To configure Database Mail using a wizard
- In SSMS, expand the node for the instance you want to configure Database mail.
- Expand the Management node.
- Tight-click Database Mail, and then click Configure Database Mail.
- Complete the New Account Wizard dialog
Use this page to create a new Database Mail account. A Database Mail account contains information for sending e-mail to an SMTP server.
A Database Mail account contains the information that SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server.
A Database Mail account is only used for Database Mail. A Database Mail account does not correspond to a SQL Server account or a Microsoft Windows account. Database Mail can be sent using the credentials of the SQL Server Database Engine, using other credentials that you supply, or anonymously.
The most important data that you must enter in this screen is its Server name (Actually SMTP Server name). In addition, you can use the Port No 25 but sometimes this port is blocked in your network. If you come across this situation, work with your system administrator to open up port 25, or use an alternate port like 587.
Given below are the SMTP addresses of the FREE mail accounts:
- Yahoo : smtp.mail.yahoo.com
- Gmail : smtp.gmail.com
- Hotmail : smtp.live.com
In addition, you need to make sure that your email email account credentials are correct.
Test The SQL Server
- Using SSMS, connect to an instance of SQL Server Database Engine where Database Mail is configured, expand Management, right-click Database Mail, and then click Send Test E-Mail. If no Database Mail profiles exist, a dialog prompts the user to create a profile and opens the Database Mail Configuration Wizard.
- In the Send Test E-Mail from <instance name> dialog box, in the Database Mail Profile box select the profile you want to test.
- In the To box, type the e-mail name of the recipient of the test e-mail.
- In the Subject box, type the subject line for the test e-mail. Change the default subject to better identify your e-mail for troubleshooting.
- In the Body box, type to body of the test e-mail. Change the default subject to better identify your e-mail for troubleshooting.
- Click Send Test E-Mail to send the test e-mail to the Database Mail queue.
- Sending the test e-mail opens the Database Mail Test E-Mail dialog box. Make a note of the number displayed in the Sent e-mail box. This is the mailitem_id of the test message. Click OK.