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 |
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. |
|
DBA
2012-04-12 |
re: Database Mail Configuration I can not download in this link http://www.sqldev.net/xp/xpsmtp.htm. This link has been closed. Help please... |
|
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. |