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

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

EXECUTE sp_configure 'show advanced', 1;

RECONFIGURE;

EXECUTE sp_configure 'Database Mail XPs',1;

RECONFIGURE;

GO

 

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'Primary Account',

    @description = 'Account used by all mail profiles.',

    @email_address = 'myaddress@mydomain.com',

    @replyto_address = 'myaddress@mydomain.com',

    @display_name = 'Database Mail',

    @mailserver_name = 'mail.mydomain.com';

 

-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'Default Public Profile',

    @description = 'Default public profile for all users';

 

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'Default Public Profile',

    @account_name = 'Primary Account',

    @sequence_number = 1;

 

-- Grant access to the profile to all msdb database users

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = 'Default Public Profile',

    @principal_name = 'public',

    @is_default = 1;

GO

 

--send a test email

EXECUTE msdb.dbo.sp_send_dbmail

    @subject = 'Test Database Mail Message',

    @recipients = 'testaddress@mydomain.com',

    @query = 'SELECT @@SERVERNAME';

GO

posted @ Sunday, May 17, 2009 10:25 AM

Print

Comments on this entry:

# re: Database Mail Configuration

Left by Uri Dimant at 5/18/2009 12:14 AM
Gravatar
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?

# re: Database Mail Configuration

Left by Dan Guzman at 5/18/2009 7:04 AM
Gravatar
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).

# re: Database Mail Configuration

Left by Jerry Hung at 5/27/2009 1:46 PM
Gravatar
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

# re: Database Mail Configuration

Left by avijit at 8/18/2009 4:22 AM
Gravatar
what r procedures required for run above mail script....and how to install in sqlserver2000

# re: Database Mail Configuration

Left by Dan Guzman at 8/18/2009 9:45 PM
Gravatar
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

# re: Database Mail Configuration

Left by Magnus Ahlkvist at 10/23/2009 3:29 AM
Gravatar
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;

# re: Database Mail Configuration

Left by Dan Guzman at 10/24/2009 9:04 PM
Gravatar
Thanks for pointing out the extraneous space, Magnus. I fixed the issue.

# re: Database Mail Configuration

Left by Jessie at 1/12/2010 4:52 AM
Gravatar
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.

# re: Database Mail Configuration

Left by Dan Guzman at 1/19/2010 6:36 AM
Gravatar
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.

# re: Database Mail Configuration

Left by DBA at 4/12/2012 1:52 AM
Gravatar
I can not download in this link http://www.sqldev.net/xp/xpsmtp.htm. This link has been closed. Help please...

# re: Database Mail Configuration

Left by Doug Purnell at 4/18/2012 8:28 AM
Gravatar
Great post, saved me hours of work!

# re: Database Mail Configuration

Left by Rx at 8/3/2012 11:04 AM
Gravatar
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

# re: Database Mail Configuration

Left by guzmanda at 8/5/2012 11:25 AM
Gravatar
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.

Comments have been closed on this topic.