A Basic Guide To Configuring Database Mail In SQL Server
Database Mail is a feature within SQL Server that allows the database engine to generate and send email messages. Database Mail utilizes the Simple Mail Transfer protocol (SMTP) which allows any compatible SMTP server to receive an email generated by the Database Mail system. Configuring Database Mail is a relatively straight forward process. The steps outlined below will walk you through the process of creating a new Database Mail account and configuring the necessary parameters from within Management Studio.
The first step when configuring Database Mail for the first time is to connect to the desired server within SQL Management Studio and expand the Management node.
Locate the option for Database Mail, right-click it and select Configure Database Mail.
The Database Mail Configuration Wizard will be displayed. By default, the option to Set up Database Mail by performing the following tasks radio button will be selected. If this is the first time you are configuring Database Mail on the server then leave this option selected and click Next.
If you are modifying an existing Database Mail account or profile select the applicable option and then click Next. The steps below are tailored towards creating a new profile and account but if you are modifying an account the steps are basically the same.
The next step is to create a Profile. SQL Server allows for the creation of multiple mail profiles. For now, we’ll just be creating a basic profile to use in order to create the necessary SMTP account.
In this example I am naming the profile Database Mail Test.
Once the profile name has been specified you can give it a description, though this is not a required field.
Once the profile name has been specified click the Add… button to add an SMTP account.
The following screenshot illustrates configuring a new SMTP account in Database Mail. In addition to the Account Name and Description I have also provided the necessary Outgoing Mail Server parameters. The E-mail address specifies the account from which the email will be sent while the Reply e-mail field allows you to specify an alternate email address for any Database Mail messages that are received when the user attempts to replay to the message.
The Server name is the most important piece here. If you are not sure what the correct email server name should be you may need to check with your IT team to confirm. In most, but not all cases its probably something like mail.domain.com
You can usually leave the default Anonymous authentication selected but depending on your security policy you may want to go with Windows Authentication or use the Basic SQL Authentication option.
Once the new account has been configured click OK.
The following widow allows you to Manage the Profile Security of the Database Mail profile. Check the box next to the newly created profile to make it public.
Public profiles can be accessed by all users of any mail-hosted database while private profiles can only be accessed by specified users. In most cases you probably want to make the profile public, especially if its the only Database Mail profile on the server. For specific applications or processes, you may want to configure a private profile.
You also have the option to make any given profile the default public profile. Though this is not necessary, if you only have a single profile on your server, it’s not a bad idea to make it the default profile.
Once you have selected the desired profile security options click Next >.
The final step in setting up the Database Mail account is to Configure the System Parameters. The screenshot below shows the default setting for all new accounts.
Make any changes you require to these settings and then click Next >. In some instances you may want to increase the Account Retry Attempts as well as prohibit additional file extensions. Just make sure you know what the account will be used for before blocking certain file types.
If the account is going to be used to transmit XML documents you want to make sure you aren’t blocking that file extension.
On the final screen simply review the steps that will be taken and click Finish.
Assuming everything was configured correctly you should see the following screen indicating that the Database Mail profile and account were created successfully.
At this point you should be all set!
The best way to confirm that everything was configured correctly is to send a test email message from the server.
In order to do this go back to the Management node in SQL Management Studio, right-click Database Mail and select Send Test E-Mail…
Provide a valid email address in the following window and click Send Test E-Mail.
If everything was configured properly you should receive the email shortly. Otherwise you may need to troubleshoot the configuration to pin point where things went wrong.
You also have the option to configure Database Mail using the sp_configure
system stored procedure. The steps outlined below will guide you through the process of manually activating Database Mail as well as creating a Database Mail account.
The first step is to enable Database Mail.
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
Once Database Mail has been enabled you can also create the necessary account by calling the sysmail_add_account_sp
stored procedure as shown below. Though there are actually more options available than the ones shown below, I have done my best to mimic the same options provided in the Database Mail Wizard’s New Database Mail Account window.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = '',
@description = '',
@email_address = '',
@display_name = '',
@replyto_address = '',
@mailserver_name = '',
@port = 25;
Once this has been executed you can then verify that the account was created by navigating back to the Database Mail node in Management Studio, right-clicking it and selecting Configure Database Mail. This time you will want to select the radio button next to Manage Database Mail accounts and profiles and then choose to View, change or delete an existing profile.
You should see the newly created account.
I hope this overview has provided some valuable insight regarding the ways Database Mail can be configured within SQL Server. Though I typically go the code route when configuring Database Mail on any new servers, using the Wizard is an excellent way to get a deeper understanding of the options available if you are configuring it for the first time.