Troubleshooting SQLMail Problems

Certain basic troubleshooting techniques can be used to diagnose and resolve most SQLMail problems. Typically, errors in starting a mail session or sending mail fall into two categories: permissions problems when accessing Shared File System (SFS) shares being used as post offices, and registry setup problems.

Check the following, preferably in the order given here:

1. Ensure that the SQL Server service (MSSQLServer) is assigned the LocalSystem account and that the option Allow Service To Interact With Desktop is selected. These can be checked by using the Services application in the Control Panel.
2. Ensure that the user who set up SQLMail can successfully start a mail session and send mail by using the Window NT Mail client application, MSMAIL32.EXE.
3. When starting a mail session, Mail makes use of information in the Windows NT registry to determine startup information, such as the share name serving as a post office for the user logging in. This registry information is configured for the current user when the SQL Setup or SQL Enterprise Manager program is run and the option "Copy SQLMail Configuration from Current User Account" in the Mail Login dialog box is checked.

   This is the Registry key used by SQLMail:

   HKEY_USERS
   \.DEFAULT
   \Software
   \Microsoft
   \Mail

   If this Registry key is not set up properly, ensure that you are able to start a mail session using the account you are currently logged in under, and then rerun SQL Setup or SQL Enterprise Manager, ensuring that the "Copy SQLMail Configuration from Current User Account" is checked.

4. Ensure that the SQL Server service has RWCD access to the post office share. If the SQL Server is running under the LocalSystem account, this requires the post office to grant these rights to EVERYONE if the SQL Server is on the same domain as the post office server, or GUEST if it is not.

   Access to the file share can be tested by using the xp_cmdshell extended stored procedure. For instance, the following commands can be used to test read and write access to the server:

   xp_cmdshell "dir \\mailserver\poshare"
   

   and

   xp_cmdshell "echo 'hello' > \\mailserver\poshare\test.txt"
   

   Note If the post office share is on an NTFS drive, permissions must be set for both the network share and the file system.

5. If the post office resides on a computer running Windows NT Server version 3.5 or later, and if the MSSQLServer service on a separate Mail client computer runs under the LocalSystem account, ensure that the post office share has been added to the list of shares that can be accessed by services running under LocalSystem. This can be done by adding the share name to the Registry key on the Windows NT computer sharing the post office:

   \\HKEY_LOCAL_MACHINE
   \System
   \CurrentControlSet
   \Services
   \LanmanServer
   \Parameters

   Name: NullSessionShares
   Type: REG_MULTI_SZ
   Data: COMCFG

   A Registry key can be added by using the Registry Editor (REGEDT32.EXE). For information, see the online Help for the Registry Editor. You can also set the SQL Server service (MSSQLServer) to run under a Windows NT user account, which does not require the NullSessionShares Registry entry.