Microsoft SQL Server 2000 Agent Integration with Lotus Notes - PDF by uzd19483

VIEWS: 20 PAGES: 3

									Microsoft SQL Server 2000 Agent
Integration with Lotus Notes & Domino
By Peter Lee
It seems strange that although Microsoft Exchange and Lotus Domino are the major
players in the messaging market and have a similar market share, there is so little
documentation to be found on integrating Lotus Notes & Domino with Microsoft SQL
Server. A browse through SQL Books Online or the Microsoft Knowledge Base
reveals you that you can use third-party mail systems but, for obvious reasons, only
tells you how to configure Exchange. Should you opt to explore the rest of the
internet, most links point to the same FAQ page on a particular site, the instructions in
which are rather vague. In this document, I aim to provide detailed instructions on
how to use Notes & Domino rather than Exchange as a mail system with SQL 2000.

In order to test this, I built a single Windows 2000 Advanced Server machine, on
which I installed SQL 2000 Enterprise Edition. I also installed Lotus Domino 5.0.10
on the same machine (it could just as easily be installed on a different machine). I
also installed Service Pack 1 for SQL 2000 as, according to the Knowledge Base, it –
and the later service packs – include a number of fixes for the MAPI drivers provided
with SQL 2000.

Once SQL has been installed, ensure that the SQL Agent service is logging on with a
domain account (mine logs on as a user called SQLSERVICE – the same one used by
the SQL Server service itself).

Now, create a user on the Domino server for use by the SQL Agent itself. I called my
user SQLSERVICE again. One vital step here is to ensure that when this user is
created, you do not elect to store its user ID file in the Domino Directory, but instead
you store it as a file that you will use later. I also ensured that my Domino
SQLSERVICE account was a member of the Domino “Administrators” group.

Next, log on to the SQL Server machine as your SQL Service account. The next step
may seem a little strange, but you need to install Microsoft Outlook in order to use
Notes & Domino with the SQL Agent. The reason for this is simple: SQL requires a
MAPI-compliant mail system to work, and Domino is not MAPI-compliant. You
must install Outlook (I installed Outlook 2000 here) and then install the Notes client
as installing Notes adds some additional lines to the MAPISVC.INF file, which is
only created when Outlook is installed. Note that Outlook only needs to be installed
on the machine where SQL Server itself is running.

Once Notes has been installed, launch the Notes client and work your way through the
configuration wizard. Ensure than when you are asked “Who are you?” you select the
“use the following ID file” option and browse to the file created earlier when you
created your Domino SQLSERVICE account. This file must be stored on the local
machine rather than on a network share. Once the wizard is complete, the Notes
client should launch fully. Click on the Mail icon on the desktop and try sending a
mail to SQLSERVICE, then make sure that it arrives in your inbox.

Now you can launch Outlook for the first time. When you double-click on your
Outlook icon you are asked to select the mail system you wish to use. At this point,
you have two options. If you wish, you can select “Internet mail” and connect to your
Domino server’s POP3 service (assuming you have configured it accordingly).
Alternatively, you can set up a standard connection to your Domino server. To do
this, select “Corporate or workgroup” from the initial screen of the wizard, then
“Lotus Notes” from the list. You will now be asked for the password needed to
access your Notes user ID, so type it into the dialog box or, if you gave the Notes
SQLSERVICE account a blank password, just click on OK. You will now see the
final screen of the Outlook configuration wizard, which should state that you have
opted to use Lotus Notes mail. Click on “Finish” and the wizard now closes. You
may now once again double-click on your Outlook icon and Outlook should now
launch fully. You should be asked now if you want to set Outlook as your default
mail client – you must say Yes now for this to work. Try to create a new mail and
click on the TO button and make sure that you can see the contents of the Domino
Directory. It is worthwhile closing Outlook down at this point, right-clicking on the
Outlook icon and selecting Properties. If you now click on the Addressing tab and
ensure that the Domino Directory for your Domino domain is selected as the first
directory to be displayed or searched.

At this point, restart the machine. Once it has restarted, open Outlook and also open
SQL Enterprise Manager. Make sure that your SQL Server Agent service is started,
and that your Domino server is running. Open the properties of the SQL Server agent
and in the “Mail Session” section click on the drop-down and select your Outlook
mail profile you created earlier (by default it is called “Microsoft Exchange Settings”,
even though you may not be using Exchange at all!). Click on “Test” to ensure that
the mail system works, then click on “OK” at the bottom of the properties box. Now
expand the Support Services section of Enterprise Manager, open the properties of
SQL Mail, select your Outlook profile, click on “Test” to make sure all is functioning
correctly, and click on “OK” to close the dialog box.

You may now create operators within the SQL Agent section of Enterprise Manager.
When you click on the ellipsis (“…” button) to browse your list of available mail
recipients the contents of the People section of your Domino Directory will now be
displayed.

Once you have followed these steps, your SQL Agent is now configured to send its
mail messages to a Domino server rather than to Exchange.

Peter Lee
IBMCI, IBMCSA, IBMCAD, PCLI, PCLP, MCDBA, MCT, MCDST, MCP, CNA, A+
Training Instructor
Remarc Technologies
Quick start version

   1. Create a user on your Domino server for the SQL Agent to use. Ensure that its
       user ID is saved as a file rather than copied into the Domino Directory.
   2. Log on to your SQL server using the same account as your SQL Agent.
   3. Ensure that Outlook is installed. If it isn’t, install it.
   4. Install Lotus Notes (any version).
   5. Start Notes and configure it as you would normally configure a Notes client.
       Ensure that you provide the user ID file you saved in step 1 when asked to
       identify the user. Once setup is complete, close Notes.
   6. Launch Outlook. Select the “Corporate or Workgroup” option, then select
       “Lotus Notes” from the list. Enter the password for the Notes user ID file
       when prompted.
   7. Launch Outlook again. Say “Yes” when asked if you want to set Outlook as
       your default mail client.
   8. Open SQL Enterprise Manager. Expand “Management” and open the
       properties of the SQL Agent. In the “Mail Session” section select your
       Outlook profile (usually called “MS Exchange Settings”.) Click on “Test” to
       make sure that it works.
   9. Expand the “Support Services” section, open the properties of SQL Mail and
       ensure that your Outlook profile is selected. Click on “Test” to confirm that it
       works.
   10. Create operators as usual within the SQL Agent section of Enterprise manager.
       The ellipsis button will now allow you to browse the contents of your Domino
       Directory.

								
To top