Database Mail in SQL Server 2012

About Database Mail

It’s an enterprise solution for sending e-mail messages. It can contain query results and can also include files from any resource on your network using Database Mail.

Database Mail was introduced as a new feature in SQL Server 2005 and replaces the SQL Mail feature found in previous versions. Database Mail uses the standard Simple Mail Transfer Protocol (SMTP) to send mail.

Note: Database Mail is not active by default. You must enable Database Mail using either the Database Mail Configuration Wizard, the sp_configure stored Procedure, or using the Surface Area Configuration facet of Policy-Based Management.

Configure Database Mail using the Database Mail Configuration Wizard

Before using Database Mail, first verify that the SQL Server Agent is running and verify that the Service Broker is enabled for the MSDB database.

Verifying SQL Server Agent is running or not using the following query.

----------------------------------------------------------------------

USE [MASTER]

Go

EXEC xp_servicecontrol N'QueryState', N'SQLServerAGENT';

Go

 

--Output

 

Current Service State

-----------------------

Running.

----------------------------------------------------------------------

Note: If the Agent is not running then the sent messages will be collected in the MSDB database and sent when the Agent service is started again.

Verify that the Service Broker is enabled using the following query.

---------------------------------------------------------------------

USE [MASTER]

Go

SELECT is_broker_enabled

FROM sys.databases

WHERE database_id = DB_ID(N'msdb');

Go

 

--Output

 

is_broker_enabled

-----------------

1

----------------------------------------------------------------------

Column is_broker_enabled will be:

1: Enable
0: disable

Note: Service Broker message is disabling then Database Mail queues messages in the database but cannot deliver the messages.

If the Service Broker is disabled (is_broker_enabled = 0), run the following T-SQL to enable the Service Broker for the MSDB database.

----------------------------------------------------------------------

USE [MASTER]

Go

ALTER DATABASE [msdb] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Go

----------------------------------------------------------------------

Point: New installations of SQL Server number of internal stored procedures are disabled

Enable Database Mail

Now enable Database Mail using the sp_CONFIGURE stored procedure, run the following.

----------------------------------------------------------------------

USE [MASTER]

Go

EXEC sp_configure 'show advanced options', 1

Go

Configuration option "show advanced options"were  changed from 0 to 1. Run the RECONFIGURE statement to install.

RECONFIGURE;

GO

Command(s) completed successfully.

EXEC sp_configure 'Database Mail XPs', 1;

GO

Configuration option "Database Mail XPs" was changed from 0 to 1. Run the RECONFIGURE statement to install.

RECONFIGURE

GO

Command(s) completed successfully.

EXEC sp_configure 'show advanced options', 0;

GO

Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

RECONFIGURE

GO

 

Command(s) completed successfully.

----------------------------------------------------------------------

Surface Area Configuration facet of Policy-Based Management

Now enable Database Mail using the Surface Area Configuration Facet of Policy-Based Management, launch SQL Server Management Studio, right-click on the server and select Facets.



On the Facets dialog box, select Surface Area Configuration from the Facet drop down list.



Now find DatabaseMailEnabled in the Facet properties section and change its value from False to True and then click the Ok button.

Database Mail Configuration Wizard

Now select Database Mail Configuration Wizard, in Object Explorer, expand the Management folder in SQL Server Management Studio, right-click Database Mail, and select Configure Database Mail.

Launch Database Mail Configuration Wizard

 

This will take up the Database Mail Configuration Wizard welcome screen. Click Next to continue.

 

Now the Select Configuration Task screen chose Set up Database Mail by performing the following tasks: Click Next to continue.

 

If you haven't previously enabled Database Mail then you will obtain a message box asking if you wish to enable the Database Mail feature. Click yes to enable it.

 

Next, on the New Profile screen, provide an e-mail Profile Name and an optional description of the profile and then select the Add button to configure an SMTP Account.

 

Now in the New Database Mail Account screen, provide the required information for the outgoing mail server (SMTP). Fill in the Account Name, Description (optional), Email Address, Display name, Reply Email (optional), and SMTP Server Name and Port number. Then select the appropriate SMTP authentication for your server and click next to continue.

 

The following details are:

• Account Name: The desired account name for this SMTP account.
• Description: Description of the account
• E-mail address: Sender email address.
• Display Name: Name associated with the email address
• Reply e-mail: Where the reply to the emails will be forwarded to.
• Server name: Provide the SMTP to be used to send emails from the SQL Server.
• Port Number: Port number to be used by this account. Default is 25.

 

The server requires a secure connection (SSL): check this box.

SMTP Authentication

• User Name: Enter your Hotmail or Live account
• Password: Enter password of your Hotmail or Live account.
• Confirm Password: Enter password of your Hotmail or Live account.

 

It will return you to the New Profile screen. Click Next to continue.

 

The Manage Profile Security screen allows you to set this profile to either public or private. A private profile is accessible only to specific users or roles. A public profile allows any user or role with access to the mail host database (Microsoft database) to send e-mail using this profile.

 

 

This profile You can also set as a default profile. In this case, users or roles can send e-mail using the profile without explicitly specifying the profile name.

The Configure System Parameters

• Account Retry Attempts: The number of times that the external mail process attempts to send the e-mail message using each account in the specified profile.
• Account Retry Delay (seconds): The amount of time, in seconds, for the external mail process to wait after it tries to deliver a message using all accounts in the profile before it attempts all accounts again.
• Maximum File Size (Bytes): The maximum size of an attachment, in bytes.
• Prohibited Attachment File Extensions: A comma-separated list of extensions which cannot be sent as an attachment to an e-mail message. Click the browse button to add additional extensions.
• Database Mail Executable Minimum Lifetime (seconds): The minimum amount of time, in seconds, that the external mail process remains active. The process remains active as long as there are e-mails in the Database Mail queue. This parameter specifies the time the process remains active if there are no messages to process.
• Logging level: which messages are recorded in the Database Mail log. Possible values are:
• Normal: logs only errors
• Extended: logs errors, warnings, and informational messages
• Verbose: logs errors, warnings, informational messages, success messages, and additional internal messages. Use verbose logging for troubleshooting.

Click Next to continue.

Finally, click Finish to execute these actions.

Now all actions has been completed successfully. Click Close to exit the Database Mail Configuration Wizard.

 

Make a test of the new e-mail Profile within SQL Server Management Studio. Right-click Database Mail and select Send Test E-Mail.

 

Enter a recipient email address and click Send Test E-Mail.

 

Click Ok

Configure Database Mail: using Transact-SQL

First you will create Database Mail accounts for the profile using the msdb.dbo.sysmail_add_account_sp Stored Procedure and then you will create an account profile using the msdb.dbo.sysmail_add_profile_sp Stored Procedure then execute this Stored Procedure msdb.dbo.sysmail_add_profileaccount_sp to add the account to the profile.

For Example

--Step 1

-- Create a Database Mail account

 

EXEC msdb.dbo.sysmail_add_account_sp

@account_name = 'DBA Test Account'

, @description = 'DBA SMTP account for Testing'

, @email_address = '[email protected]'

, @display_name = 'DBA Mail'

, @replyto_address = '[email protected]'

, @mailserver_type = 'SMTP'

, @mailserver_name = 'localhost'

, @port = 25

, @use_default_credentials = 0

, @username = 'DBAUser'

, @password = 'xxxxxxxx'

Go

--step 2

-- Create a Database Mail profile

EXEC msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DBA SMPT Profile'

, @description = 'DBA SMPT PROFILE FOR testing'

Go

--step 3

-- Add the account to the profile

EXEC msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DBA SMPT Profile'

, @account_name = 'DBA Test Account'

, @sequence_number = 1

Go

Now you can send database mail using the msdb.dbo.sp_send_dbmail stored procedure.

For Example

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBA SMPT Profile'

, @recipients = '[email protected]'

, @subject = 'Automated Test Results (Successful)'

, @body = 'finished successfully.'

Go

Security in Database Mail

Note:To send permissions for sp_send_dbmail default to all members of the DatabaseMailUserRole database role in the MSDB database.

A database user or role must be granted permission to use a Database Mail profile if that profile is private. Since Database Mail enforces security for mail profiles, you must explicitly grant permissions to each MSDB database user or role that should have access to a private Database Mail profile. You can grant access to either specific users or to all users in MSDB. A private profile restricts access to a specified list of users or roles of the mail-host database (MSDB). A public profile, on the other hand, allows any user or role with access to the mail host database (MSDB) to send e-mail using that profile. The Database Mail profile used in this guide is a private profile and will therefore require permissions to be granted to a user or role before it can be used to send e-mail.