Blogs from Suhas

Dirtying my hands in SQL Server

How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account

How to configure SQL Server Database Mail to send email using your Windows Live Mail Account or your GMail Account

Rate This
  • Comments 59

Hi Friends,

This post comes as a response to requests from many of our customers, who want to know the step by step process on how to configure SQL Server Database Mail to send emails using their Windows Live Mail Account or their Gmail Account.

If you are using SQL Server 2005 or higher, you might have noticed that there is now a “Database Mail” (DB Mail) option under “Management”. This is very different from the “SQL Mail” that we had on previous versions (it still exists under Management\Legacy). Using DB Mail, you no longer have to configure a mailbox on your machine, and you certainly do not need to run the SQL Server or the SQL Server Agent under the account you want to send emails from. Moreover, you can have multiple mail profiles and you can decide which account to use under various conditions.

So, here we go on the steps to configure DB Mail:

  1. Right-Click on Database Mail and choose Configure Database Mail.

    DBMail01
  2. This starts the Database Mail Configuration Wizard. Click Next.
  3. In the Select Configuration Task screen, choose “Set up Database Mail by performing the following tasks:” and click “Next”.

    DBMail02
  4. Now, you will be required to enter a Profile Name. This can be any Arbitrary Name that will help you identify the Profile. You might also want to add a Description. Now, click on Add.

    DBMail03
  5. Now, you are prompted to create a New Database Mail Account. Enter any Account Name and Description. The other parameters are as follows:
    • For configuring Windows Live Mail:

      Email address: Your Live e-mail ID
      Display name: Your name
      Reply e-mail: Any reply-to email account
      Server name: smtp.live.com
      Port number: 25
      This server requires a secure connection (SSL): Checked ON

      In the next section, choose Basic Authentication and enter the following information:
      User name: Your Live e-mail ID
      Password: Password for your Live e-mail ID
      Confirm password: Password for your Live e-mail ID

      The configuration should look like the screenshot below. Now, click OK.

      DBMail04
    • For configuring Google Mail (Gmail):

      Email address: Your Gmail ID
      Display name: Your name
      Reply e-mail: Any reply-to email account
      Server name: smtp.gmail.com
      Port number: 587
      This server requires a secure connection (SSL): Checked ON
      (Settings looked up from Gmail Help)

      In the next section, choose Basic Authentication and enter the following information:
      User name: Your gmail ID
      Password: Password for your gmail ID
      Confirm password: Password for your gmail ID

      The configuration should look like the screenshot below. Now, click OK.

      DBMail05

  6. Back on the New Profile screen, click Next. The next Manage Profile Security screen allows you to set the Public Profiles, the Private Profiles and the Default Profiles for each of the Public and Private Profiles.

    DBMail06
  7. Click Next. This will move us to the Configure System Parameters screen. You may want to tweak the parameters; however, for demonstration purposes, we will keep these as default. Now, click Next.

    DBMail07
  8. In the Complete the Wizard screen, review the parameters and click Finish. Ensure that all the 5 Actions succeed and then click Close.

    DBMail08
  9. Your DB Mail should now be configured successfully and you should be all setup to send emails using DB Mail. But before we confirm success, we will like to send a test email and confirm receipt. To do that, right-click on Database Mail and choose Send Test E-Mail…

    DBMail09
  10. Enter a To: email id and click Send Test E-Mail.

    DBMail10
  11. You should receive the email in a few seconds. If you have received the email, you have been able to configure DB Mail successfully, and you can now start using DB Mail.

Hope this post will be helpful.

Disclaimer
None of the email addresses used in this post belong to me. Please do not try to contact me in any of these email addresses, as your emails will never reach me.

All information provided here is my personal opinion and is neither verified nor approved by Microsoft before it is published. All information, and code samples, if any, is provided "AS IS" with no warranties and confers no rights.

Request to my readers: Please let me know on what topics you would like me write on. I would be happy to be of any help to my readers.

Comments
  • Suhas,

    Thanks for detailed steps.

    It is certainly going to help for those who want to use SMTP of Live or Gmail to configure Database Mail.

    Great work!

    Thanks

    Balmukund

  • when i am trying to setup account with gmail at that time i am receiving following error

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 11 (2009-09-16T15:42:39). 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. 2sm877726qwi.32

    Please let me know how to solve this error.

    Thanks

  • Hi Aric,

    This issue has nothing to do with SQL Server Database Mail system. This mostly looks like an issue with the configuration.

    Make sure you have specified the SMTP server name and the port number properly. If your SMTP server requires SSL, make sure Basic Authentication is used and the user name and password is entered properly.

    Most of the issues we see on this error points us to an invalid SMTP Server Name or an invalid port. For example, many websites suggest using port 465 for sending emails through gmail; however, we have seen that port 465 does not work. Port 587 just works fine.

    Please let me know if this is useful.

    Thanks,

    Suhas

  • hi there

    thank you for this article

    it was useful

    by the way i agree with Suhas in which Google defined 2 ports (465 and 587) but only 587 works.

    it took 2 hour to get it !

  • Fine - but if the only mailserver is Exchange how do I go about configering my Exchange to accept SMTP or how do I build a SMTP server

  • Hi Les,

    For configuring Exchange to accept SMTP or to build a SMTP server, please get in touch with your Exchange Administrator. I will not be able to help you here as I do not have knowledge on Exchange.

    Thanks,

    Suhas

  • Thanks Suhas , its been a great help ,Mail setup was successful .

    thanks

    Regards

    Ashish Gupta

  • Hai Friend,here am new to the SQL Server 2005 ...plz help me ...when i right click on the Database mail,it showing only refresh option i din't get any other options like..configure Database Mail....so plzz help me what i have to do now...

  • I don't think I remember seeing anything like this. Can you execute "select @@version" and paste in the output? May be we can find some clues from this.

    Suhas

  • Hi,

    I do the same thing as you mention in snaps for gmail account port is 587 and every thing is exactly same

    But i got this error

    Message

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 8 (2009-12-22T16:47:11). Exception Message: Cannot send mails to mail server. (The operation has timed out.).

    )

    Can any body give me solution

  • The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-01-19T13:54:43). 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.5.1 Authentication Required. Learn more at                              ). )

  • Hi Atif and Kunal,

    Are you inside a corpnet? Are you accessing the internet through a Proxy?

    If you are, then neither will work. You have to be on the Live Internet for this to work.

    Suhas

  • Hi, Is there any option such as My smtp requires authentication as in Outlook because I get an error:

    The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-02-11T18:10:33). Exception Message: Cannot send mails to mail server. (Mailbox unavailable. The server response was: must be authenticated).

  • Hi Suhas,

    I have configured the Database Mail, and the test email is successful. I have to also use this database Mail profile and build the SQL Server Agent job called Automated Email. When the job run, I want the email to be sent to the administrators that backup job was successsful and it should include all the files created in the backup process. So in the job command the code is as the following: The job runs successfully but when I add the following parameter but this parameter is not working @file_attachment = 'C:\Program Files\Microsoft SQL Server\.....error the Access denied.

    What is the right way to work on that, how do I cross this hurdle. I am using sqlserver 2005.

    Use msdb

    GO

    EXEC sp_send_dbmail  @profile_name ='DB Mail Profile',

    @recipients ='csaha@imsa.edu',

    @copy_recipients ='csaha@imsa.edu',

    @blind_copy_recipients='csaha@imsa.edu',

    @body ='This is a test message',

    @subject='Database Backup',

    @body_format='TEXT',

    @importance='Normal',

    @sensitivity='Normal'

    Chitra

  • Chitra,

    If you are using SQL server to send email attachment files in systems other than SQL Server will not be accepted. Use Windows account and provide access to the directory .

    Is C: you are referring to is on database server or on some other system ?

    Thanks

    Srinvas

Page 1 of 4 (59 items) 1234
Leave a Comment
  • Please add 6 and 8 and type the answer here:
  • Post