Database mail services provides the good option of sending mail to client using SMTP server (Simple Mail transfer Protocol). In earlier versions of sql server this was known as SQL Mailing service which use MAPI(Messaging Application Programming Interface) for sending mails. Service broker need to be running in sql server 2005 for configuring the Database mail.
SMTP server is more secure and reliable compare to MAPI services.
Step by Step approach to Configure Database Mail
To configure database mail in your sql server enviornment, you have to follow below steps
To enable the Database Mail run the below query
sp_configure ‘Database Mail XPs’,1
In Older version
sp_configure ‘SQL Mail XPs’,1
By using Database mail configuration wizard
Connect to SQL Server using SSMS (SQL Server Management Studio)
Click on Management Folder
· Right click on Database mail from the list
· Select the first option to configure the new database profile and mail account.
· Click on next
· Here provide the Profile Name (Meaningful name which describe purpose)
· Description is optional
· To create SMTP account Click on Add
· Click on new Account
· Here Give the Account Name with description below
· Provide the name id of the using which mails are going to be send by SMTP Server(ex : Sandy@gmail.com)
· Give the display name (This acts as alias for your email id like ‘SQL DBA’)
· Reply email is optional
· Now give IP address of the SMTP Server
· Select basic Authentication in SMTP Authentication option(give your username and password).
· Now click on next
· If you want to make this profile as default select yes from the drop down list
· Click on Next and finish
For testing of database mail, right click on the database mail option – select send test Email – select the database profile and give the email id to receive the mail.
After minute or two you will receive the mail in your inbox if everything fine.
Configure the Database Mail using Script
Follow the below steps for configuring database mail server using transact sql.
1. Create the database Mail Account
@account_name = ‘DBAAccount’,
@description = ‘Database mail account’,
@email_address = ‘ sqlDBA@email.com ‘,
@replyto_address = ‘sqlDBA@email.com’,
@display_name = ‘SQL DBA’,
@mailserver_name = ‘smtp.email.com’ ;
2. Create database mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = ‘DBAProfile’, @description = ‘DBA Profile for sending mail’
3. Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = ‘DBAProfile ‘, @account_name = ‘DBAAccount’, @sequence_number =1
4. Grant access to the profile to all users in the msdb database
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = ‘DBAProfile’, @principal_name = ‘public’, @is_default = 1
Using below sp_send_dbmail procedure you can send the test email
EXEC msdb.dbo.sp_send_dbmail @profile_name = ‘DBAProfile’, @firstname.lastname@example.org, @subject = ‘Test mail’, @body = ‘This is test mail sent by SQL DBA’, @body_format = ‘HTML’ ;