SQL Server Database Mail Configurations

SQL Server Database Mail is used to send e-mail from the SQL Server Database Engine to user. You can also send an output of any stored procedures or table records in e-mail as an excel/text attachment or in HTML format in e-mail body.

Lets configure SQL Server Database Mail.

To Configure Database Mail there are three steps as following.

1. Creating a Database Mail account.
2. Creating a Database Mail profile.
3. Link mail account with profile.

Above, three step can be done either by using Database Mail Configuration Wizard, or T-SQL code.

Configuration using Database Mail Configuration wizard

First go to object Explorer > Then expand Management Folder > Right Click on Database Mail.

Next, Right Click on Configure Database Mail.

After clicking on Configure Database Mail , a Database Mail Configuration Wizard window opens then click on Next button.

As you are configuring Database Mail for first time so, select first option Set up Database Mail…

> then click on Next button.

After clicking on Next button, a New profile window opens.

Now you nee to setup a Database Mail profile and Account as shown in below screenshot.

In Profile Name and  Description : Specify name for Database Mail profile and Description. Description is an optional.

After providing profile name and Description, Click on Add.. button to set up account and link it with profile.

Once you click on Add button, a Add Account to Profile window opens, then click on New Account button.

After clicking on New Account button, a window opens where you need to specify details for new account that will be linked with the profile.

In Account name and Description : Specify name for account and account description, account description is an optional.

In Outgoing Mail Server(SMTP) : Set up outgoing Mail Server details as follows, also shown in below screenshot.

Specify your valid gmail Id.

Server Name will be ‘smtp.gmail.com’.

port number – 587

Check the SSL for a secure connection.

In SMTP Authentication: You have to option either select window authentication using database engine service credentials or Basic Authentication.

Here we are selecting a basic authentication, and specified valid gmail Id and passwords.

Finally click on OK button.

Once you click in Ok button, you will see account details that you created are auto filled in SMTP account section.

Then Click on Next button..

After clicking on Next button, a window appears that asks you to select public profile or private profile.

In Public Profile check the profile that you have created and click on Next button.

After clicking on Next button, Click on Finish button to complete the final step.

Now you are ready to use database mail, you can check it in quickly using object explorer.

Go to object explorer, Click on Management Folder > Right click on Database Mail > Click on Send Test E-mail

Once you click on Send Test E-mail, you will see the following option for sending e – mail.

Database Mail Profile: Select database mail profile from drop down list that you want to use for sending email, it is that you have just created.

If you have multiple profile then you can choose any one that you want to use for sending e-mail.

To : Provide mail id to which you want to send e-mail, for testing purpose you can provide your mail id.

Subject: Provide e-mail subject.

Body: Specify e-mail message that will be display in e-mail body.

Finally click on Send test e-mail button

Once you click on Send test e-mail button, you will get a email notification on mail id that you provided as shown in below screenshot.

When you open above mail notification you will see a mail with body.

SELECT * FROM msdb.dbo.sysmail_event_log;

sp_configure 'show advanced', 1; 
GO
RECONFIGURE;
GO
sp_configure;
GO

GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DatabaseMailAlertProfile',

@description ='This is database Mail Alert profile.';

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp  
    @profile_name = 'DatabaseMailAlertProfile',  
    @principal_name = 'public',  
    @is_default = 1 ;
GO

EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'DatabaseMailAccount',  
    @description = 'This is Database Mail Account',  
    @email_address = '*******@gmail.com',  
    @display_name = 'Database Mail Alert',  
    @mailserver_name = 'smtp.gmail.com',
    @port = 587,
    @enable_ssl = 1,
    @username = '*******@gmail.com',
    @password = 'provide the password for gamil id' ;  
GO

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DatabaseMailAlertProfile',

@account_name = 'DatabaseMailAccount',

@sequence_number =1 ;

GO

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DatabaseMailAlertProfile',

@recipients = 'provided recipients valid mail id',

@body = 'provide e- mail message',

@subject = 'provide e-mail subject';

GO

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = 'DatabaseMailAlertProfile'

EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp @profile_name ='DatabaseMailAlertProfile'

EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''DatabaseMailAccount'

EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = 'DatabaseMailAlertProfile'

Also Read..

How to send a SQL Server query results in e-mail message

Troubleshooting Database Mail in SQL Server