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: firstname.lastname@example.org
Display name: Your Name
Reply email: email@example.com
Server name: smtp.gmail.com
Port number: 587
Basic authentication: Checked
User name: firstname.lastname@example.org
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:
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:
…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.