Microsoft KB Archive/259710

From BetaArchive Wiki
Knowledge Base


PRB: SQL Server Agent Fails to Start on Windows 9x When You Change the sa Password

Article ID: 259710

Article Last Modified on 10/17/2003



APPLIES TO

  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition



This article was previously published under Q259710

SYMPTOMS

Changing the system administrator (sa) password from a blank to any other value, for security purposes, on a Microsoft Windows 95-based computer or a Microsoft Windows 98-based computer, might prevent the SQL Server Agent from starting. The SQLAgent.out file found in the C:\Mssql7\Log directory appears as follows:

Sqlagent.out
-------------------
4/7/00 1:36:14 AM - ? [129] SQLServerAgent starting under Windows 9x service control

4/7/00 1:36:16 AM - ! [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]

4/7/00 1:36:16 AM - ! [000] Unable to connect to server; SQLServerAgent cannot start

4/7/00 1:36:16 AM - ? [098] SQLServerAgent terminated (normally)
                

If you change the sa password back to a blank, the SQL Server Agent works again. This problem occurs on the golden, SP1 and SP2 release of both the Desktop Edition and the Microsoft Data Engine (MSDE).

CAUSE

The information needed for SQL Server Agent to start is obtained from the registry; however, the information does not exist in the registry until after the sa password is changed by the SQL Server Enterprise Manager (SEM). This method is used on Microsoft Windows 9x computers, because they do not support trusted-connections, which is the default for Microsoft SQL Server 7.0 on both Microsoft Windows NT 4.0-based computers and Microsoft Windows 2000-based computers.

WORKAROUND

To work around this problem:

  1. In the SQL Enterprise Manager (SEM), locate the Management folder, and then select the SQL Server Agent.
  2. Right-click the SQL Server Agent, and then click Properties.
  3. In the Properties dialog box, click the Connection tab.
  4. In the Connection dialog box, specify the login or password that you want to use. Although you can change the sa password programmatically with T-SQL statements, you must manually use the SQL Enterprise Manager to adjust the sa password for the SQL Server Agent on the target computer. You can use the SQL Enterprise Manager from another computer for this management if performing this task on the target computer is not practical.


Keywords: kbbug kbprb KB259710