Configure SQL Mail on SQL Server 2000 with Outlook 2007 client

Scenario is, we have SQL 2000 as database engine and outlook 2007 as mail client, now as we know SQL 2000 requires MAPI client for SQL mail configuration and from records we need minimum Outlook 2000 client, in this case we have outlook 2007 client present. I was skeptical initially that whether SQL mail will at all work with outlook 2007 or not, by going through several articles and forums, I found it to be working for most of them, so I did a deep dive and configured it for my case.

 

Standard configuration details can be found on following Microsoft article @ http://support.microsoft.com/?id=263556

 

Follow below steps to configure SQL Mail with Outlook 2007,

 

Pre-requisites:

Your SQL services (DB engine and agent services) should be running under domain service account for which email profile is active

 

Configuration Steps:

1. Login with the SQL service account instead of your own domain account

2. Open outlook 2007 client by going to Start > All programs > Microsoft Office > Microsoft Office Outlook 2007

3. Configure the email profile for your service account (assuming that you are using exchange server), or verify the settings if already configured

4. Keep outlook running and in parallel open SQL Server Enterprise Manager

5. Go to SQL Server Enterprise Manager > (Local or SQL Server name) > Support Services > SQL Mail > Properties > From drop down menu, select the ‘Outlook’ profile (if not listed, then recheck outlook email settings, profile should be active and working)

6. ‘Select ‘outlook’ and click ‘test’, it should show “Successfully Started (and stopped) a MAPI session with this profile”

7. Once done, In enterprise manager go to Management > SQL Server Agent > Right Click and go to Properties > In ‘General’ tab, go to ‘Mail Session’ and from drop down menu select ‘Outlook’ > it will request to restart SQL Agent services, click ‘yes’  

8. Once started, again go to same section in agent properties and click Test > it should show “Successfully Started (and stopped) a MAPI session with this profile”

9. Now within SQL Server Agent > Go to Operators > Right click and select ‘New Operator’ > in Name, give ‘testing mail’ > in ‘E-mail name:’ give your email id > click ‘test’ > it should send an email to the id you mentioned just now

10. SQL mail is configured and ready to use 🙂

 

Post configuration steps:

1. Go to Query analyzer and connect to SQL server where sql mail is just configured

2. Execute, “EXEC master.dbo.xp_stopmail”, it should say ‘Stopped SQL Mail Session’

3. Now, execute, “EXEC master.dbo.xp_startmail”, it should say ‘SQL Mail Session started’

 

User execute permission access on xp_sendmail:

By default sysadmin and db_owner of master database can use xp_sendmail stored proc. If you recieve request from other database users who need execute permissions on xp_sendmail procedure, then it’s bit tricky, xp_sendmail is extended stored procedure and users present in master database can only access it, so you need to do following to grant them appropriate privileges,

1. Add those users in master database first

2. Go to Master database > Users > Select appropriate user > Right click the user > Select Properties > Click ‘Permissions’ > Select radio button against ‘List all Objects’ > From the navigation bar, scroll down till you see ‘xp_sendmail’ > click the ‘Execute’ check box against xp_sendmail and click ‘Apply’ > Click OK > Click OK again

3. Now go to query analyzer adn connect with the user login (if SQL login) to which permission has been granted and send a test mail, it should show ‘Mail Sent.’

4. Job done; remember, no need to give db_owner permission to users on master database else it will create a security risk.

 

Errors and Solutions:

Error: Error 18025: xp_test_mapi_profile: failed with mail error 0x80040111

Reason: Error 0x80040111 is MAPI_E_LOGON_FAILED, this means it’s authentication failure that SQL Mail tried to connect to the mail server using the information provided in the mail profile and it failed. This error can arrive in two cases,

First case, when you try to test by going to Support Services > SQL Mail > Right Click ‘properties’ > Typing the profile name and click ‘test’, to resolve this do following,

This solution is taken from following forum link http://social.msdn.microsoft.com/Forums/en/sqlnotificationservices/thread/d76f9446-36ba-49a2-966a-8618f629cc0d

-> Log on to server using SQL Server domain account .

-> Log on to the server >> created a new profile >> Changed the SQL Agent setting to point to that profile (which was now visible..wow).

        -> Started the mail session using xp_startmail.

                SQL Mail session started.

        -> Sent a test mail : xp_sendmail ‘xxxx@xx.com’,’test’

                We should get -> Mail sent message.

 

Second case, when you try to execute xp_sendmail via query analyzer to send emails, then you may receive this error even though your test is showing successful by running it from SQL mail properties or SQL Agent properties, to resolve this, do following,

-> Go to Query analyzer and connect to SQL server where sql mail is just configured

-> Execute, “EXEC master.dbo.xp_stopmail”, it should say ‘Stopped SQL Mail Session’

-> Now, execute, “EXEC master.dbo.xp_startmail”, it should say ‘SQL Mail Session started’

-> Test now by executing, “EXEC master.dbo.xp_sendmail N‘yourmailid@yourdomain.com’, N’test mail’;”, it should say ‘Mail Sent.’

-> Check the mail box, mail should be there

 

There might be other errors as well, but I have reported the solution to errors which I encountered while doing my configuration, for other errors do leave your valuable suggestions 🙂

 

Other useful references:

http://www.techrepublic.com/blog/datacenter/how-do-i-configure-sql-mail-in-sql-server-2000/363

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/d058d250-6e89-45b6-8f24-6fdcae728b3b/

http://msdn.microsoft.com/en-us/library/ms189505(v=sql.105).aspx

http://dbaspot.com/ms-sqlserver/142159-xp-sendmail-fails.html

Safe mail: http://www.nyx.net/~bwunder/SQL2000/15090.htm