July 5, 2009 at 6:40 am
Hi
I am getting below error.. Could anyone please suggest the resolution?
Background: Accidentally service account was dropped and then recreated. So we did restart the SQL Services. Now we get below errors:
SQL Agent is now not starting for following error after restart of SQL
Service:
SQLServerAgent could not be started (reason: SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role).
Action taken:
1. Made sure service account is local admin/has rights to start services (Other SQL Services are fine with same account)
2. Made sure to delete and create service account in every possible place (like os group/group policy to local page etc) and tried both with services.msc and surface area configuration to start service
3. Log does not give any specific error.
I understand a reboot will probably resolve it. But is there any way to start it without a reboot?
Thanks
UC
July 6, 2009 at 8:41 am
Did you uses Surface Area Configuration to re-assign the account to the SQLServerAgent Service? It sounds like you have mismatched SID's which is why you are getting unknown. I'd try using SAC to change the account to LocalSystem, then change it back to the proper domain account.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 6, 2009 at 8:55 am
What is the meaning of mismatched SID's Jack?
-MJ
Please do not print mails and docx unless it is absolutely necessary. Spread environmental awareness.
July 6, 2009 at 9:03 am
A SID is a GUID that AD uses to uniquely identify an account (they are also used in SQL Server for SQL Accounts). When you assign an account to one of the SQL Server services using the SAC, that account is placed in the appropriate groups on the SQL Server server and the SID is what is stored. In this case, since the service account was deleted from AD and recreated it got a new SID so as far as the SQL Server is concerned it is a new account and needs to be assigned to the appropriate groups which SAC will do.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 6, 2009 at 9:55 am
To add to Jack's reply.
The SID is how AD identifies an account. Just because you recreated the account with the same name, it's not the same account. AD and SQL see it as a different account since it has a different SID. (that's how it was explained to me anyway).
July 9, 2009 at 3:27 am
I think Jack meant to say SQL Server Configuration Manager (SSCM) instead of Surface Area Configuration. It is important that you change service accounts here and not in the Services applet because SSCM will automatically grant the new service account the rights and permissions it needs to run SQL Server Agent.
John
July 9, 2009 at 3:28 am
Got errors when I posted this and now it's there three times! And it won't let me delete it, either.
July 9, 2009 at 3:28 am
Got errors when I posted this and now it's there three times! And it won't let me delete it, either.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply