SQL cannot start because of a logon issue

[juanfd]

Hello, we had a leak in security here in my company and we had to change the Administrator password for all the servers as well as for the domain.
The problem arose when the next day after changing the pwd, we noticed that the sqlagent job that used to do a disk backup of the databases didn't run...
we checked out and the sqlagent was down, so we tried to start it and a dialog saying that "the sqlagent cannot start due to a logon issue" showed up.
At this point we tried to change the pwd as well as the user in the server properties but then it shows up a message saying that no connection could be established and something about a SSPI error.

Any help is greatly appreciated

 

[mrdenny]

This is comming up because the SQL Server can't authinticate it self against the domain. This is because you just changed the password of the account that the SQL Service is using.

Create a new account on the domain (give it domain admin rights for the moment, we'll clear that up in a minute, lets get the SQL Server back online first). Call it SQLServer or SQLService or something like that. (I'll use SQLService as my example.)

On the SQL Server(s), open up the Service app in the Admin Tools menu. Find the MSSQLSERVER and SQLSERVERAGENT. Set both services to startup using the SQLService account you just created.

Restart the MSSQLSERVER service, then the SQLSERVERAGENT service. Your SQL Server should now startup. Now that your SQL Server is working correctly again, lets restrict the access. Odds are that your SQL Server doesn't need to be a domain admin (no services should ever be run as the Domain Admin account, or with Domain Admin rights unless needed, which is almost never).

On your SQL Server(s) open the Local Security Policy app in the Admin Tools menu. Grant the SQLService account the "Log on as a service" right, and the "Act as part of the Operating System" right. Now remove the Domain Admin rights from the SQLService account.

The last thing to do is to restart the SQL Server and SQL Agent Services one last time, so that the SQL Server will be running with it's new permissions.

If you have any questions, or issues let me know.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

And if mrdenny's solution solves your problem, make sure you let him know AND reward him with a star.

How do you reward a star? At the bottom left of the post is "Thank ..... for this valuable post!" It has a pink/purple star next to it. Click on that link and then follow the directions in the popup window.

-SQLBill