Troubleshooting SQL Mail Dwayne Lanclos and Joseph Pilov Support Professionals Developer Support Mic

1
Troubleshooting SQL Mail Dwayne Lanclos and
Joseph PilovSupport ProfessionalsDeveloper
SupportMicrosoft Corporation
2
Agenda

• Overview of SQL Mail (focus on Microsoft SQL
  Server versions 2000 and 7.0)
• SQL Mail setup requirements
• Basic troubleshooting topics
• Advanced troubleshooting topics

3
Overview of SQL Mail Services

• Provides a way to send and receive e-mail
  messages in Microsoft SQL Server
• Establishes a client MAPI connection to a mail
  server
• Two independent services available
• SQL Mail mail stored procedures
• SQL Agent Mail notifications and alerts

4
Overview of SQL Mail Services (2)

• SQL Mail stored procedures
• xp_startmail, sp_processmail, xp_sendmail,
  xp_readmail, xp_deletemail, xp_findnextmsg,
  xp_stopmail
• SQL Mail stored procedures can
• Manipulate data, process queries received by
  e-mail, return the result set by creating a reply
  e-mail
• Be used from either a trigger or a stored
  procedure

5
Overview of SQL Mail Services (3)

• SQL Agent Mail independent from SQL Mail
• SQL Agent Mail only sends messages
• SQL Agent Mail can be used when
• Alert is triggered
• A scheduled task reports status of completion

6
Terminology

• Mail server
• Post Office Protocol (POP3) manages incoming
  mail)
• Simple Mail Transfer Protocol (SMTP) manages
  outgoing mail)
• Mail client
• Mail Application Programming Interface (MAPI )

7
Terminology (2)

• Mail profile
• Settings, Control Panel, Mail, Show Profiles
• Default mail client
• Settings, Control Panel, Internet Options,
  Programs tab, E-mail
• Internet Explorer, Internet Options, Programs
  tab, E-mail
• C/W Corporate or Workgroup (Outlook option)
• IMO Internet Mail Only (Outlook option)

8
SQL Mail Setup Requirements

• Mail server
• Simple MAPI (for 7.0) or Extended MAPI (for 2000)
  mail client
• A Windows NT or Windows 2000 user account
• Domain account (needed for Exchange server)
• Local or domain account for POP3/SMTP server
• Mail profile
• Control Panel, Mail, Show Profiles

9
SQL Mail Setup Checklist

• Install mail client.
• Log on to server using the account configured to
  start SQL Server and/or SQL Server Agent.
• Configure one or more mail profiles.
• Check that mail client used for profile is
  default mail client.
• Send and/or receive mail using mail client.
• Provide mail profile to SQL Mail.
• Start SQL Mail and test sending e-mail.
• Provide mail profile to SQL Agent.
• Start SQL Agent and test sending e-mail to
  operator.

10
Is the Problem with the Mail Server?

• When having a SQL Mail problem, confirm if the
  mail server supports SQL Mail
• Supported servers are Microsoft Exchange Server
  and POP3/SMTP
• Third-party mail server running in native mode is
  not supported, unless it is used as a POP3/SMTP
  server
• Ensure 100 connectivity for incoming mail

11
Is the Problem with the Mail Client?

• Must have
• Outlook 2000 (SQL Server 7.0 or 2000)
• Outlook 2002 (SQL Server 2000)
• Does not work with POP3/SMTP and SQL Mail
• Not supported
• Outlook Express no mail profile created
• Any non-MAPIcompliant client

12
Is the Problem with the Mail Client? (2)

• Configure Microsoft Outlook with C/W option
• Works with Exchange or POP3/SMTP
• IMO option wont work with SQL Mail
• C/W and IMO options not available in Outlook
  2002
• Verify mail client is the default e-mail client
  in Internet Explorer
• Tools menu, click Internet Options, click
  Programs tab, select from E-mail list

13
Is the Problem with the Startup Service Accounts?

• SQL Server and SQL Server Agent startup accounts
  must be Windows accounts different from Local
  System
• For Microsoft Exchange Server
• Use a Windows NT or Windows 2000 domain user
  account
• For any POP3/SMTP server
• Can use a Local or a Domain Windows NT or Windows
  2000 user account
• Mail profile is associated with the startup
  account

14
Is the Mail Profile Configured Correctly?

• Must log on with the service startup account to
  configure mail profile!
• Avoid using special characters such as hyphen,
  pound sign, period in the profile name
• Profile name must be fewer than 32 characters
• NT account needs to have a mailbox on the
  Exchange server (Exchange servers only)
• Configuring Mail Profiles (Books Online)

15
Can You Send and/or Receive E-mail from Mail
Client?

• Test the configured profile and mail connectivity
  by sending and/or receiving mail through mail
  client
• Ensure that no pop-up dialog boxes appear that
  require user interaction at any time
• Logon and log off dialog screens
• OK and Cancel dialog screens
• Sending-mail-on-your-behalf security screens

16
Is SQL Mail in Cluster Environment?

• MAPI is not cluster-aware and cannot fail over
  not supported on Microsoft Cluster Services
  (MSCS)
• MSCS Windows NT 4.0 Enterprise Edition, Windows
  2000 Advanced Edition, or Windows 2000
  Datacenter Server
• SQL Server 7.0 and 2000 Enterprise Edition

17
Is SQL Mail in Cluster Environment? (2)

• Can still configure SQL Mail on a single node of
  cluster
• Might be able to set up on both nodes, if .pst
  file is stored on the shared disk
• Still not supported or tested, and is not
  guaranteed to work

18
Possible Problems

• Mail stuck in Outbox (or Inbox)
• SQL Mail hangs
• POP3 servers
• Third-party mail clients
• Non-domain account used to start SQL Server or
  SQL Agent

19
Possible Problems (2)

• Bad profile
• Test button only tests for a valid profile (no
  guarantee that SQL Mail will use it)
• Microsoft Outlook is not default mail client

20
Mail Stuck in Outbox (or Inbox)

• Mail sent message appears when executing
  xp_sendmail
• Outgoing mail remains in Outbox (or Inbox) unless
  e-mail client is kept open
• Usually because of an incorrect version of
  Mapi32.dll
• Default behavior for Outlook XP when used with
  POP3/SMTP server

21
MAPI-Related Problems

• Incorrect Mapi32.dll could cause outgoing mail to
  stay in Outbox and not be sent until mail client
  is opened
• Fixed in Windows NT 4.0 SP3 (but not later
  service packs)
• Mapi32.dll continuously changes in size and
  functionality
• In some cases current (working) Mapi32.dll is
  replaced by installation of new software (look
  for Mapi32x.dll)

22
POP3 Servers

• Every mail server supports SMTP and most support
  (or can be configured to support) POP3
• You probably use POP3 to retrieve e-mail at home
• If mail client cannot connect to POP3 server,
  user gets a pop-up dialog box this will cause
  SQL Mail to hang

23
POP3 Servers (2)

• If SQL Mail cannot connect to send notification
  mail after a job is completed, it will hang and
  no more scheduled jobs will be run
• POP3 server can be installed on the SQL Server
  computer to improve connectivity

24
SQL Mail Hangs

• Frequently seen when using POP3 server and
  connection cannot be established hang does not
  go away when connection is re-established
• Any dialog box that appears when trying to send
  mail will cause a SQL Mail hang

25
SQL Mail Hangs (2)

• New security features for Outlook will cause a
  security dialog box to appear
• Start SQL Server from command prompt (sqlservr
  c) to test if a pop-up dialog box appears when
  running xp_sendmail

26
Third-party Mail Clients

• Examples Lotus Notes, Novell GroupWise, and
  others
• No guarantee that third-party clients will work
  with SQL Mail not supported by Microsoft
• Also not supported is an Outlook mail profile
  using a mail service other than Exchange or
  Internet Mail (for example, the Microsoft
  Outlook support for Lotus ccMail service)

27
Known Issues with Outlook

• Ships with Office 2000, Office XP, and also as a
  standalone product
• Works with Exchange and POP3 mail servers
• Outlook 2000 has IMO option that will not work
  with SQL Mail
• Must install Outlook 2000 with C/W option
• Security patch for Outlook 2000 will cause SQL
  Mail 7.0 to hang
• Outlook 2002 (ships with Office XP) will only
  work when used with an Exchange server

28
Known Issues with Outlook Express

• Ships with Internet Explorer 4.x and 5.x
• Works only with POP3 servers
• Does not create a mail profile
• Internet Explorer 5.0 installation makes Outlook
  Express default mail client (could conflict with
  SQL Mail startup)
• Mapi32.dll stub installed with Outlook Express
  could replace an otherwise working Mapi32.dll
  file and rename it Mapi32x.dll

29
(No Transcript)