BizTalk 2013 Installation and Configuration – Configure SQL Server Database Mail feature (Part 8)

If your BizTalk Server environment uses SQL Server 2012 and you wish to configure BAM Alerts, you must have already configured SQL Server Database Mail feature before you try to configure BAM Alerts, otherwise, the BizTalk Basic configuration will ignore this feature (BAM Alerts).

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. You can read more here: https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail?view=sql-server-2017

Note: You still can have BAM Portal with activities and aggregations without alerts, this is mandatory if you want alerts.

Before we begin, we need to determine the server name and port number for the Simple Mail Transfer Protocol (SMTP) server that we will use to send e-mails (If the SMTP server requires authentication, determine the username and password for the SMTP server). In our case will be the local SMTP Server that we previous install and configure.

To Configure SQL Server Database Mail Account:

  • Press the “Windows key” to switch to Metro UI and type “SQL Management” or “SQL” and click on “SQL Server Management Studio” option on Apps menu.
BizTalk Server 2013 Database Mail SQL Management
  • In Object Explorer panel, connect to the SQL Server instance you want to configure Database Mail on and expand the server tree.
    • Expand the “Management” node
BizTalk Server 2013 Database Mail SQL Management Object Explorer Database Mail
  • Double click “Database Mail” to open the Database Mail Configuration Wizard.
    • Or right-click under “Database Mail” and select the option “Configure Database Mail”
BizTalk Server 2013 Database Mail SQL Management Object Explorer Configure Database Mail
  • On the Welcome to Database Mail Configuration Wizard page, click “Next” to continue
BizTalk Server 2013 Database Mail Welcome Database Mail Configuration
  • On the Select Configuration Task page, select “Set up Database Mail by performing the following tasks:” and click “Next” to continue.
    • Set up Database Mail by performing the following tasks: Perform all of the tasks required to set up Database Mail for the first time. This option includes all of the other three options.
    • Manage Database Mail accounts and profiles: Create new Database Mail accounts and profiles or to view, change, or delete existing Database Mail accounts and profiles.
    • Manage profile security: Configure which users have access to Database Mail profiles.
    • View or change system parameters: Configure Database Mail system parameters such as the maximum file size for attachments.
BizTalk Server 2013 Database Mail Select Configuration Task
  • If Database Mail has not been enabled, you will receive the message: “The Database Mail feature is not available. Would you like to enable this feature?” Click “Yes” to continue.
BizTalk Server 2013 Database Mail Select Configuration Task message
  • On the New Profile page, specify the “Profile name” and “Description” to be included in the profile, and click “Add…” to specify an account
BizTalk Server 2013 Database Mail New Profile
  • On the New Database Mail Account page, specify the account name, description, mail server information, and authentication type. Click “OK”
    • A Database Mail account contains the information that SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server and doesn’t correspond to a SQL Server account or a Microsoft Windows account. Database Mail can be sent using the credentials of the SQL Server Database Engine, using other credentials that you supply, or anonymously. When using basic authentication, the username and password in a Database Mail account are only used for authentication with the e-mail server. An account need not correspond to a SQL Server user or a user on the computer running SQL Server.
      • Account name: Type the name of the new account.
      • Description: Type a description of the account. The description is optional.
      • E-mail address: Type the name of the e-mail address for the account. This is the e-mail address that e-mail is sent from. For example, an account for SQL Server Agent may send e-mail from the address SqlAgent@Adventure-Works.com.
      • Display name: Type the name to show on e-mail messages sent from this account. The display name is optional. This is the name displayed on messages sent from this account. For example, an account for SQL Server Agent may display the name “SQL Server Agent Automated Mailer” on e-mail messages.
      • Reply e-mail: Type the e-mail address that will be used for replies to e-mail messages sent from this account. The reply e-mail is optional. For example, replies to an account for SQL Server Agent may go to the database administrator, danw@Adventure-Works.com.
      • Server name: Type the name or IP address of the SMTP server the account uses to send an e-mail. Typically this is in a format similar to smtp.<your_company>.com. For help with this, consult your mail administrator. As I say early we will use our local SMTP Server, so in this case, the server name will be “localhost”
      • Port number: Type the port number of the SMTP server for this account. Most SMTP servers use port 25.
      • This server requires a secure connection (SSL): Encrypts communication using Secure Sockets Layer.
      • Windows Authentication using Database Engine service credentials: Connection is made to the SMTP server using the credentials configured for the SQL Server Database Engine service.
      • Basic Authentication: Specify the username and password required by the SMTP server.
      • Anonymous authentication: Mail is sent to the SMTP server without login credentials. Use this option when the SMTP server does not require authentication.
BizTalk Server 2013 Database Mail New Database Mail Account
  • Back on the New Profile page, click “Next” to continue
BizTalk Server 2013 Database Mail New Profile back
  • On the Manage Profile Security page, you will be able to set the Public, Private and Default Profiles, select our profile as public and default and click “Next”.
BizTalk Server 2013 Database Mail Manage Profile Security
  • On the Configure System Parameters page, leave the default values and click “Next”
BizTalk Server 2013 Database Mail Configure System Parameters
  • On the Complete the Wizard page, review the actions to be performed and click “Finish” to complete creating the new account.
BizTalk Server 2013 Database Mail Complete
  • On the “Configuring…” page, ensure that all actions succeed and then click “Close”
BizTalk Server 2013 Database Mail Configuring

The Database Mail should now be configured successfully and all should be correctly set up to send emails, but we must make sure by testing it. So we will send a test email and confirm the reception. To do that:

  • Right-click on Database Mail and select “Send Test E-Mail…”
BizTalk Server 2013 Database Mail Send Test E-Mail
  • On the Send Test E-mail from windows, specify a “To:” email and click “Send Test E-Mail”
BizTalk Server 2013 Database Mail Send Test E-Mail parameters

If all goes well you should receive the email in a few seconds. If you have received the email, you have been able to configure Database Mail successfully.

Related links

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

3 thoughts on “BizTalk 2013 Installation and Configuration – Configure SQL Server Database Mail feature (Part 8)”

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top