How to configure database mail in sql server 2005-08 1

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

Reconfigure

In Older version

sp_configure ‘SQL Mail XPs’,1

Reconfigure

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

Use msdb

EXECUTE msdb.dbo.sysmail_add_account_sp
@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’, @recipients=sqldba@email.com, @subject = ‘Test mail’, @body = ‘This is test mail sent by SQL DBA’, @body_format = ‘HTML’ ;

 

One comment on “How to configure database mail in sql server 2005-08

  1. Reply luck jordan Feb 19, 2013 4:57 pm

    Normally I do not read article on blogs, however I wish to say that this write-up very forced me to check out and do it! Your writing taste has been amazed me. Thank you, quite great post..watch

Leave a Reply

  

  

  


6 + 5 =