SQL Server Agent does not start after setting password to sa
Everything worked fine since long ago. MSSQLSERVER and SQLSERVERAGENT services started automatically loged on as Local System
until I set password for sa. Now after setting password for sa SQL Server works normally, but SQLSERVERAGENT does not start.
I get the message
"SQLSERVERAGENT service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service."
What should be changed to make it work again?
Thr errors from sqlagent.out:
2015-08-11 17:54:31 - ! [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
2015-08-11 17:54:31 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
until I set password for sa. Now after setting password for sa SQL Server works normally, but SQLSERVERAGENT does not start.
I get the message
"SQLSERVERAGENT service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service."
What should be changed to make it work again?
Thr errors from sqlagent.out:
2015-08-11 17:54:31 - ! [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
2015-08-11 17:54:31 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
Vitor Montalvão
Did you change the SQL Server Agent service account or still Local System?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
I presume likewise that you then also need to change the SQL agent startup settings:
https://msdn.microsoft.com/en-us/library/ms365941.aspx
https://msdn.microsoft.com/en-us/library/ms365941.aspx
To change the password used by the SQL Server Agent service
Click the Start button, point to All Programs, point to Microsoft SQL Server 2016, point to Configuration Tools, and then click SQL Server Configuration Manager.
In SQL Server Configuration Manager, click SQL Server Services.
In the details pane, right-click SQL Server Agent (<instancename>), and then click Properties.
In the SQL Server Agent (<instancename>) Properties dialog box, on the Log On tab, for the account listed in the Account Name box, type the new password in the Password and Confirm Password boxes, and then click OK.
On a stand-alone instance of SQL Server, the password takes effect immediately, without restarting SQL Server. On a clustered instance, SQL Server might take the SQL Server resource offline, and require a restart.
ASKER
Sorry, I am still working with old 2000 SQL Server.
As for SQL Server Agent service account, it is still Local System
As for SQL Server Agent service account, it is still Local System
Did you delete some logins, like for example BUILTIN\Administrators?
If so, add it back and give it System Administrator role.
If so, add it back and give it System Administrator role.
ASKER
I did not delete any logins and did not reconfigured anything. I only set password for sa.
Ok. Just want to confirm that.
Do you have any error entry in Event Viewer or SQL Server log?
Do you have any error entry in Event Viewer or SQL Server log?
ASKER
BUILTIN\Administrators is in the list of SQL logins. It has Granted access. It has System Administrators Server Role
here is the version for SQL 2000 (which is quite similar) on how to set the changed password on the sql agent:
https://technet.microsoft.com/en-us/library/Aa197921%28v=SQL.80%29.aspx
https://technet.microsoft.com/en-us/library/Aa197921%28v=SQL.80%29.aspx
ASKER
Error from Event Viewer/Application:
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
Error from SQLAGENT.OUT file
2015-08-12 17:17:20 - ! [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
2015-08-12 17:17:20 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
Error from SQLAGENT.OUT file
2015-08-12 17:17:20 - ! [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000]
2015-08-12 17:17:20 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
Check SQL Server connection from SQL Server Agent –> Properties.
ASKER
I am not sure where to get SQL Server Agent –> Properties in SQL 2000.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help! I am ashamed not to find it myself-:( Now it works!
Why didn't you chose Pillai's comment as solution and Guy's as assisted? After all Pillai's the one that bring the solution. Guy just pointed you how to get there.
ASKER
Sorry, it's my fault :-(
If you want I can unaccept the solution for this question so you can split the points.
Dear Vitor,
after reviewing the full question history, I think your comment is not "appropriate".
I had posted (as first) a first info where/how, though ignoring the version, I got the "wrong one".
then, after some (in my opinion) irrelevant posts, I posted another tech article on how to do it (though that one does not contain a screenshot), and only after the asker indicates that still he could not find the screen (though the screenshot of Pillai is the right one), I posted the last screenshot on how to find it.
so, the points "redistributed" accordingly would be like 75%-25% ...
just my 3c
a3
after reviewing the full question history, I think your comment is not "appropriate".
I had posted (as first) a first info where/how, though ignoring the version, I got the "wrong one".
then, after some (in my opinion) irrelevant posts, I posted another tech article on how to do it (though that one does not contain a screenshot), and only after the asker indicates that still he could not find the screen (though the screenshot of Pillai is the right one), I posted the last screenshot on how to find it.
so, the points "redistributed" accordingly would be like 75%-25% ...
just my 3c
a3
Many apologizes Guy,
I didn't check the article you sent previously and that makes my comment about points distribuition unecessary. So, maybe the OP also failed to check the article and Pillai's comments sounds to be the solution since it was the OP followed but it was only a reminder.
I didn't check the article you sent previously and that makes my comment about points distribuition unecessary. So, maybe the OP also failed to check the article and Pillai's comments sounds to be the solution since it was the OP followed but it was only a reminder.
Only eshakhovskey can confirm what was the solution that was applied.
If I did not miss anything, there is a difference between what I was proposing and what Guy was proposing. I mentioned about the settings in tab "Connection" ( marked in Green ) where as the link provided by Guy was mentioning about settings in tab "General" ( marked in Red) in the following picture
Adding a comment related to the picture that I posted earlier (link ) :- "Beginning with SQL Server 2005, SQL Server Agent does not support SQL Server Authentication. This option is available only when you administer an earlier version of SQL Server."
If I did not miss anything, there is a difference between what I was proposing and what Guy was proposing. I mentioned about the settings in tab "Connection" ( marked in Green ) where as the link provided by Guy was mentioning about settings in tab "General" ( marked in Red) in the following picture
Adding a comment related to the picture that I posted earlier (link ) :- "Beginning with SQL Server 2005, SQL Server Agent does not support SQL Server Authentication. This option is available only when you administer an earlier version of SQL Server."