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…”:
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:
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
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”:
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:
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:
[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.
Thank you so much… After a long struggle, resolved the Db_mail issue. This post really helpful.
Big thanks again.
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?
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
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.
Thanks for providing a solution 🙂
Thanks lot for your wonderful help
Thanks a lot. My problem was solved
1st class article – hjelped me get it working
Thank you!
1st class article – helped me get it working.
Thank you!
Thank you.