This week I had to setup a sandbox environment to test sending some HTML reports (created by a SQL Server stored procedure, more on this next week) I’d prepared for a client, and wanted delivered to my mailbox each morning. For the purpose of this demo I used my Gmail account.
I created my sandbox environment using Microsoft Azure; this is my go-to for any testing as it takes less than half an hour to spin up a new SQL Server virtual machine.
I choose SQL Server 2016 SP1 on Windows 2016 on a DS1_V2 Standard virtual machine (this is only visible by de-selecting ‘Recommended’), primarily as it’s the cheapest allowing me to run as many virtual machines as possible without exhausting my Azure credits and disabling my account. A tip is to setup a Runbook within Azure Automations to shut down your virtual machines at midnight just in case you forget.
The only 2 changes I made to the defaults were to change the IP Address from Dynamic to Static, and within the SQL Server settings Virtual Network change SQL connectivity from ‘Private ’ to ‘Public (Internet)’.
Here is my script template for setting up Database Mail on a client’s site. As I’m using my Gmail account there are a couple of additional steps which I’ll cover in a minute. If you want to follow along also using your Gmail account, all you need to change for now is the @email_address parameter.
use [master]; GO /* Create database mail account */ exec msdb.dbo.sysmail_add_account_sp @account_name = 'My Gmail account' , @email_address = 'firstname.lastname@example.org' , @mailserver_name = 'smtp.gmail.com' , @port = 587 , @enable_ssl = 1; GO /* Create database mail profile */ exec msdb.dbo.sysmail_add_profile_sp @profile_name = 'Database Mail profile'; GO /* Associate Database Mail profile with the account My Gmail account */ exec msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Database Mail profile' , @account_name = 'My Gmail account' , @sequence_number = 1; GO /* Enable Database Mail */ exec sp_configure 'show advanced options',1 reconfigure GO exec sp_configure 'Database Mail XPs',1 reconfigure GO
As the Gmail SMTP server is external to our network there are some security credentials to enter under Basic authentication as shown in the screenshot below. I discovered that just entering your Gmail account password is not enough, even with the correct password you get the following error in the Database Mail log.
This is because I have 2-Step Verification enabled for my Gmail account. To to get around this you need to setup an application password. To generate this, click your name or photo near your Gmail inbox’s top right corner and then My Account. Within Sign-in & security, scroll down to Password & Sign-in method (you may need to login before seeing this option) and click on App passwords.
Click on Select app and select Other (Customised name). I used “Azure Test Database Mail account” as the name, then click Generate. A password will be generated and will be displayed in a pop up screen similar to below.
Copy the code in yellow (picture above has been edited to protect my password) and paste into the password field in the Database Mail GUI shown above.
Now we are ready to test it. Right-click on the Database Mail in the SQL Server object explorer under Management, then select Send Test E-Mail.
Enter your target email address in the To: field and hit Send Test E-Mail, and you should receive this email message (if you called your SQL Server SQLSERVER2016).
“This is a test e-mail sent from Database Mail on SQLSERVER2016.”