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:
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=1
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.