INF: How to Configure SQL Mail (263556)

MORE INFORMATION

Overview of SQL Mail

SQL Mail allows SQL Server to send and receive e-mail by establishing a client connection with a mail server. SQL Server version 6.5 uses a single service to handle mail. SQL Server 7.0 and SQL Server 2000 use two services to handle mail:

• MSSQLServer: Processes mail for all of the mail stored procedures using simple MAPI (SQL Server 6.5 and SQL Server 7.0) or extended MAPI (SQL Server 2000).
  
  -and-
  
  
• SQLServerAgent: Uses its own mail capabilities that are configured and operated separately from SQL Mail and uses simple or extended MAPI.

The SQL Server Agent mail features are referred to as SQLAgentMail to distinguish it from the SQL Mail features provided by MSSQLServer.

SQL Mail and SQLAgentMail establish a MAPI connection with a mail host. Both SQL Mail and SQLAgentMail can connect with Microsoft Exchange Server or a Post Office Protocol 3 (POP3) server. SQLMail and SQLAgentMail for SQL Server 6.5 and SQL Server 7.0 can also connect with Microsoft Windows NT Mail, but this configuration is not supported for SQL Server 2000. Microsoft recommends the use of Exchange Servers for reliability due to the limitations and logon issues of the POP3/SMTP protocols. Microsoft does not support using SQL Mail to communicate with mail servers such as Lotus Notes, Lotus cc:Mail, or Novell GroupWise unless they are treated as POP3 servers.

SQL Mail is a mail-enabled application. When you send mail, SQL Mail uses the Mapi32.dll file to make an API call to start the MAPI spooler of the Windows Messaging subsystem (Mapisp32.exe), send the mail, and then shut the spooler down. In older mail clients, the MAPI spooler was not designed to be used with an NT service. With these older MAPI spoolers, outgoing mail generated by SQL Mail remains stuck in the Outbox until the mail client is opened. The problem was fixed in Microsoft Windows NT 4.0 Service Pack 3, but was not incorporated into subsequent NT 4.0 service packs due to product incompatibilities.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: Several problems with configuring SQL Mail are due to use of an incorrect Mapi32.dll file.

Before You Set Up SQL Mail

SQL Mail requires a mail connection, a mailbox, a mail profile, and the Windows NT account you used to start SQL Server. This Windows NT account needs to be a domain account if you are using an Exchange server as your mail server. It can be a local or domain NT account if you are using POP3/SMTP mail server.

The basic procedure is to log on to your Windows NT server using the account you used to start SQL Server, set up your mail profile, test the mail connection, and then provide this mail profile to SQL Mail. When you choose a profile name, try not to include special characters (such as hyphens, pound signs, periods, and so forth) because they may work with Exchange clients (such as Outlook), but not with the SQL Server 6.5 version of SQL Mail. For the same reason, the profile name cannot be longer than 32 characters.

For best results, use Microsoft Exchange Server for sending and receiving SQL Mail. Microsoft Windows NT Mail is a limited mail program that was included with Microsoft Windows NT prior to the release of the full featured mail services provided by the Microsoft Exchange Server product line and cannot be used with SQL Mail 2000.

If you are using a third-party mail server (such as Lotus Notes, Lotus cc:Mail, Novell GroupWise, and so forth), you must configure the mail server as a POP3 server. Microsoft does not support connecting to these mail servers using the native mail services that might be installed by the third-party mail client. Some third-party mail clients do not support the MAPI required by SQL Mail. Contact the third-party vendor or refer to the http://www.microsoft.com/exchange/default.asp Web site for assistance concerning the use of their mail clients or the availability of a Exchange connector to integrate both Exchange and your current mail service provider.

How To Set Up SQL Mail With Exchange

1. On the Microsoft Exchange server, set up a mailbox for the user account that is to be used by the MSSQLServer service. This account must be a domain account. For example:

   Account: DOMAIN1\SQLServerAccount
   Mailbox: SQL1

2. On the SQL Server computer, log on to Windows NT by using the same user account that is to be used by the MSSQLServer service.
   
   In the preceding example in step 1 it would be:

   DOMAIN1\SQLServerAccount

3. Install an Exchange client on the SQL Server computer. When running SQL Server 6.5 or SQL Server 7.0, this can be the client application that ships with Microsoft Exchange Server, Microsoft Outlook 98 or Microsoft Outlook 2000. The addition of the Outlook security patch for Outlook 2000 to prevent the spread of e-mail viruses will cause SQL Mail 6.5 or 7.0 to stop responding (hang). Because Outlook 2002 also includes this security feature, it will also cause SQL Mail 6.5 or 7.0 to hang. (See the "Outlook 2000 Client" section later in this article for a detailed explanation of the cause.)
   
   When running SQL Server 2000, the mail client must be the Microsoft Outlook 2000 or Outlook 2002 client. Because SQL Mail 2000 makes an extended MAPI connection to the mail server, it is not affected by the Outlook security features. With SQL Server 2000 Service Pack 1, you can also use Outlook 98.
   
   For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

   281293 FIX: SQL Mail 2000 Needs Outlook 2000 Client

4. Start the Exchange client and configure the client to connect to the Microsoft Exchange Server. You will need to provide the name of the Exchange server and the mailbox on the Exchange server. After the setup is complete, verify that you can send and receive mail interactively. You can then close the client.
5. Start the Mail application in Control Panel. Click Show Profiles to find the name of the profile that was configured in step 4. If the profile name is longer than 32 characters or contains unusual characters (periods, hyphens, pound signs, and so forth), change the profile name to be less than 32 characters and remove the unusual characters (spaces are okay).
6. In Control Panel, click Services, and then verify that the MSSQLServer service is configured to run under the same Windows NT user account that you logged on with in step 2. Start or restart the MSSQLServer service if necessary. In SQL Server 7.0 and SQL Server 2000, make any startup account changes by way of the SQL Enterprise Manager if you are using Full Text Search on the computer.
7. Configure SQL Mail to use the profile name you found in step 5. SQL Mail uses the account and password configured for the MSSQLServer service to login to the Exchange server.
   
   NOTE: If the profile name that you type in is not recognized or no profiles appear in the drop-down list box for SQL Server 7.0 or SQL Server 2000, you have started the MSSQLServer service under the Local System account. Change the service to start under a domain account, stop and restart SQL Server, and then try to configure the mail profile again.
8. Start SQL Mail. The SQL Mail icon becomes green if SQL Mail starts successfully. (This step is not necessary when using SQL Server 2000 because SQL Mail will be automatically started when you first try to send mail.) Test SQL Mail by opening a query window and use xp_sendmail to send e-mail to yourself. If you are using SQL Server 7.0 or SQL Server 2000, also configure SQLAgentMail with the same profile. You can test SQLAgentMail by sending mail to an operator.

How To Set Up SQL Mail With An Internet Mail Server

WARNING: Unless you can guarantee 100% connectivity to your Internet mail server without the need to reauthenticate your mail logon, SQL Mail can hang, forcing you to stop and restart SQL Server. Use of an Internet mail server is not a recommended solution but may be the only alternative in some cases.

The procedure for setting up SQL Mail with an Internet mail server (POP3/SMTP server) is similar to that for setting up SQL Mail with an Exchange Server:

1. On the POP3 server (for incoming e-mail), set up a mailbox for the user account that is to be used by SQL Mail. You need to have an account on the POP3 mail server (even if there will never be incoming mail for SQL Mail to process) because the mail clients are designed to first check for incoming mail before sending outgoing mail. You must also set up a mailbox for outgoing mail on the SMTP server if it is different from the POP3 server.
2. Install an Internet Mail client on the SQL Server computer. The only Internet Mail client applications supported by Microsoft when you use SQL Mail 6.5 or SQL Mail 7.0 are Microsoft Windows Messaging (provided with Windows NT 4.0), Microsoft Outlook 98, and Microsoft Outlook 2000. The addition of the Outlook security patch for Outlook 2000 to prevent the spread of e-mail viruses will cause SQL Mail 6.5 or 7.0 to hang. Any non-Exchange messages sent with Outlook 2002 require the Outlook client to be actively running on the server.
   
   The only Internet Mail client application that can be used when you use SQL Mail 2000 is Microsoft Outlook 2000. You could also use Outlook 2002 if you are willing to keep the Outlook client open on the server at all times. With SQL Server 2000 Service Pack 1, you can also use Outlook 98.
   
   For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

   281293 FIX: SQL Mail 2000 Needs Outlook 2000 Client

   Because Microsoft Outlook Express does not create a Mail profile, Outlook Express will not work with SQL Mail.
3. Log on to the Windows NT server by using the Windows NT account you used to start SQL Server. Start the Internet Mail client and configure a profile to use the Internet Mail service and to connect to the POP3 (incoming) and SMTP (outgoing) servers. The account name and password to provide for access to the POP3/SMTP servers is the mail account you created in step 1 (it is not necessarily the same as the account used to start SQL Server). After setup is complete, verify that you can send and receive mail interactively. Then you can close the mail client application.
   
   Setting up the Internet Mail service creates a Personal Store file (.pst) to store downloaded mail messages from the POP3 server. This .pst file is used by any service that is using this mail profile, and causes a conflict if SQL Mail and SQL Agent Mail both use the same mail profile. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

   313969 PRB: The Error 'Cannot Open .pst File' Occurs When You Use a POP3 Server Profile with SQL Mail

   If you specify a location for the .pst file, do not use a network share.For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

   297019 OL2000: Why .pst Files Are Not Recommended over a LAN or WAN Link

4. In Control Panel, click Mail to open the mail application. Click Show Profiles to find the name of the profile that was configured in the previous step (for example, "Internet Settings"). The same restrictions on the length of the profile and unusual characters apply as in the case of setting up SQL Mail with an Exchange server.
5. In Control Panel, click Services, and then verify that the MSSQLServer service is configured to run under the same Windows NT user account you used to setup the mail profile. Start or restart the MSSQLServer service, if necessary.
6. Configure SQL Mail to use the profile name you found in step 4.
   
   NOTE: If the profile name you type in is not recognized or no profiles appear in the drop down listbox for SQL Server 7.0 or SQL Server 2000, you have started the MSSQLServer service under the Local System account. Change the service to start under a valid Windows NT account, stop and restart SQL Server, and then try to configure the mail profile again.
7. Start SQL Mail. The SQL Mail icon becomes green if SQL Mail has started successfully. (This step is not necessary when using SQL Server 2000 because SQL Mail will be automatically started when you first try to send mail.) Test SQL Mail by opening a query window and use xp_sendmail to send e-mail to yourself. If you are using SQL Server 7.0 or SQL Server 2000, also configure SQLAgentMail with the same profile. You can test SQLAgentMail by sending mail to an operator.

WARNING: If you attempt to use SQL Mail to establish a connection to a POP3/SMTP mail server you MUST be able to repeatedly connect and send mail by using the following steps:

1. Click the icon that starts the mail client.
2. Send a message to the same e-mail address listed in the profile so that you can test both the send and receive capability.
3. Exit the mail client.
4. Repeat steps 1 through 3 several times to simulate expected e-mail traffic.

NOTE: If at any time during this process any dialog boxes appear that require a response (such as clicking OK to log on) or you are prompted to enter a password, it will not work with SQL Mail. For an Internet mail connection to work with SQL Mail, you must have 100% connectivity or SQL Mail is not usable and you might have to restart your server to clear up the problem.

The reason for this is that SQL Mail does not provide for retries when accessing a POP3 account. If SQL Mail cannot connect to the POP3 server on the first attempt, the Internet Mail Connector normally opens a dialog box that prompts you to click OK to retry. It that should occur, SQL Mail, which is running as part of the MSSQLServer service, never sees the dialog box and stops responding at this point. You might have to stop the Mapisp32.exe application or even restart your Windows NT computer to clear the problem.

Windows Messaging Client

This client can be used with SQL Server 6.5 and 7.0 but cannot be used with SQL Server 2000. The Microsoft Windows Messaging mail client ships with Windows NT 4.0 and supports Microsoft Mail and Internet Mail (POP3/SMTP). There is a known issue with outgoing mail sent by SQL Mail being stuck in the Outbox unless the Windows Messaging client is open. This problem was caused by the MAPI spooler not being designed to be called from within a Windows NT service. This problem was fixed with Microsoft Windows NT 4.0 Service Pack 3 but the fix was not included in later Windows NT service pack builds because it caused product incompatibilities.

If you installed Windows Messaging prior to applying Service Pack 3, you most likely have the fix for this problem. If you installed or reinstalled Windows Messaging after applying Service Pack 3, you need to contact Microsoft Product Support Services to obtain a fix for the Mapi32.dll file. To determine if you need a fix, find the Mapi32.dll file on the computer and check the date and size of the file. For example, if the date of your Mapi32.dll is 10/14/1996 and the size is 621 KB (file version 4.0), that file version causes outgoing SQL Mail to be stuck in the Outbox. If the Mapi32.dll file is dated 5/1/1997 and the size is 701 KB (the date and size of the fix included in Windows NT 4.0 Service Pack 3), that is the correct version of the file (also version 4.0). Because the version numbers are the same, it is more reliable to use the size and date of the file rather than the version number when you are determining whether or not you have the correct Mapi32.dll file.

Windows Messaging is not included in Microsoft Windows 2000, so you need to purchase Microsoft Outlook to reliably use SQL Mail with an Internet mail server if you are using Windows 2000.

Outlook 97 or Outlook 98 Client

The Outlook 98 client can be used with SQL Server 6.5 and 7.0 but cannot be used with SQL Server 2000 unless you have applied SQL Server 2000 Service Pack 1.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base: Microsoft Outlook supports Exchange mail and Internet mail (POP3/SMTP). Outlook 97 shipped with Microsoft Office 97 and installs a Mapi32.dll file dated 12/20/96 with a size of 714 KB. Because Outlook 97 was not designed to be used within a Windows NT service, the Mapi32.dll file (version 4.00.993.3) causes outgoing e-mail from SQL Mail to be stuck in the Outbox if the Outlook client is not left open on the server. To resolve this problem, upgrade to Outlook 98 or Outlook 2000.

Outlook 98 is a stand-alone product, intended as a replacement for Outlook 97. When you install Outlook 98, select the Corporate or Workgroup option. Do not select the Internet Only option. The Internet Only option installs only a stub (32 KB in size, dated 3/26/1998, version 5.5.2163.0) of the Mapi32.dll file and this file version does not work with SQL Mail. The correct Mapi32.dll version for Outlook 98 that works with SQL Mail is 5.5.2174.0, or later (dated 3/26/1998 and 857 KB). The Corporate or Workgroup option still allows you to use Outlook 98 with a POP3/SMTP Internet mail server. If you have installed Outlook 98 with the Internet Only option, in Control Panel, use Add/Remove Programs to rerun the Outlook 98 setup and reinstall the Outlook 98 components. You will also need to re-create your mail profile.

Outlook 98 does not support Internet Message Access Protocol (IMAP)-based mail with the Corporate or Workgroup option. If you need IMAP support, you need to set up Outlook Express (in addition to Outlook) to send and receive IMAP-based mail. Use Outlook Express for sending and receiving IMAP-based mail and provide the Outlook mail profile for SQL Mail.

Outlook 2000 Client

This client can be used with SQL Server 6.5, SQL Server 7.0, and SQL Server 2000.

Microsoft Outlook 2000 supports Exchange mail and Internet mail (POP3/SMTP) and ships with Microsoft Office 2000. The MAPI model was completely changed in Outlook 2000. Instead of a complete MAPI library, Outlook 2000 uses only a MAPI stub (128 KB in size). This module points to the correct MAPI library file to use, based on the language installed on the Windows NT computer (for example, the Msmapi32.dll file in the \Program Files\Common Files\System\Mapi\1033\NT folder). Replacing the Mapi32.dll stub with a different version breaks Outlook 2000 and you will need to run Fixmapi.exe to correct the problem.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:
Outlook 2000 also has to be installed with the Corporate or Workgroup option. To verify that you have installed Outlook 2000 properly, on the Outlook 2000 Tools menu, click Options. Click the Mail Services tab or the Mail Delivery tab, depending on how Outlook is currently configured. You will see a Reconfigure Mail Support command button. If you click Reconfigure Mail Support, a dialog box opens. If the Corporate or Workgroup option is not selected, click to select it and Outlook 2000 will reconfigure itself without requiring you to remove and reinstall the program. However, you must re-create your mail profile.

NOTE: If you do make a change and you select the Corporate or Workgroup option, you must restart SQL Server for the changes to take effect.

After you install Outlook 2000, there should be at least two Mapi32.dll files, one dated 1/14/1999, version 1.0.2536.0 that is in the \Winnt\System32 directory (for backward compatibility with mail-enabled applications like SQL Mail but is not used by Outlook 2000) and another file dated 1/26/1999, version 1.0.2518.0 in the \Program Files\Common Files\System\Mapi\1033\NT directory (used by Outlook 2000). If you do not have a Mapi32.dll file in the \Winnt\System32 directory, Outlook 2000 may work properly but you will not be able to start SQL Mail. If that happens, copy the Mapi32.dll file from the \Program Files folder to the \Winnt\System32 folder.

In response to the threat posed by e-mail worms, Microsoft released a security patch for Outlook 2000 that notifies the user when a non-Outlook program attempts to send mail and requests the user to indicate whether this should be allowed by clicking Yes or No. Because SQL Mail is run through the SQL Server service, this security pop-up screen is sent to the virtual desktop and is never seen by a user. Because mail will not be sent pending user interaction, the end result is that SQL Mail will hang.

This behavior can be seen with SQL Mail for SQL Server 6.5 or SQL Server 7.0 because they make a simple MAPI connection to the mail client and this will trigger the security pop-up screen. Because SQL Mail for SQL Server 2000 makes an extended MAPI connection, the security pop-up screen is bypassed.

If your version of Outlook 2000 is 9.0.0.4201 or later (as seen in the "About Microsoft Outlook" box in Outlook 2000), you have the Outlook security update installed. Contact your Exchange administrator to see if it is possible to relax the default security constraints.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Outlook 2002 Client

This client can be used with SQL Server 2000.

Microsoft Outlook 2002 supports Exchange mail and Internet mail (POP3/SMTP) and ships with Microsoft Office XP. Outlook 2002 employs a unified configuration and does not provide separate configuration options for Corporate or Workgroup or Internet Mail Only. Outlook 2002 also includes security features that first appeared in the Outlook 2000 security patch to protect against e-mail worms.

In Outlook 2002, the MAPI spooler logic was moved in-process, so any non-Exchange messages sent (such as those to an Internet mail server) require the Outlook client to be actively running on the server computer. If the Outlook client is not left open, outgoing messages will remain in the Inbox until such time as the Outlook client is opened when they will be sent. This behavior is not seen with messages sent to an Exchange server.

The Microsoft Outlook 2002 e-mail security features provide additional levels of protection against malicious e-mail messages. The important feature from the SQL Mail perspective is the confirmation of automatic sending of e-mail messages. Because the security features are triggered by a simple MAPI connection, Outlook 2002 when used with SQL Mail on SQL Server 6.5 and SQL Server 7.0 may cause SQL Mail to hang due to the security pop-up screen. Contact your Exchange administrator to see if it is possible to relax the default security constraints.

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

Internet Explorer and Outlook Express Client

Use of Outlook Express with SQL Mail is not supported because SQL Mail requires a mail profile and Outlook Express does not create one. Even if you have not explicitly set Outlook Express as your default mail client, installation or upgrading of Internet Explorer might cause Outlook Express to be set as your default mail client.

On some versions of Internet Explorer or Outlook Express (for example, Internet Explorer 5.0), SQL Server ignores the supplied mail profile and attempts to use the default mail client. For example, if the default mail client is Outlook Express, SQL Mail may ignore the supplied Outlook mail profile (even though the profile tested successfully with the SQL Mail Test button) and attempt to start with Outlook Express. If Outlook Express was not configured to connect to a POP3/SMTP server, SQL Mail then stops responding or reports error messages about being unable to connect to the mail server. If you attempt to start SQL Mail through SQL Enterprise Manager and it stops responding, you might have to use Task Manager to perform an End Task on the SQL Enterprise Manager to clear the problem.

To determine which default mail client you are using, follow these steps:

1. Open Control Panel and double-click Internet Options.
2. Click the Programs tab, and check if Outlook Express, Netscape Messenger or some other mail client is selected. Select Microsoft Outlook(if installed) from the drop-down combo box for e-mail.
3. Stop and restart SQL Server before you attempt to start SQL Mail.

If you are not using Internet Explorer 5.0, the selections in the Programs tab in the Internet Options dialog box appear slightly different. The options may be very different if you are using a non-Microsoft browser.

In addition to making Outlook Express your default mail client, the Internet Explorer installation might also rename your working Mapi32.dll file to Mapi32x.dll in order to install a MAPI stub for Outlook Express. If you then attempt to start SQL Mail, it fails when it attempts to use this MAPI file.

If you had a working SQL Mail configuration prior to the installation or upgrade of Internet Explorer, search your \Winnt\System32 directory to see if it contains a Mapi32.dll and a Mapi32x.dll. If there is a significant size difference between these two files (for example, 128 KB for Mapi32.dll and 701 KB for Mapi32x.dll), exchange (swap) the names of these two files and attempt to start SQL Mail again.

Additional Information

For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base: For more information, see the following Microsoft Web site:
For more information, refer to the following book: