You are currently viewing Send database mail from SQL Server with Gmail

Send database mail from SQL Server with Gmail

Did you know you can use Gmail to send emails from SQL Server?

In Management Studio, open the folder “Management” and right click on “Database Mail” and select “Configure Database Mail”. In the wizard that opens, select the first option, “Set up Database Mail…”:

Configure Database Mail Set up Database Mail
Configure Database Mail Set up Database Mail

Provide a name for the profile, in this demo I’m using “TomasGmailProfile”. Also enter a description if you wish. Press the “Add” button to add an SMTP account:

Configure Database Mail Add SMTP Account
Configure Database Mail Add SMTP Account

On the dialog that opens, select “New Account”. Enter a name for the account, I’m using “TomasGmailAccount”. Add a description if you want. Use the following settings:

E-mail address: yourmail@gmail.com
Display name: Your Name
Reply email: yourmail@gmail.com
Server name: smtp.gmail.com
Port number: 587
SSL: Checked
Basic authentication: Checked
User name: yourmail@gmail.com
Password: your password
Confirm password: your password

Configure Database Mail Add SMTP Account Settings
Configure Database Mail Add SMTP Account Settings

Click “OK”, and “Next”. In the next dialog, check the “Public” checkbox on the new account, and select “Yes” in the “Default Profile” column if you wish and press “Next”. Normally you can leave the settings on the next page as they are, but you may want to increase the value for “Maximum File Size (Bytes)”. The default is 10000000 Bytes which is 10 Megabyte. If you are trying to send mails with attachments larger than this value, you’ll get the 22051 error:

[red_box]Msg 22051, Level 16, State 1, Line 0 File attachment or query results size exceeds allowable value of 1000000 bytes.[/red_box]

To change this value at a later time, start the wizard from the beginning and select “View or change system parameters”:

Configure Database Mail View Or Change System Parameters
Configure Database Mail View Or Change System Parameters

The next step is to configure Gmail to allow access. Login to your Gmail account and select the “Security” page. Make sure the “Access for less secure apps” is Enabled:

Gmail Security Access For Less Secure Apps Enabled
Gmail Security Access For Less Secure Apps Enabled

That’s it! This should be all to start sending emails from within SQL Server. To send mail with T-SQL, use the sp_send_dbmail system stored procedure.

If you’re still having trouble sending emails, start by checking the “Database Mail Log” by right clicking “Database Mail” in Management Studio. This shows a log with the latest database mail event. Just note that there may be a delay before any errors show up in the log. If you’re getting the following error:

Database Mail Error
Database Mail Error

[red_box]The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2014-10-01T18:16:31). 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).)[/red_box]

…you may need to visit the log in Gmail to see the reason for the failure. The log can be seen by selecting “View all events” on the “Security” page mentioned above. There you may “recognize all the unusual activity below as yours”, which may help. However, make sure any errors really are SQL Server trying to get access.

Tomas Lind

Tomas Lind - Consulting services as SQL Server DBA and Database Developer at High Coast Database Solutions AB.

This Post Has 10 Comments

  1. Sanjeev

    Thank you so much… After a long struggle, resolved the Db_mail issue. This post really helpful.
    Big thanks again.

  2. Shailendra

    Hi this was really helpful , thank you but my problem is my email address has two step verification turned on and it fails to send email but if I turn off two step verification, it works fine. Do you have any idea on this?

    1. Tomas Lind

      Hi, I’m not that familiar with Googles 2-Step Verification but since manual steps are required I think it is not possible.

      Regards
      Tomas

      1. Shailendra

        Thanks Tomas Lind for your response but I found the solution. I just need to create app specific password from gmail account and use that password instead of regular password.

  3. Senthil

    Thanks lot for your wonderful help

  4. Lokendra Chand

    Thanks a lot. My problem was solved

  5. Dave Boltman

    1st class article – hjelped me get it working

    Thank you!

  6. Dave Boltman

    1st class article – helped me get it working.

    Thank you!

  7. martonthenagy

    Thank you.

Leave a Reply