INF: Troubleshooting SQLMail with Post Offices

• Microsoft SQL Server versions 4.2, 6.0, 6.5

SUMMARY

You can use certain basic troubleshooting techniques 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 a post office, and registry setup problems. SQLMail functionality is documented in the Release Notes help file for version 4.2x, or in "Books Online" for version 6.x. These should be thoroughly reviewed before using SQLMail functionality.

MORE INFORMATION

You should examine the following checklist of items, preferably in the order given:

1. Confirm that a mail session can be started and mail can be sent using the Win32 mail client, MSMAIL32.EXE.



2. With SQL Server 4.2x, SQLMail requires that the SQLServer service is run under the system account (sometimes called the LocalSystem account). You can determine this by opening the Services application in Control Panel, selecting the SQLServer service and clicking the Startup button. In addition, the service must have the option "Allow service to Interact with Desktop" selected.
   
   With SQL Server version 6.x, it is suggested that the MSSQLServer service is run under a user account, however, SQLMail will work when the MSSQLServer is run under the system account and the option "Allow service to Interact with Desktop" is not selected.
   
   IMPORTANT NOTE: A service running under the system account has no user account information associated with it. This will prevent certain network server software from allowing the Windows NT service to access resources. Microsoft LAN Manager user-level security servers and Novell NetWare Servers will not allow a Windows NT service running under the system account to access their file shares.
   
   For additional information on running SQL Server as the system account when the post office resides on a Novel NetWare server, please see the following article in the Microsoft Knowledge Base:
   

   Q126726 : Using SQLMail with a NetWare Post Office




3. Confirm that the SQLMail user name and password are correct. To do this, run SQL Server Setup. Select Set Server Options and click Mail Login. Make sure that the password is not null. If it is null, SQLMail does not start. Before you click Continue, do step number four and five below.



4. If SQL Server is running under a user account, skip to step number seven.



5. When starting a mail session, Mail uses information in the Windows NT registry in order 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. To make this information available to SQL Server, run SQL Server Setup, select Set Server Options, select the Mail Login button, and ensure that the option box "Copy SQLMail Configuration from Current User Account" is selected.
   
   This option will create the following registry key, which is used by SQLMail:
   
   HKEY_USERS\.DEFAULT\Software\Microsoft\Mail
   
   If this registry key is not set up properly, confirm that the ability to start a mail session using the account with which you are currently logged into Windows NT is possible, and re-run SQL Server setup, ensuring that the "Copy SQLMail Configuration from Current User Account" is selected.



6. If the post office resides on a Windows NT version 3.5 (or later) server, you need to add the post office share to the list of shares that can be accessed by services running under the system account. You can do this by adding the share name to the following registry value on the Windows NT computer where the post office is located using the Registry Editor (REGEDT32.EXE):
   
   HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\LanmanServer \Parameters\NullSessionShares
   
   For example, if the post office was running on a computer with the name "NTServer" and the post office directory is shared as "WGPO", the value "WGPO" must be added to the NullSessionShares value.
   
   Because the NullSessionShares value is of the type REG_MULTI_SZ, each share name must be on a separate line from the other share names.
   
   In order for this registry change to take effect, the post office computer must be rebooted.



7. If SQL Server is running under a user account instead of the system account, confirm that step one was performed while logged into Windows NT with the same user account that SQL Server is using.



8. Confirm that the SQL Server service has Change access permissions or RWCD access permissions to the post office share. If the SQL Server is running under the system account, this will require the post office share grant these rights to the Everyone group.
   
   Additionally, if the post office is located on a NTFS partition, Everyone must be granted the same rights as the share permissions on the post office directory, including all subdirectories as well as all existing files in the post office directory.



9. Test SQL Server access permissions on the post office share. You can do this by using the XP_CMDSHELL extended stored procedure, which runs with the same permissions as SQLMail.
   
   You can use the following commands to test read and write access to the server, assuming the post office is located on a computer named "NTServer" and the post office directory is shared as "WGPO":
   

   
         xp_cmdshell "dir \\NTServer\WGPO"
   
         xp_cmdshell "echo 'hello' > \\NTServer\WGPO\test.txt"
    

   If either of the above commands fail, either step three, five, six, or seven has not been completed correctly.



10. Make sure that you have the proper version of MAPI32.DLL. When you install the Exchange client, the MAPI32.DLL file is over 640 KB. The proper version of the file is 16 KB.



11. Access Violation and MAPI Exception error messages appear if you run SQLMail and other MAPI client software or Mail blocking at the same time. For additional information on SQLMail messages being blocked, please see the following article in the Microsoft Knowledge Base:
    

    Q124048 : PRB: Outgoing SQLMail Messages Blocked and Not Sent

Additional query words: sql6 mapi mapi32 mail32 windows nt

Keywords : kbprg SSrvProg SSrvStProc
Version : 4.2 4.21 6.0 6.5
Platform : WINDOWS
Issue type :