Configuring SQL Server DB Mail to send emails to your Gmail/Yahoo Mail.

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!!

Advertisements

15 comments

  1. 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.

  2. 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.

    1. 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.

      1. 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.

  3. 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 ?

  4. 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…

  5. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s