Database Mail in SQL Server allow you to send email form SQL Server database to various recipients. This feature is used for various purpose like to get notifications to client, DBAs use this feature to get alert on SQL Server & database configuration changes, performance alerts etc.
Database Mail contains information about email account credentials like email id, login, password, SMTP server name/IP, port etc. that used to send email from database to recipients through SMTP server.
Throughout the configuration we’ll create mail profile and mail account. Now, what is mail profile? It is collection of mail accounts. Developers/DBA use mail profile to send notification/alerts. There are two types of database mail profile in SQL Server, Public and Private. Public type profile can be used by any user of the database having permission send database email. Private is mapped with particular user of the database to send emails. Here we’ll setup public mail profile.
To create database mail user must be a sysadmin and to send email you either you have to be a sysadmin or member of DatabaseMailUserRole in msdb database.
Now lets go with creating database mail and send an email using it, step-by-step:-
- Connect to your database server using SQL Server Management Studio and go to Object Explorer.
- In the Object Explorer, expand Management, right click on the Database Mail and choose Configure Database Mail option.
3. Click Next on this window.
4. If you are configuring for the first time on your database server and the Database Mail feature is not enabled, this window will appear to ask you whether you want to enable it or not. Click on Yes and move to the next window.
5. There are multiple options available at this window, as we are going to configure new profile, go with default Set up Database Mail by performing the following tasks and click on the Next.
6. Now at this window you need to put the profile name and description (optional) about the profile and click on Add button.
7. This window is important, here we’ll have to put information about the mail account. Mandatory and important information we’ll have to put are:-
- Account Name
- E-mail address
- Display Name (This is the name displayed on messages sent from this account. It’s optional)
- Server Name (This is mail server name. Here I have used Microsoft account so mentioned it’s SMTP mail server)
- Port (Port on which mail server will receive the mail request)
- Just below the server name, there’s a check box This server requires secure connection. If the mail server you are putting is SSL enabled, you have to check it.
- Now comes SMTP Authentication. There are three type of authentication here are:-
- Window Authentication using Database Engine service credentials : This option uses SQL Server service account to connect the mail server name.
- Basic authentication : If you go with this, you have to mention user name and password. like here you can see.
- Anonymous authentication : Use this option when the SMTP server does not require authentication.
Fill all this information and click Ok.
8. You’ll be back to previous window, click on Next here.
9. At this window, there two tabs Public Profile and Private Profile. As we are going to create public profile, lets go with it. Click on the check box before the profile name and click Next.
10. At this window there are some system parameter you can configure/change according to your requirement. Two parameters often used by administrators and developers are:-
- Maximum File Size (Bytes) : Value to this parameter decide what size of the attachment file user can send using the profile.
- Prohibited Attachment File Extensions : This helps administrators to prohibit type of file user can send. Related to security.
Modify if you want/need to and click on the Next.
10. At the end click Finish.
11. And Database Mail configured.
As we are done with the configuration, It’s time to test it. Right click on the Database Mail, choose Send Test E-Mail
Put recipients email address and click on Send Test E-Mail.
Here your e-mail is sent for that you get queue id. Click on Ok and its done
I got the test email in my Inbox.