[SQL Server Version 6.5]
For those of you who are managing SQL Server on a machine, or a domain without Exchange Server and wish to utilise the SQL Mail service for Alert Notification and Management Microsoft Mail can offer a very similar service using an NT Post Office.
A typical scenario that I manage involves an NT 4 application server running batch processes from bulk-copied data on SQL Server 6.5, the total length of which is about 5-6 working days. The server is manually logged in on the SQL Server service account with Exchange client running minimised. On my workstation I have Microsoft Mail service on Outlook 98. As the run proceeds I receive mails informing me of milestones passed or failed. On completion of the run I am sent a number of queries containing run times and checking results.
The following method is one that I have implemented on many servers successfully. I am not promising that you will not have any problems, I myself spent several weeks trying the implement SQL Mail from scratch. I searched the internet for information on SQL Mail with Microsoft Mail, but didn’t find any extensive information, so after I eventually configured SQL Mail, I devised this configuration method.
At times you may find it frustrating, but the results are worth the effort, I hope that the following will ease the installation.
Security Context for SQL Server
Creation of a NT account for SQL Server service to use as it’s security context.
- Open User Manager for Domains on the domain that SQL server is participating in.
- Create an NT user account for SQL Server to log into the network. Make sure this account (e.g. sqlsrvr) has the following properties and a password set:
- Unchecked – User Must Change Password at Next Logon
- Checked – User Cannot Change Password
- Checked – Password Never Expires
- Unchecked – Account Disabled
- Make this NT account a part of the local Administrators group on the server where SQL Server is installed.
- Still in User Manager for Domains, choose the Policies menu and select User Rights. In the User Rights dialog, select the Show Advanced User Rights checkbox.
- Add the sqlsrvr account to the following Rights:
Act As Part Of the Operating System
Log On As a Service
Microsoft Mail Post Office for NT Server
Installation of a Microsoft Post Office is accomplished the following;
- Go to Control Panel, double-click on Microsoft Mail Postoffice. If no such icon is present the Microsoft Mail component has not yet been installed (see Microsoft Personal Support Center, Knowledge Base Article 158862).
- Follow the on-screen instruction to create a post office locally.
- Create a post office Administrator account (e.g. sqlsrvr). This will be the account that SQL Server will use. Be sure to note the Name associated with the Mailbox for this account, as this is how SQL Server will reference the mail account.
- Create a post office account for each client user, again be sure to note the account note the Name associated with the Mailbox, as this is how SQL Server will reference the mail account.
- Exit Microsoft Mail Postoffice.
SQL Server Service on NT Server
SQL Server must now be configured to logon to NT under it’s own security context.
- Go to Control Panel, double-click on Services.
- In the Service list box, choose the MSSQLSERVER service. Press the Startup button. SQL Server probably is logged into the network as the System Account.
- Choose Log on as This account, this will default to the local system account, LocalSystem.
- Press the browse button [.] and select the newly created account and set and confirm the password.
- Stop and Restart the MSSQLSERVER service to have SQL Server log into the network under the new security context.
Windows Messaging on NT Server
Prior to testing SQL Mail, Microsoft Mail needs to be configured and tested independently on SQL Server. This assumes that Exchange (or similar) client has been installed on the server.
- Log out of the NT server, and log in under the SQL Server account (e.g. sqlsrvr).
- Right-click on Inbox icon on the desktop and select Properties.
- Add the following services:
SQL Mail on SQL Server
Mail now has been installed and tested manually, configuration of SQL Mail remains.
- Run SQL Setup. Select Set Server Options.
- Click Mail Login, enter the Profile Name (Point 5, section above) (e.g. Windows Messaging Settings).
- Exit from SQL Setup.
- Run SQL Enterprise Manager, and select the server on the Server Manager and expand to display the databases and services.
- Right-click on the SQL Mail icon and select Start.
- The icon should turn from red to green (wait a few seconds as it may turn back to red).
- Testing SQL Mail can be accomplished by opening ‘Managing Alerts and Operators‘ and creating a new operator for SQL Server. Select an appropriate Name (e.g. SQL Server) and enter the name of the post office account, not the mailbox, as the Email-Name.
- Confirm that everything you have spent the last half-an-hour doing by clicking on Test.
- You should be rewarded with a dialogue box informing you of the success of the mail test.
- Confirmation of this can be achieved by examining Exchange. The test mail will be either in the Outbox, awaiting delivery, or have been delivered and retain a copy in Sent Items. In which case the message itself should have arrived in the Inbox.
- Minimise Exchange, SQL Mail configuration is complete.