At iTech Northwest, we are considering moving our email services to Google Applications for Domains. One of the items on my to do list was to see if I could send email from Microsoft SQL Server 2005’s Database Mail feature. As it turns out, it’s very simple to setup and configure.
Open Microsoft SQL Server Management Studio. Navigate to Management and expand it.
Right click on Database Mail and select Configure Database Mail.
When the Wizard screen loads, click Next.
Select the first option and click Next.
Enter in a Profile Name and click Add…
Click New Account…
Enter in your Gmail account information. Under the Outgoing Mail Server (SMTP) section, make sure you enter 465 or 587 for Gmail’s SMTP Port number. While doing research on sending authenticated email via Gmail, I heard that some people had issues with port 465, so in my example I’m using 587. Select Basic Authentication and enter in your Gmail account information. Click OK when you are done.
After you click OK, you should see the above screen. Click Next >. Keep clicking Next until you get to the following screen.
If you setup everything properly, you should get 4 success messages. Click Close.
We are ready to send a test email.
Right click Database Mail and select Send Test E-Mail…
Select your Database Mail Profile from the drop down list box. Enter in a To: address, Subject, and Body. Click Send Test E-Mail when you are ready.
If everything worked, you should get a message saying your email has been queued for processing. In a few minutes or seconds, your message should arrive.
With Database Mail configured, you can start sending email via stored procedures. In the next couple of weeks, I’ll build a EmailQueue table, where all I have to do is insert data into my table and triggers will fire to send an email based on the information in the newly inserted column. This will enable me to keep a copy of the messages being sent, but also to allow all of my applications to use the same back end to generate emails.