BUG: Xp_sendmail with @Query Parameter on a Cluster Generates Error 17969 (310822)

SYMPTOMS

If you use the @query parameter with the xp_sendmail extended stored procedure on a clustered SQL Server server, execution of the xp_sendmail stored procedure may result in error 17969 if all these conditions are true:

Your connection uses Trusted Security and/or Microsoft Windows NT Authentication.
SQL Server is clustered.

The text of the error message is:

Server: Msg 17969, Level 16, State 1, Line 0 Unable to login to SQL Server

The SQL Server error log displays messages similar to:

2027-12-01 17:20:36.50 ods      Starting SQL Mail session...
2027-12-01 17:20:36.56 ods      SQL Mail session started.
2027-12-01 17:20:44.48 logon    Login succeeded for user 'CSDOM\Administrator'. Connection: Trusted.
2027-12-01 17:20:46.47 logon    Login failed for user '\'.
2027-12-01 17:20:55.58 logon    Login succeeded for user 'sa'. Connection: Non-Trusted.
2027-12-01 17:20:56.49 logon    Login succeeded for user 'sa'. Connection: Non-Trusted.

WORKAROUND

To avoid this behavior, you can use any one of these methods:

Create a link for the clustered SQL Server to the nonclustered SQL Server, and then have the nonclustered server handle the xp_sendmail requests. For example:
1. Set up SQL Mail on the SQL Server nonclustered server.
2. Use the sp_addlinkedserver stored procedure to link the SQL Server clustered server to the nonclustered server.
3. Run the xp_sendmail extended stored procedure on the nonclustered server with the @query parameter, and then specify the four part linked server name to extract the data required from the linked table on the clustered server.
Use SQL Standard authentication instead of Microsoft Windows NT integrated authentication.

Create a stored procedure and encapsulate an xp_cmdshell extended stored procedure to call osql to connect to the server with SQL authentication, and then run the xp_sendmail extended stored procedure. For example:

Create a stored procedure to encapsulate xp_sendmail:

   CREATE PROCEDURE dbo.procSendMail
     @querytext varchar(250),
     @recipientstext varchar(250),
     @subjecttext varchar(250)
     -- Include parameters for all the xp_sendmail parameters, that you want to expose
   AS
     -- Temporary buffer for the command
     DECLARE @strTempSQL varchar(500)
     -- Make sure you update your cluster name and password for the sa account in the statement
     SET @strTempSQL = 'C:\Mssql7\Binn\Osql.exe -S MyServerName -U sa -P MyPassword -Q"EXEC master..xp_sendmail @recipients=''' + @recipientstext + ''', @query=''' + @querytext + ''', @subject=''' + @subjecttext + ''', @no_header = ''TRUE''"'
     -- Execute the xp_cmdshell to run the Osql.exe tool, which connects to SQL Server by using standard authentication
     EXEC master..xp_cmdshell @strTempSQL

Use the stored procedure to send mail. For example:

EXEC procSendMail 'SELECT * FROM Northwind..Customers', 'someone@example.com', 'My report'

STATUS

Microsoft has confirmed that this is a problem in SQL Server 7.0.

REFERENCES

The SQL Mail service is not fully supported on a clustered server. For additional information, click the article numbers below to view the articles in the Microsoft Knowledge Base:

298723 BUG: SQL Mail Not Fully Supported for Use in Conjunction with Cluster Virtual SQL Servers

263556 INF: How to Configure SQL Mail

311231 INF: Frequently Asked Questions - SQL Server - SQL Mail

315886 Common SQL Mail Problems