How to with Exchange:
Create an account on the domain
Setup the account in Exchange
Add the domain account the the local administrator’s group on the SQL Server
Configure a MAPI profile for the new domain account using “Internet Mail” on the SQL Server
Login to the SQL Server as that account
Modify the SQL Server so that it starts as the new Domain Account (currently, its set to startup as a Local Account)
Restart the SQL Server service
Once the server is restarted, open up EM, expand Support Services, right click on SQL Mail and select the newly created Profile. Select Test. It should work
I have tried to follow this.
I changed the MSSql server logon service account and the SQL agent account, then did the same to the SMTP service.
I get an error reading:
Could not start.
The account specified for this service is deferent from the account specified for services running same processes.
IIRC the service account needs some specific privileges. The ones I remember are:
Act as part of the operating system
Replace a process level token
There may be some others..
I recall that the documentation in SQL BOL covered this adequately.
In 2K the privilege setup is a bit more obscure but if you follow the documentation you should get it right. I managed to get it working in about an hour about 2 years ago....
The other thing that I found (That was on SQL2K SP1) was that SQLMail would only work if you installed the Outlook 2000 client on the server. I could not get it to work with any other client. This issue has been fixed in subsequent SP's (per the documentation) but I have not verified that it works with any other client.