Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/310822

From BetaArchive Wiki
Knowledge Base


Article ID: 310822

Article Last Modified on 12/10/2002



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition



This article was previously published under Q310822

BUG #: 57093 (SQLBUG_70)

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.
                

CAUSE

You must use the virtual server name to access a clustered SQL Server. Because the default named pipe is used instead of the virtual server name, xp_sendmail cannot find SQL Server; therefore, the query fails with the "Unable to login to SQL Server" error message.

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:

    1. 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
                              
    2. 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



Additional query words: sqlmail 17969 cluster NT authentication mail,sql mail

Keywords: kbbug kbpending KB310822