Though this is a very old topic; DB Mail being introduced in SQL Server 2005 replacing annoying SQL Mail, i would like to brief about this topic because………………….
Well, i was configuring alerts using PBM(Policy Based Management) on one of our brand new SQL Server 2008R2 Instances today in such a way that if any policy has been violated, SQL Will send an email to our DBAops Group. I will cover this topic in upcoming posts! Prior to this, for SQL to be able to send email notifications, we’ve to configure DB Mail. In this demo let’s see how to send emails from SQL Server to Gmail. you can test alerts, operators, notifications, log shipping/Mirroring notifications, Job failure notifications and what not……(Basically you can simulate a real environment at home to learn things) on your own Personal laptop where you are trying to learn and explore new things within SQL Sever. why to wait?? Let’s proceed…then
First of all of course you need to have a GMAIL account 🙂 . If you don’t want your personal email account for SQL Server sending alerts, create a test account for this purpose. I did the same under the name sqlssms1@gmail.com
Note – Gmail smtp sever: smtp.gmail.com
Go to your Account Settings and make sure that POP is enabled for your account as shown below.
Save your settings and you can sign off from your GMail. Now, let’s go to your SQL Server and very first thing which you’ve to make sure is your DB Mail XP’s are enabled. Just Query sp_configure ‘database mail’ and make sure that the running value returned is ‘1’ as shown below. Also, Service Broker should be enabled on MSDB Database. You can execute select name,is_broker_enabled from sys.databases where name = ‘msdb’ and make sure that “1” is returned.
Go to your Object Explorer – expand Management Node and select Configure Database mail as shown below.
Follow the below in Sequence as shown below.
Next – > and Click on Add to add new SMTP account to the profile as shown below.
Once you select Add, you’ll be welcomed with the below Screen, where we’ve to configure our account details and provide the smtp details for gmail.
Things to note in the above screenshot: Gmail Requires SSL to be checked and Port 587. Now Select Next and I’ve chosen this as my Default Public Profile as you can see below.
Select next->Next-> and Finish and you’ll be seeing a success message as shown below.
That’s all we need to do for configuring your DB Mail on SQL Server. Now, let’s see by sending a test email from SQL Server. Please see the below screenshots…
I’ve entered the same gmail account for testing purposes, and you can see the email being delivered to my Inbox as shown below…
Tadaaaaaaaaaa!!! That’t it Guys…..All I can say is, now you are all set to play with all your Automated(SQL Agent Jobs) stuff , create Operators, configure in such a way that SQL will send you notifications to the operator. Now it’s all yours!! In the upcoming Post, Let’s see how can we configure PBM to send email alerts, if any of the policies has been violated.
Hope this info is useful..Cheers!!
MSSQLTREK 
Hi Sree, thanks for the detailed steps for setting up DB mail. I tried the same on my test server, but its getting failed. I could find the below error from the DM mail logs.
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2012-05-16T13:39:06). 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 173.194.79.109:587).
)
Please suggest me where I’m wrong here.
Interesting…Mail Server Failure?? Are you sure that you enabled POP and using smtp.gmail.com?? Also…that port needs to be Opened(both Incoming/outgoing) in your Firewall.
Sorry I have no idea on this, Can you please help me.
From control panel I can see Windows fire wall has been disabled (Firwall is off) on all Domain, Private and Public profiles. But it is saying connected for Domain and diconnected for both Public and Private profiles.
I have enabled POP in gmail.
Can you send me the Screenshot of your DB Mail config?
Unable to paste the screen shot. But it is similar to the one which you have shown here. All the configurations have set as you said.
i’ve tried and get error like Chandru
i have 2 account at database mail, google and internal mail i had mail server. When i used internal mail, i has success, but when i used google , i found faild. Whereas i used seeting as you guide above. My firewal at database server has OFF , and i have been setting up at Google account setting. What should i set next ?
Hi Hendra! You got the DB Mail configuration part correct as you were able to send emails using internal SMTP Server. Coming to your Gmail, What port you are using for Gmail and the smtp server??
I’ll give it a try sometime today or tomorrow sending emails to my Gmail from my new SQL2012 Instance. Will let you know if I find any new changes made from Google with Ports/POP/SMTP settings…
i used port 25 on mail server and 587 on gmail and SSL enabled
As promised, I tried configuring DB Mail on a different Instance(2008R2 SP1) with Port 587 and smtp.gmail.com and it worked just like champ….Very Clean DB Mail Logs.
Date 10/18/2012 7:49:53 PM
Log Database Mail (Database Mail Log)
Log ID 1
Process ID 4612
Last Modified 10/18/2012 7:49:53 PM
Last Modified By SREEKANTHPC\SQLDBEngineDR
Message
DatabaseMail process is started
Got an email to my Inbox in few seconds “This is a test e-mail sent from Database Mail on SREEKANTHPC\DR.”
Are you trying from your Corporate Domain or from your Personal Laptop from your Home?
Working good. thanks
Could you please send same steps for yahoo? I tried same for yahoo as like gmail does not able to get test mail
How to configure DB mail in SQL Server Express edition
Thanks for reply
You can’t use DB Mail in express edition. Looks like there’s a workaround though, Leveraging CLR. See this article..I am not sure if it works or not. I never had to use DBMAIL on Express edition.
http://www.sqlservercentral.com/blogs/querying-microsoft-sql-server/2013/09/02/sending-mail-using-sql-server-express-edition/