How to configure Database Mail – MS SQL Server
Database Mail is easy to configure, but I thought of putting steps together to save your little time and ensure no issue when you do this. You may come across two Question Message Prompt during this configuration and if you do not perform those two steps manually then Database Mail configuration will take longer to complete whereas same can be accomplished quickly by following steps outlined below. Those two Question Message Prompt will be for “Service Broker” and “Database Mail” feature. Now Lets start and see when these message comes and what you should do. You may take care of these before you even start.
Open SQL Server Management Studio, Expand Management and right click on Database Mail; that will bring following screen:
Click on “Configure Database Mail” that will take your to Step 2.
Select “Set up Database Mail……..” as selected above and click “Next”
It may prompt you following screen shot.
Pleas DO NOT click “Yes”. Click “No” that may prompt another message as shown below:
Again Click “No” DO NOT CLICK YES
Now open Query Window in SSMS and run following to enable “Service Broker”
ALTER DATABASE MSDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE
In order to enable “Database Mail”, run following:
sp_configure ‘Database’,1 GO reconfigure with override GO
Since, you clicked on “No” for both “Service Broker” and “Database Mail Feature” so you need to start it all over again but this time it will NOT prompt you for any of these. Wizard will take you to next step i.e. Step 3
Create Mail Profile by providing information in below screen using “Add” buttion in above screen.
After you provided all required information as shown above, Click “Next” that will update previous screen with following detail:
Now Click Next
If you wish to all all users to access your mail profile then you may make in Public as shown above.
Review all information, and if found correct then Click “Finish” that will bring following status screen:
Above screen confirms successful configuration of Database Mail.
Now you should try sending a test email to verify. In order to do that follow steps below:
Right Click on “Database Mail” and then Click on “Send Test E-Mail” as shown below:
That will bring following screen, provide email address where you would like to receive email as show below:
Click “Send Test E-Mail” that will prompt following screen:
Now check your mailbox, you should have received an email.
Database Mail is easy to configure, my personal experience if you take care of “Service Broker” and enable “Database Mail” manually then it makes Database Mail configuration very easy and quick.