Messaging database provides two configuration objects: objects configuration database allows you to configure the mail database to use when sending an email from your application database or SQL Server Agent.
Email accounts database
Email profiles database
In this section:
Relationship between objects messaging configuration database
Mail account database
Mail profile database
Configuration tasks messaging database
Additional configuration tasks messaging database (system stored procedure)
additional References
Relationship between objects messaging configuration database
The figure shows two profiles, three accounts and three users. User 1 has access to Profile 1, which uses the Account 1 and Account 2 The User 3 has access to Profile 2, which uses the Account 2 and 3 Account User 2 has access to the profile 1 and to Profile 2.
Windows Authentication: email database using the credentials of the Windows service account database engine SQL Server database for authentication on the SMTP server.
Basic authentication: email database using the user name and password specified to authenticate to the SMTP server.
Anonymous Authentication: Use this option when the SMTP server does not require authentication. Mail database does not use credentials to authenticate to the SMTP server.
The account information is stored in the MSDB database. Each account includes the following information:
Account name
Account Description
Email address of the account
Display Name Account
E-mail address to use for the response account
Name of the mail server
Type the mail server. For Microsoft SQL Server, it is always SMTP (Simple Mail Transfer Protocol).
Port number of the mail server
A column of bits indicating whether the connection to the SMTP mail server is via SSL (Secure Sockets Layer).
A column of bits indicating whether the SMTP server connection is via identification configured for Database Engine SQL Server database information.
User name to use to authenticate to the mail server, if it requires authentication
Password to use to authenticate to the mail server, if it requires authentication
The Mail Configuration Database Wizard lets you create and manage accounts easily. You can also use stored procedures in msdb configuration to create and manage accounts.
Mail profile database
A mail profile database is a sorted collection of email accounts database related data. Applications that send mail using the mail database specify profiles instead of directly accountable. Separate information on mail servers objects used by the application helps improve flexibility and reliability: Profiles provide automatic failover; thus, if a mail server is not responding, the mail database can directly send mail to another mail server. Administrators database can add, delete or reconfigure accounts without having to modify the application code or the working stages.
In addition, profiles enable administrators to monitor database access to email. Joining Database Mail User Role is mandatory to send messages using the messaging database. Profiles provide additional flexibility to administrators by allowing them to control the message senders and the accounts used.
A profile can be public or private.
Public profiles are available for all members of the database role Database Mail User Role data MSDB database. They allow all members Database Mail User Role role to send e-mail using this profile.
Private profiles are defined for security principals in the MSDB database. They only allow users to specified database, roles and role members of the sysadmin fixed server to send mail using this profile. The default profile is private and allows access only to members of the sysadmin fixed server role. To use a private profile, sysadmin should give users permission to use the profile. In addition, the stored authorization EXECUTE sp_send_dbmail procedure is given only to the members of Database Mail User Role role. A system administrator must add the user to database role Database Mail User Role data for the user to send emails.
Profiles improve reliability in situations where a mail server is no longer accessible or able to process messages. Each account profile has a sequence number. This determines the order in which the mail database uses the accounts of the profile. For a new email, messaging database uses the last account that sent a message correctly, or if no message has been sent, the account with the smallest sequence number. If this account fails, the messaging database uses the account number that has the next higher sequence, and so on until it successfully sends the message or that the account number has the sequence higher fails. If the account with the highest number sequence fails, the messaging database interrupts attempts to send the email to the time set in the parameter Account Retry Delay sysmail_configure_sp and resumes the process of trying to send the email starting from the lowest number sequence. Use Account Retry Attempts parameter sysmail_configure_sp to configure the number of times that the external mail process attempts to send the email message using each account in the specified profile.
If multiple accounts have the same sequence number, email database uses only one of them for a given email. In this case, the mail database makes no warranty with respect to the account used for this sequence number or use the same account of a message to another.