Imagine going through your inbox on Monday morning and reading a message from your SQL Server
informing you that someone has accessed confidential salary information from the corporate database.
This messaging scenario is possible with SQLMail, which comes with SQL Server. You can send and
receive mail messages between Windows Messaging applications such as Microsoft Exchange, and
Microsoft SQL Server 6.x.
With SQLMail, you can easily set up procedures within SQL Server to send plain-English alert
messages to designated users. For example, you can have SQLMail forward nightly database maintenance
logs to an administrator. Depending on your database application, you can define triggers that
monitor various queries on sensitive data such as salary information. Also, users can send database
queries to SQL Server through standard mail messages.
Figure 1 shows Microsoft's building-block approach to SQL and Exchange integration:
SQL builds on Messaging API (MAPI), which is part of Windows NT. NT acts as a standard mail client
and addresses the Exchange server. The result is an open, commodity-based approach to messaging that
avoids using proprietary BackOffice application-specific components to share information. Let's
examine what makes SQLMail tick and how to configure SQL Server for SQLMail integration with Windows
Messaging.
Configuring SQLMail on the Server
SQLMail is a group of SQL Server extended stored procedures that let SQL Server interact with
external NT Server programs (in this case Exchange Server), an MS Mail postoffice, or an MS
Mail-compatible postoffice under NT, through sqlmap60.dll. The SQLMail procedures use NT's basic
MAPI mail functions, such as send mail, receive mail, and process mail. With SQLMail, you can
receive messages from the SQL Server for tasks such as alerts or triggers, or you can send SQL
queries for data retrieval through ordinary mail messages. SQLMail lets you set up a SQL Server as
an MS Mail-compatible client. This configuration lets the SQL Server send and receive mail with an
Exchange Server or MS Mail postoffice, both of which are MAPI compliant.
SQLMail functionality requires a pre-existing MS Mail-compatible postoffice, such as an
Exchange Server mailbox, MS Mail postoffice, or an NT Mail postoffice. The configuration you choose
determines how you must configure SQLMail connectivity. (For tips on how you can avoid problems when
setting up SQLMail, see the sidebar, "Troubleshooting SQLMail Configuration.")
If you are using an existing Exchange Server, set up an Exchange user mailbox for the SQL
Server to use as its message store. This account must match that of the MSSQLSERVER service in the
NT Control Panel. Although you can use the default LocalSystem account to set up SQLMail with the
SQL Server, I recommend that you use a valid domain account instead. This setup not only facilitates
SQLMail functions but also is required if you use SQL database replication. Later, when you use
advanced SQL functionality, this setup will reduce headaches.
If you're still using MS Mail, you must use the ADMIN program to set up a mail account for SQL
Server in the postoffice you want. In small environments, or those where an Exchange Server isn't
available, you can set up an NT postoffice on the server. NT postoffices are a carryover from the
Windows for Workgroups postoffice (WGPO). A WGPO is an MS mail-compatible postoffice that you can
use for Windows Messaging within an NT domain. To set up an NT postoffice, select the icon for the
MS Mail postoffice from the Control Panel. The first time you launch the icon, you'll get a prompt
to connect to an existing postoffice or create a new one. Select the option to create a postoffice,
and you'll see a prompt for the administrator account details that you'll use to administer the
postoffice. Once you've completed the required information, use Explorer to share the WGPO
directory, and make sure that the MSSQLSERVER account has sufficient privileges to access the share.
Configuring the SQLMail Client
The next step in setting up SQLMail is configuring the Windows Messaging or Exchange client on
the NT server. If you're using Office 97, the Outlook client is also compatible. To create the
required Registry entries, you must configure the client on the NT server. First, configure a
Windows Messaging profile for testing basic mail send and receive functionality outside SQL Server.
From Control Panel, open the Mail and Fax icon. Click Show Profiles, and add a profile for the SQL
Server. This profile must contain only information services for Microsoft Mail, Personal Address
Book, and Personal Folders.