Monday, April 7, 2008

MSSQLSERVER & SQLSERVERAGENT (MS SQL 2000) failed after changing the password in active directory

PC must change the password periodically to maintain security, though you still have choice to not to do so if you work alone in default Windows domain. But when it comes to active directory, most MIS guys enforce user to change it through the AD management tools. Thus those softwares in your PC that ties to this AD account for login should be smart enough to changed accordingly.

But in MSSQL 2000, this is not the case. After changing the new AD password, the MSSQLSERVER & SQLSERVERAGENT will not be updated and it will give you error message (if you start it from SQL Server Enterprise Manager: Server Group->[Instance]->Management->SQL Server Agent):
"An error 1609 - (The service did not start due to a logon failure) occurred while performing this service operation on the SQLServerAgent service."

If you start it from Services.msc(Go to command prompt, type: "services.msc"), you will get the same logon error:

Due to the login account has changed, now MSSQL and SQL Server Agent cannot identify you correctly anymore, since the password it keeps is the one you saved last time. You must change the password to let it works.
1) Right-click the particular service (MSSQLSERVER or SQLSERVERAGENT).
2) Choose "Properties"
3) Select "Log On" tab.
4) Change the new AD password as shown, that is!


Anonymous said...

Is there no way to work around the need to manually enter the changed AD password? This seems like a real problem that will occur each tiem the AD password changes.

gary said...

I think I need to google for more. :) I am not sure have MS published it as KB, but I am using MSSQL 2000 SP4 already???

Viney said...

Thanks man, you are a legend