Dan Guzman Blog

Database Mail Configuration

I recently had to setup Database Mail on dozens of SQL Server instances.   Rather than perform this tedious task using the SSMS GUI, I developed a script that saved me a lot of time which I'm sharing here.   

My needs were simple so I only needed a single SMTP account and profile.  I decided to make the profile the default public one so that all msdb users would use this profile unless a different sp_send_dbmail @profile value was explicitly specified.  You might want to extend this script if you need other accounts/profiles, such as separate ones for administrative alerts or user reports.

Setup Script/o:p

Below is the template script I used for my task.  The sysmail_add_account_sp @username and @password parameters might be required depending on your SMTP server authentication and you will of course need to customize the mail server name and addresses for your environment.

– Enable Database Mail for this instance/o:p

EXECUTE sp_configure 'show advanced', 1;/o:p

RECONFIGURE;/o:p

EXECUTE sp_configure 'Database Mail XPs',1;/o:p

RECONFIGURE;/o:p

GO/o:p

 /o:p

– Create a Database Mail account/o:p

EXECUTE msdb.dbo.sysmail_add_account_sp/o:p

    @account_name = 'Primary Account',/o:p

    @description = 'Account used by all mail profiles.',/o:p

    @email_address = 'myaddress@mydomain.com',/o:p

    @replyto_address = 'myaddress@mydomain.com',/o:p

    @display_name = 'Database Mail',/o:p

    @mailserver_name = 'mail.mydomain.com';/o:p

 /o:p

– Create a Database Mail profile/o:p

EXECUTE msdb.dbo.sysmail_add_profile_sp/o:p

    @profile_name = 'Default Public Profile',/o:p

    @description = 'Default public profile for all users';/o:p

 /o:p

– Add the account to the profile/o:p

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp/o:p

    @profile_name = 'Default Public Profile',/o:p

    @account_name = 'Primary Account',/o:p

    @sequence_number = 1;/o:p

 /o:p

– Grant access to the profile to all msdb database users/o:p

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp/o:p

    @profile_name = 'Default Public Profile',/o:p

    @principal_name = 'public',/o:p

    @is_default = 1;/o:p

GO/o:p

 /o:p

–send a test email/o:p

EXECUTE msdb.dbo.sp_send_dbmail/o:p

    @subject = 'Test Database Mail Message',/o:p

    @recipients = 'testaddress@mydomain.com',/o:p

    @query = 'SELECT @@SERVERNAME';/o:p

GO/o:p

Legacy Comments


Uri Dimant
2009-05-18
re: Database Mail Configuration
Hi Dan
Thank you for sharing that. I just wonder, did you run it on SS2008 or SS2005? Should have it worked on multi server feature in SS2008?

Dan Guzman
2009-05-18
re: Database Mail Configuration
Hi, Uri.

I ran the script against a mix of SQL Server 2005 and 2008 instances. I used the SQL 2008 SSMS multi-instance feature to run it (right-clicked on the SSMS resgistration group selected new query).

Jerry Hung
2009-05-27
re: Database Mail Configuration
My comment to add will be to enable SQL Agent to use the Database Mail profile too if needed

-- Enable SQL Server Agent to use Database Mail profile (in Alert System tab)
-- restart SQL Agent after
USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile',
N'REG_SZ', N'DBA_Notifications'
GO


avijit
2009-08-18
re: Database Mail Configuration
what r procedures required for run above mail script....and how to install in sqlserver2000

Dan Guzman
2009-08-18
re: Database Mail Configuration
The Database mail setup script can be run from any SQL Server Management Studio window. Just customize as instructed and execute.

The Database Mail feature was introduced in SQL Server 2005. For SQL 2000 SMTP mail, I recommend xpsmtp.dll, a free download from http://www.sqldev.net/xp/xpsmtp.htm

Magnus Ahlkvist
2009-10-23
re: Database Mail Configuration
Hi,

I found an error in the script - there's a Space in front of the account name in the sysmail_add_profileaccount_sp call

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Default Public Profile',
@account_name = ' Primary Account',
@sequence_number = 1;


Dan Guzman
2009-10-24
re: Database Mail Configuration
Thanks for pointing out the extraneous space, Magnus. I fixed the issue.

Jessie
2010-01-12
re: Database Mail Configuration
I am a new bird for this part. I have try this on Win server 2008 and with SQL server 2008. While i failed to send a test mail. And below is the error message, can you please help me out about this?
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2010-01-12T14:10:53). Exception Message: Could not connect to mail server. (No such host is known).
By the way, the SMTP service has been started and the port - 25 has been opened.

Dan Guzman
2010-01-19
re: Database Mail Configuration
Hi, Jessie.

I suggest you troubleshoot with a basic network connectivity test. Using the @mailserver_name specified in sysmail_add_account_sp, try the follwing from the command prompt on the SQL Server box:

PING mail.mydomain.com

The above command will test name resolution and basic TCP/IP connectivity. If that is successfull, test the SMTP port connection:

TELNET mail.mydomain.com 25

The mail server should then return a 220 message number. The "quit" to close the connection.

Doug Purnell
2012-04-18
re: Database Mail Configuration
Great post, saved me hours of work!

Rx
2012-08-03
re: Database Mail Configuration
Running on a virtual server, company has Groupwise (I can't get to that at the corporate site). Does SQL have its own SMTP or does it rely on another mail server?
Thanks

guzmanda
2012-08-05
re: Database Mail Configuration
Database mail includes an SMTP client but not a SMTP server. The SMTP client simply sends the message to the specified SMTP server, which is responsible for routing the message to the final recipient(s).

I believe Groupwise has SMTP server capibilities. However, Groupwise SMTP will need to be configured and SMTP allowed through firewalls. If you cannot access Groupwise directly via SMTP, you'll need some other SMTP server or gateway to route the email.