Sending email from Gmail with MS SQL Server 2005

by Otto on November 6, 2007

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.

1-ConfigureDatabaseMail

Right click on Database Mail and select Configure Database Mail.

2-WelcomeWizard

When the Wizard screen loads, click Next.

3-Setup

Select the first option and click Next.

4-AccountSetup

Enter in a Profile Name and click Add…

5-CreateAccount

Click New Account…

6-AccountInfo

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.

7-AccountSetup2

After you click OK, you should see the above screen. Click Next >. Keep clicking Next until you get to the following screen.

8-Finish

If you setup everything properly, you should get 4 success messages. Click Close.

We are ready to send a test email.

9-Test1

Right click Database Mail and select Send Test E-Mail…

10-Test2

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.

11-Test3

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.

{ 8 comments }

goutam June 16, 2008 at 8:59 am

Thankes ,Please Send If u have more information about this topic.

Federico June 16, 2008 at 8:59 am

I get error 5.5.1 Authentication RequiredI’ve checked SSL with user and password, but it doesn’t work.

Ian April 8, 2010 at 8:56 am

I have used port 465 and 587… 587 is for using TLS encryption when connecting to gmail’s SMTP server. As far as I know SQL sever 2005 and 2008 do not include or allow TLS encryption when connecting to SMTP server. Port 465 worked perfectly for me in Outlook 2003 for sending emails but not via database mail using SQL server 2008. Event log in database mail shows “The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-04-08T09:41:14). Exception Message: Cannot send mails to mail server. (The operation has timed out.). )”. Anyone have any insight on this?

Anuraj July 1, 2010 at 10:57 pm

Thanks, Very use full post with clear idea..It working Fine.

Pehlebhiayatha August 29, 2010 at 12:19 pm

Thanks for sharing this….Great step by step instructions.

Irina September 13, 2010 at 10:05 am

To Ian:
Have you found resolution for a timeout problem?
I am trying to set up 2008 Database mail with Gmail account and experiencing the same .

pimsainnum June 8, 2011 at 8:18 pm

I had error:
Message
The mail could not be send tho the recipients because of the mail server failure. (Sending Mail using Account 2 xxxx), Exception Message: Could not connect to mail server. *A connection attempt fail because the connected party did not property respond after a period of time, or established connection failed because connection host has failed to respond 74.125.235.149:587Z)l

Bhaskar November 11, 2011 at 7:24 am

Hi,

I need to send the attachment which is generated by the scheduler. Is there any method to send?

Comments on this entry are closed.

Previous post:

Next post: