2
2012
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://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

Related Posts
Leave a comment
Subscribe to this blog via Email
Old Posts
- November 2017 (3)
- October 2017 (4)
- September 2017 (2)
- May 2017 (1)
- April 2017 (1)
- July 2016 (3)
- May 2016 (1)
- April 2016 (1)
- February 2016 (2)
- January 2016 (1)
- October 2015 (1)
- September 2015 (1)
- August 2015 (1)
- July 2015 (2)
- June 2015 (3)
- April 2015 (1)
- March 2015 (1)
- December 2014 (1)
- September 2014 (2)
- April 2014 (1)
- January 2014 (3)
- October 2013 (2)
- September 2013 (2)
- August 2013 (4)
- July 2013 (1)
- June 2013 (2)
- May 2013 (5)
- April 2013 (3)
- March 2013 (1)
- February 2013 (9)
- January 2013 (11)
- December 2012 (14)
- November 2012 (3)
- October 2012 (4)
- July 2012 (2)
- June 2012 (3)
- May 2012 (2)
- April 2012 (8)
- March 2012 (6)
- February 2012 (3)
- January 2012 (1)
- December 2011 (5)
- November 2011 (8)
- October 2011 (5)
- September 2011 (3)
- August 2011 (3)
- July 2011 (3)
- May 2011 (1)
- November 2010 (1)
Tags
Calender
M | T | W | T | F | S | S |
---|---|---|---|---|---|---|
« Nov | ||||||
1 | 2 | 3 | 4 | |||
5 | 6 | 7 | 8 | 9 | 10 | 11 |
12 | 13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 | 25 |
26 | 27 | 28 | 29 | 30 |
View Post by Categories
Recent Articles
- Setting up Always ON Availability Group in Multi Subnet Cluster – Recommendations
- Configuring Replication with Always ON Availability Group
- Login failed for user ‘DOMAIN\COMPUTER$’. Reason: Could not find a login matching the name provided. [CLIENT: ]
- Modern Servicing Model (Service Pack and Cumulative Updates) for SQL Server 2017 and onwards
- Fix: SSMS 2012 opening Debug window when pressing F5