THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Setting up Database Mail to use Gmail account for Presentations

This past weekend’s SQL Saturday #31 in Tampa, FL I had the need to send email from one of my Virtual Machines while doing my presentation on Event Notifications in SQL Server 2005/2008.  Generally speaking, I don’t need to send email from the virtual machines on my laptop.  However, in this case the point of the presentation was to show how to use Event Notifications to get near-real time notification of events in SQL Server.   Gmail provides standard SMTP access for sending email using an authenticated gmail account and its really easy to configure Database Mail to send using the Gmail SMTP Server.  The required settings are readily available in the Gmail help system.  However, to make things easier for those who might try this at a later point in time, the Database Mail Account screen below has the pertinent information filled out:

 image

To handle this programmatically and prevent having to step through the GUI the following TSQL can be used:

EXEC sys.sp_configure N'Database Mail XPs', N'1'
GO
RECONFIGURE
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp 
  
@profile_name=N'Gmail Notification Account'
  
@description=N'Email Notifications from SQL Server using Gmail Account'
GO
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp 
  
@principal_name=N'guest'
  
@profile_name=N'Gmail Notification Account'
  
@is_default=1
GO
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp 
  
@profile_name=N'Gmail Notification Account'
  
@account_name=N'Gmail Notifications'
GO
EXECUTE msdb.dbo.sysmail_add_account_sp   
  
@account_name='Gmail Notifications',
  
@email_address='fakeaddress@gmail.com',
  
@display_name='SQL Server Gmail Notifications'
  
@replyto_address='fakeaddress@gmail.com'
  
@description='Email Address for sending Notifications using Gmail'
  
@mailserver_name='smtp.gmail.com',
  
@mailserver_type='SMTP',
  
@port=587,
  
@username='fakeaddress@gmail.com',
  
@password'fakepassword',
  
@use_default_credentials=0,
  
@enable_ssl=

Make sure that you change the address and password information as these are just for the sake of this example.  For those interested, the same can also be done with Windows Live Mail accounts using the setting information contained in their help.

Published Monday, January 25, 2010 8:44 AM by Jonathan Kehayias

Comments

 

Eric Wisdahl said:

Good stuff Jon!  I've used the gmail as my email account a few times at presentations.  Of course, for SSIS it is a little bit more fun since there isn't a way to pass authentication information that ISN'T windows through the standard send mail task.  Hadn't really thought of setting up my default database mail account yet though.

January 25, 2010 9:11 AM
 

Cameron Mergel said:

Sweet!  Thanks for sharing that.

January 26, 2010 8:30 AM
 

Jason Strate said:

I had noticed you had done this at SQL Saturday and actually went back to the hotel to set this up.  Very useful.

January 26, 2010 11:32 AM
 

TiborKaraszi said:

FWIW, you use the same setup for hotmail, with smtp server as only differnce (smtp.live.com). You might need a pay account, though. I use hotmail for my Db Mail demos...

February 2, 2010 1:10 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement