Programming4us
         
 
 
SQL Server

SQL Server 2008 : Setting Up Database Mail

6/4/2011 4:20:27 PM
Unlike with SQL Mail, setting up profiles and accounts for use with Database Mail is easy to accomplish, thanks mainly to the Database Mail Configuration Wizard, found in the SQL Server Management Studio (SSMS) Object Browser. You can use this wizard both to set up and manage Database Mail. Before using it, you need to switch on the Database Mail feature, which is off by default, in keeping with Microsoft’s secure-by-default approach. Follow these steps to do so.

Configure the Database Mail XPs configuration option by running the following T-SQL code in a new query window (while logged in as sysadmin, of course):

use Master
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Configuration option 'show advanced options' changed from 0 to 1. Run the
RECONFIGURE statement to install.
Configuration option 'Database Mail XPs' changed from 0 to 1. Run the
RECONFIGURE statement to install.

If you ever want to disable Database Mail, you can run this:

sp_configure 'Database Mail XPs', 0;

This statement prevents Database Mail from starting in response to a call to sysmail_start_sp . If Database Mail is running when you make this call, it sends unsent queued mail until the mail sending process (DatabaseMail.exe) has been idle for the duration of the DatabaseMailExeMinimumLifeTime configuration setting ; then it stops.

You also need to enable Service Broker in msdb (if not done already) because Database Mail relies on it as part of its implementation. To do this, you stop the SQL Server Agent service and then execute the following script:

USE master
GO
ALTER DATABASE msdb SET ENABLE_BROKER

You can check the status of Service Broker on msdb by using the following code:

Use Master
GO
SELECT is_broker_enabled
FROM sys.databases
WHERE name = 'msdb'
GO
is_broker_enabled
-----------------
1
(1 row(s) affected)

To receive message send requests from outside the SQL Server instance, you need to create an endpoint (preferably a certificate-secured one) associated with Service Broker.

To complete this configuration, you need to return to SSMS and establish a connection to the same SQL Server instance for which you just enabled Database Mail. You connect the Object Browser to that instance and expand the Management folder to reveal the Database Mail node. Then you right-click the Database Mail node and select the Configure Database Mail menu option to launch the Database Mail Configuration Wizard.

Creating Mail Profiles and Accounts

After you pass the Database Mail Configuration Wizard’s welcome screen, you are presented with the opportunity to set up Database Mail (“for the first time”). You can achieve this by creating the required profiles, profile security settings, SMTP accounts, and system-wide mail settings. You should leave the first radio button (Set Up Database Mail by Performing the Following Tasks) selected and then click Next.

Note

In Database Mail, you use mail profiles. A mail profile is simply a securable container for a group of SMTP accounts that is used when sending mail. In contrast to SQL Mail, with Database Mail, you can set up multiple profiles containing multiple accounts, allowing for finer-grained administrative control. You can create one profile for admintrators and another for regular users, for example, or create distinct profiles dedicated to various software applications.

Note also that to use Database Mail, you no longer need to run the SQL Server or SQL Server Agent Windows services under user accounts (rather than using the default, LocalSystem), nor do you need to install Microsoft Outlook (or any other Extended MAPI client) on the machine hosting SQL Server 2008.


In the New Database Mail Account screen that appears (see Figure 1), you name (using a valid sysname) and describe your first profile in the provided text boxes, and then you click Add to add your first SMTP account. This process is much like the process of setting up the SMTP (or sending) portion of your email accounts with your regular email client software. To create the SMTP account, you specify a name, an optional description, a user display name, an email address, an optional reply address, a server name, a port, and an authentication mode, which is used to authenticate to the specified SMTP server (as required by your SMTP provider). For many non-Windows SMTP providers, anonymous (no authentication) or basic (simple user name/password) authentication is usually required. If your provider requires Windows Authentication, the credentials under which the SQL Server Windows service runs are supplied to the SMTP server at runtime.

Figure 1. Using the Database Mail Configuration Wizard to set up SMTP accounts.

Instead of using the wizard, you can add a new profile via T-SQL. For example, the following three examples introduce the Database Mail stored procedures sysmail_add_profile_sp, sysmail_add_account_sp, and sysmail_add_profileaccount_sp.

The first script creates the new profile:

EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Default SQL 2008 Profile',
@description = 'Used for general-purpose emailing.'

The second script creates the new SMTP account:

EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'UnleashedMailAcct1',
@description = 'The first SMTP Account.',
@email_address = 'sql2008@samspublishing.com',
@display_name = 'SQL 2008 Mail Account 1',
@mailserver_name = 'smtp.samspublishing.com' ;

The third script associates this new account with the new profile:

EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'Default SQL 2008 Profile',
@account_name = 'UnleashedMailAcct1',
@sequence_number =1;

The great thing you’ll find when adding SMTP accounts is that Database Mail allows you to provide more than one SMTP account for the same profile. You can order the SMTP accounts by priority (using the Move Up/Move Down buttons) so that if a mail send via the top-level (or first) account fails, the second account will be used to retry sending, and so on. This is called SMTP failover priority, and there are two mail settings that control how it works. These settings, found on the Configure System Parameters screen of the wizard, are AccountRetryAttempts and AccountRetryDelay. AccountRetryAttempts specifies how many mail send retries Database Mail will make before failing over to the SMTP account of next-highest priority. AccountRetryDelay specifies (in seconds) how long to wait between mail send retries. These features represent a big improvement in reliability over SQL Mail, which had no such retry capabilities.

After adding the new account to the profile, click Next to set up the profile security settings on the Manage Profile Security screen. Database Mail profiles have two levels of security (with two corresponding tabs on the wizard screen):

  • Public— The profile can be used by all msdb users.

  • Private— The profile can be used only by specific users or members of a specific role. (Note that to send mail, users must have DatabaseMailUserRolemsdb. Use sp_addrolemember to accomplish this.) Specify these users on the Private Profiles tab of the Manage Profile Security screen. membership in

In this case, check the check box under the Public column of the data grid on the Public tab; then click the word No under the Default Profile column. A drop-down list appears, allowing you to make the profile the default (by changing the selection to Yes). The default profile on the server is used when you invoke sp_send_dbmailxp_sendmail) without specifying any profile name for the @profile_name parameter. It’s a good idea to have a default profile set up for general mailing purposes, especially when testing. (the successor to

To set profile security using T-SQL, run the following call to the stored procedure sysmail_add_principalprofile_sp:

exec msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'Default SQL 2008 Profile',
@principal_name = 'public',
@is_default = 1 ;

A third way to configure all the previously mentioned mail objects (in the form of a T-SQL script) is to use an SMSS Database Mail query template. To do this, you open the Template Explorer via the View menu (or by pressing Ctrl+Alt+T), and then you expand to the Database Mail folder and double-click Simple Mail Database Configuration. Then you connect to your SQL Server instance and, from the Query menu, select the Specify Values for Template Parameters option (or press Ctrl+Shift+M) to fill in the desired parameter values, which correspond to the parameters of the stored procedures mentioned previously.

Using T-SQL to Update and Delete Mail Objects

To delete or update profiles, accounts, profile-account associations, and profile security settings (note: do so in reverse order), you use the stored procedures shown in Table 1.

Table 1. T-SQL Stored Procedures
Stored Procedure NamePurpose
sysmail_delete_profile_spDelete a profile
sysmail_delete_account_spDelete an account
sysmail_delete_principalprofile_spDelete the association between a profile and a user or role (revokes permission for the principal on use of the profile)
sysmail_delete_profileaccount_spDelete the association between a profile and an account
sysmail_update_profile_spUpdate a profile
sysmail_update_account_spUpdate an account
sysmail_update_principalprofile_spUpdate the association between a profile and a user or role
sysmail_update_profileaccount_spUpdate the association between a profile and an account

For example, to delete a profile, you execute this:

exec msdb.dbo.sysmail_delete_profile_sp @profile_name='Undesireable Profile Name'


To update a profile’s security, changing it from the default to the nondefault profile, you execute the following:
exec msdb.dbo.sysmail_update_principalprofile_sp
@profile_name = 'Default SQL 2008 Profile',
@principal_name = 'public',
@is_default = 0;

Alternatively, you can simply return to the wizard and select one of the Manage options to alter or drop any of the settings or objects. (Of course, under the covers, the wizard probably uses all these stored procedures.)

Setting System-wide Mail Settings

You use the Configure System Parameters screen in the Database Mail Configuration Wizard to configure the system-wide Database Mail settings. (Click Next on the Select Configuration Task screen to reach this screen, if you haven’t already.) You’ve seen the first two settings that appear in the grid (AccountRetryAttempts and AccountRetryDelay) in an earlier section (“Creating Mail Profiles and Accounts”) as they relate to SMTP failover priority. These are the other four:

  • Maximum File Size (Bytes)— This setting specifies the maximum size of any one email attachment.

  • Prohibited Attachment File Extensions— This setting specifies which potentially dangerous or undesirable attachment types to ban from exchanged emails.

  • Database Mail Executable Minimum Lifetime (seconds)— This setting specifies how long (minimally) the database mail process (that is, DatabaseMail.exe, which is activated by Service Broker) should run idly before closing after it finishes emptying the mail send queue.

  • Logging Level— This setting specifies the quality of email auditing to use, and it can be set to Normal (errors only), Extended (errors, warnings, and informational messages; this is the default), or Verbose (the same as Extended, plus success messages and other messages that are useful when you debug problems with DatabaseMail.exe). To view Database Mail’s primary log, right-click the Database Mail folder in the Object Browser and then click the View Database Mail Log menu option. Examine and maintain the log by using the Log File Viewer that is launched. You can also use the built-in stored procedure sysmail_delete_log_sp to clear the log, or query the msdb sysmail_event_log view to see its contents in tabular format.

To change any of these configuration settings via T-SQL script, use the sysmail_configure_sp stored procedure. sysmail_configure_sp takes two parameters: the name of the setting (minus any spaces) and its new value. The following example uses the sysmail_configure_sp procedure to change AccountRetryDelay to two minutes:

exec msdb.dbo.sysmail_configure_sp 'AccountRetryDelay', 1200

Testing Your Setup

The final step in setting up Database Mail is to ask SQL Server to send a test email. To do this, right-click the Database Mail folder in the Object Browser and then click the Send Test E-mail menu option.

If the test fails, click Troubleshoot, and SMSS opens the “Troubleshooting Database Mail” Books Online topic, which provides a solid set of troubleshooting steps to get you started.

If the mail is sent by SQL Server and successfully received in your client software’s inbox, you can proceed to the next section to learn how to use the sp_send_dbmail stored procedure to send email from T-SQL.

Other -----------------
- SQL Server 2008 : Security and Compliance - Setting Up Auditing via T-SQL & SQL Injection Is Easy to Do
- SQL Server 2008 : Security and Compliance - SQL Server Auditing
- SQL Server 2008 : Security and Compliance
- SQL Server 2008 : Transparent Data Encryption
- SQL Server 2008 : Data Encryption - Column-Level Encryption
- SQL Server 2008 : Data Encryption - SQL Server Key Management
- SQL Server 2008 : Data Encryption
- SQL Server 2008 : Client Data Access Technologies
- SQL Server 2008 : Client Configuration
- SQL Server 2008 R2 : Client Installation
- SQL Server 2008 R2 : Client and Server Networking Considerations
- Upgrading to SQL Server 2008 : Upgrading Other SQL Server Components
- Upgrading to SQL Server 2008 : Slipstreaming Upgrades
- Upgrading to SQL Server 2008 : Upgrading Using a Configuration File
- Destination: SQL Server 2008 or SQL Server 2008 R2 (part 2) - Upgrading In-Place
- Destination: SQL Server 2008 or SQL Server 2008 R2 (part 1) - Side-by-Side Migration
- Upgrading to SQL Server 2008 : Using the SQL Server Upgrade Advisor (UA)
- SQL Server 2008 : Developing Custom Managed Database Objects (part 7) - Using Transactions & Using the Related System Catalogs
- SQL Server 2008 : Developing Custom Managed Database Objects (part 6) - Developing Managed Triggers
- SQL Server 2008 : Developing Custom Managed Database Objects (part 5) - Developing Managed User-Defined Aggregates
 
 
Most View
- Windows Server 2008 : Configuring IIS Security (part 5) - Connecting to a Remote Server Using IIS Manager
- Microsoft Dynamics GP 2010 : Dynamics GP Utilities (part 2) - Loading sample company data & Creating a new Dynamics GP company
- An OLAP Requirements Example: CompSales International (part 14) - Data Mining
- iPad : Navigating with Maps
- Overview of Internet Explorer 8 (part 1) - Defining IE8 Accelerators
- Windows Phone 7 : Turning On Airplane Mode
- SharePoint 2010 : Organizing Information - An Information Organization Project
- BizTalk Server 2009 : Using queues within asynchronous scenarios (part 1)
- Exchange Server 2010 Maintenance, Monitoring, and Queuing : Understanding Troubleshooting Basics
- SQL Azure : Design Factors (part 2)
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS