Applies to: 
SQL Server (all supported versions)
This article describes how to enable and configure database mail using the database mail configuration wizard and how to create a database mail configuration script using templates.
before we start
use the databasemail xps option to enable database mail on this server. For more information, see the database mail server xps configuration option reference article.
limitations and restrictions
enabling the sql server service agent on any database requires a database lock. if service broker was disabled in msdb, to mail-enable the database, first stop the sql server agent so service broker can get the necessary lock.
security
To configure database mail, you must be a member of the sysadmin fixed server role. to send an email with database mail, you must be a member of the databasemailuserrole database role in the msdb database.
use the database mail configuration wizard
to set up database mail using a wizard
-
In Object Explorer, expand the node for the instance you want to configure database mail on.
expand the administration node.
right-click database mail and then select configure database mail.
complete the wizard dialog boxes.
-
welcome page
select configuration task page
new account page
manage existing account page
new profile page
manage existing profile page
add account to profile page
account and profile management page
manage profile security, public tab
manage profile security, private tab
system parameters configuration page
complete wizard page
send test email page
welcome page
This page describes the steps to configure database mail.
Do not show this page again: Check this option to prevent this welcome page from being displayed in the future.
next – Goes to the select a configuration task page.
cancel: ends the wizard without configuring database mail.
select configuration task
Use the select configuration task page to indicate which task you will complete each time you use the wizard. If you change your mind before completing the wizard, use the back button to return to this page and select a different task.
set up database mail by performing the following tasks perform all the tasks necessary to set up database mail for the first time. this option includes all the other three options.
manage database mail accounts and profiles create new database mail accounts and profiles or 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 maximum file size for attachments.
database mail setup wizard
new account page
Use this page to create a new database mail account. a database mail account contains information to send email to an smtp server.
a database mail account contains the information that the sql server uses to send email messages to an smtp server. each account contains information for an email server.
a database mail account is only used for database mail. a database mail account does not correspond to a sql server account or a microsoft windows account. database mail can be sent using the sql server database engine credentials, using other credentials you provide, or anonymously. When basic authentication is used, the username and password in a database mail account are only used for authentication with the 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.
email address type the name of the account email address. this is the email address from which the email is sent. for example, an account for the sql server agent can send an email from the address sqlagent@adventure-works.com.
display name type the name that will be displayed on email 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 automail” in email messages.
reply email type the email address to use for replies to email messages sent from this account. the reply email is optional. for example, responses to an account for the sql server agent might go to the database administrator, danw@adventure-works.com.
server name type the name or ip address of the smtp server that the account uses to send email. it usually has a format similar to smtp.<your_company>.com. for help with this, check with your mail administrator.
port number type the port number of the smtp server for this account. most smtp servers use port 25 or 587, or port 465 for ssl connections.
This server requires a secure connection (ssl) encrypts communication using a secure socket layer.
windows authentication using database engine service credentials is done with 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.
username type the username that database mail uses to log in to the smtp server. username is required if the smtp server requires basic authentication.
password type the password that database mail uses to log in to the smtp server. password is required if the smtp server requires basic authentication.
confirm password type the password again to confirm the password. password is required if the smtp server requires basic authentication.
anonymous authentication mail is sent to the smtp server without login credentials. use this option when the smtp server does not require authentication.
manage existing account page
Use this page to manage an existing database mail account.
account name select the account to view, update or delete.
delete delete the selected account. you must remove this account from associated profiles, or delete those profiles, before deleting the selected account.
description view or update the account description. the description is optional.
email address view or update the name of the account email address. this is the email address from which the email is sent. for example, a microsoft sql server agent account can send an email from the address sqlagent@adventure-works.com.
display name View or update the display name on email 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 the sql server agent can display the name sql server agent automail in email messages.
reply email view or update the email address to use for replies to email messages sent from this account. the reply email is optional. for example, responses to an account for the sql server agent might go to the database administrator, danw@adventure-works.com.
server name View or update the name of the smtp server that the account uses to send email. it usually has a format similar to smtp.<your_company>.com. for help with this, check with your mail administrator.
port number view or update the smtp server port number for this account. most smtp servers use port 25 or 587, or port 465 for ssl connections.
This server requires a secure connection (ssl) encrypts communication using a secure socket layer.
windows authentication using database engine service credentials is done with 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.
username view or update the username that database mail uses to log in to the smtp server. username is required if the smtp server requires basic authentication.
password change the password that database mail uses to log in to the smtp server. password is required if the smtp server requires basic authentication.
confirm password type the password again to confirm the password. password is required if the smtp server requires basic authentication.
anonymous authentication mail is sent to the smtp server without login credentials. use this option when the smtp server does not require authentication.
new profile page
Use this page to create a database mail profile. a database mail profile is a collection of database mail accounts. profiles improve reliability in cases where an email server cannot be reached by providing alternate database email accounts. at least one database email account is required. For more information on setting the priority of database mail accounts in the profile, see Create a database mail profile.
Use the up and down buttons to change the order in which the database email accounts are used. this order is determined by a value called the sequence number. move up decreases the sequence number and move down increases the sequence number. the sequence number determines the order in which database mail uses the accounts in the profile. for a new email message, the database mail starts with the account that has the lowest sequence number. if that account fails, Database Mail uses the account with the next higher sequence number, and so on until either Database Mail successfully sends the message or the account with the next higher sequence number. high fail. if the account with the highest sequence number fails, Database Mail stops trying to send mail for the time configured in the Database Mail accountretrydelay parameter, and then starts the process of trying to send the mail again, starting with the lowest sequence number. use the database mail accountretryattempts parameter to configure the number of times the external mail process attempts to send the email message using each account in the specified profile. you can configure the accountretrydelay and accountretryattempts parameters on the configure system parameters page of the database mail configuration wizard.
profile name type the name of the new profile. the profile is created with this name. do not use the name of an existing profile.
description type a description for the profile. the description is optional.
smtp accounts choose one or more accounts for the profile. priority sets the order in which accounts are used by database mail. If no account is listed, you must select add to continue and add a new smtp account.
add adds an account to the profile.
delete delete the selected account from the profile.
upload increases the priority of the selected account.
move down decrease the priority of the selected account.
manage existing profile page
Use this page to manage an existing database mail profile. a database mail profile is a collection of database mail accounts. profiles improve reliability in cases where an email server cannot be reached by providing alternate database email accounts. at least one database email account is required. For more information on setting the priority of database mail accounts in the profile, see Create a database mail profile.
Use the up and down buttons to change the order in which the database email accounts are used. this order is determined by a value called the sequence number. move up decreases the sequence number and move down increases the sequence number. the sequence number determines the order in which database mail uses the accounts in the profile. for a new email message, the database mail starts with the account that has the lowest sequence number. if that account fails, Database Mail uses the account with the next highest sequence number, and so on, until either Database Mail successfully sends the message or the account with the next highest sequence number fails. high fail. if the account with the highest sequence number fails, Database Mail stops trying to send mail for the time configured in the Database Mail accountretrydelay parameter, and then starts the process of trying to send the mail again, starting with the lowest sequence number. use the database mail accountretryattempts parameter to configure the number of times the external mail process attempts to send the email message using each account in the specified profile. you can configure the accountretrydelay and accountretryattempts parameters on the configure system parameters page of the database mail configuration wizard.
profile name select the name of the profile to manage.
delete delete the selected profile. You will be prompted to select yes to delete the selected profile and reject unsent messages, or select no to delete the selected profile only if there are no unsent messages.
description view or change the description of the selected profile. the description is optional.
smtp accounts choose one or more accounts for the profile. the failover priority sets the order in which the account is used by database mail in the event of a failover.
add adds an account to the profile.
delete delete the selected account from the profile.
move up increases the failover priority of the selected account.
move down decreases the failover priority of the selected account.
priority view the account’s current failover priority.
account name view account name.
email address view the account email address.
add account to profile page
Use this page to choose the account you want to add to the profile. choose an existing account in the account name box or select new account.
account name select the name of the account to add to the profile.
email address view the email address of the selected account. you cannot change the email address from this page. To change the email address of the account, return to the main page of the wizard and select the option manage database profiles and email accounts.
server name view the name of the mail server for the selected account. you cannot change the server name from this page. To change the server name of the account, go back to the main page of the wizard and select the option manage profiles and email accounts from the database.
new account Create a new account.
account and profile management page
Use this page to choose a task to manage a profile or account.
create a new account create a new account.
view, change or delete an existing account manage or delete an existing account.
create a new profile create a new profile.
view, change or delete an existing profile. you can also manage accounts associated with the profile. update or delete an existing profile. this option also allows you to manage the accounts associated with the profile.
manage profile security, public tab
Use this page to set up a public profile.
profiles are 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 (msdb) to send email using that profile.
a profile can be a default profile. in this case, users or roles can send email using the profile without explicitly specifying the profile. if the user or role sending the email message has a default private profile, Database Mail uses that profile. if the user or role does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. if there is neither a default private profile for the user or role nor a default public profile for the database, sp_send_dbmail returns an error. only one profile can be marked as default profile.
public select this option to make the specified profile public.
profile name shows the name of the profile.
default profile select this option to make the specified profile the default profile.
show only existing public profiles select this option to show only public profiles in the specified database.
manage profile security, private tab
Use this page to set up a private profile.
profiles are 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 (msdb) to send email using that profile.
a profile can be a default profile. in this case, users or roles can send email using the profile without explicitly specifying the profile. if the user or role sending the email message has a default private profile, Database Mail uses that profile. if the user or role does not have a default private profile, sp_send_dbmail uses the default public profile for the msdb database. if there is neither a default private profile for the user or role nor a default public profile for the database, sp_send_dbmail returns an error.
username select the name of a user or role in the msdb database.
access select if the user or role has access to the specified profile.
profile name view the profile name.
is the default profile select whether the profile is the default profile for the user or role. each user or role can have only one default profile.
show only existing private profiles for this user select this option to show only profiles to which the specified user or role already has access.
configure system parameters
Use this page to specify the database mail system parameters. view the system parameters and the current value of each parameter. select a parameter to see a brief description in the info panel.
account retry attempts the number of times the external mail process attempts to send the email message using each account in the specified profile.
account retry delay (seconds) the amount of time, in seconds, that the external mail process should wait after trying to deliver a message using all accounts in the profile before returning to try it with all the accounts.
maximum file size (bytes) the maximum size of an attachment, in bytes.
Prohibited Attachment Extensions a comma-separated list of extensions that may not be sent as an attachment to an email message. select the browse button (…) to add additional extensions.
minimum database mail executable 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 emails in the database’s mail queue. this parameter specifies how long the process remains active if there are no messages to process.
logging level specifies which messages are logged in the database’s mail log. possible values are:
-
normal: logs only errors
extended: log errors, warnings and informational messages
verbose – Log errors, warnings, informational messages, success messages, and additional internal messages. use verbose logging for troubleshooting.
the default value is extended.
reset all select this option to reset the page values to default values.
complete wizard page
Use this page to review the actions that the Database Mail Configuration Wizard will take. no changes are made until the wizard completes.
send test email page
use the send test email from<instance_name> to send an email message using the specified database mail profile. only members of the sysadmin fixed server role can send test emails using this page.
database mail profile select a database mail profile from the list. this is a required field. if no profiles are displayed, there are no profiles or you don’t have permission for a profile. use the database mail setup wizard to create and configure profiles. if there are no profiles listed, use the database mail setup wizard to create a profile to use.
a the email address of the recipients of the message. at least one recipient is required.
subject the subject line of the test email. change the default subject to better identify your email for troubleshooting.
body the body of the test email. change the default subject to better identify your email for troubleshooting.
the database mail test email dialog box confirms the test message that database mail attempted to send the message and provides the mailitem_id for the test email message. check with the recipient to determine if the email arrived. Email is typically received within a few minutes, but email may be delayed due to poor network performance, a backlog of messages on the mail server, or if the server is temporarily unavailable. use the mailitem_id for troubleshooting.
emailed the mailitem_id of the test email message.
troubleshooting opens this docs article.
use sql server administration studio templates to generate t-sql
to create a database mail configuration t-sql script
-
In sql server management studio (ssms), from the view menu, select template explorer.
In the template explorer window, expand the database mail folder.
double-click simple database mail setup. the template opens in a new query window.
On the query menu, select specify values for template parameters. the replace template parameters window opens.
type values for profile_name, account_name, smtp_servername, email_address and display_name . sql server management studio populates the template with the values you provide.
run the script to create the configuration.
The script does not grant access to the profile to any user in the database. therefore, by default, the profile can only be used by members of the sysadmin fixed security role. for more information on granting access to profiles, see sysmail_add_principalprofile_sp (transact-sql)
next steps
- configure sql server agent mail to use database mail
- automate administration tasks using sql agent jobs in azure sql managed instance
-
-
-