Help .. Weird Error

SQLServerCentral is supported by Red Gate Software Ltd.

Home Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Help .. Weird Error

Help .. Weird Error

Rate Topic

Display Mode

Topic Options

Author

Message

Utsab Chattopadhyay

Posted Sunday, July 05, 2009 6:40 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 20, 2009 12:35 PM
Points: 187, Visits: 224

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

Regards
Utsab Chattopadhyay
www.consultdba.com

Post #747339

Jack Corbett

Posted Monday, July 06, 2009 8:41 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 7,314, Visits: 6,859

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #747790

MichaelJasson

Posted Monday, July 06, 2009 8:55 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 14, 2009 10:23 PM
Points: 75, Visits: 137

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.

Post #747796

Jack Corbett

Posted Monday, July 06, 2009 9:03 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 7,314, Visits: 6,859

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.

Post #747801

Tom Schuettke

Posted Monday, July 06, 2009 9:55 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:21 AM
Points: 2,590, Visits: 262

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).

Post #747852

John Mitchell-245523

Posted Thursday, July 09, 2009 3:27 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, October 29, 2009 8:29 AM
Points: 2,101, Visits: 1,747

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

Post #750021

John Mitchell-245523

Posted Thursday, July 09, 2009 3:28 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, October 29, 2009 8:29 AM
Points: 2,101, Visits: 1,747

Got errors when I posted this and now it's there three times! And it won't let me delete it, either.

Post #750022

John Mitchell-245523

Posted Thursday, July 09, 2009 3:28 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, October 29, 2009 8:29 AM
Points: 2,101, Visits: 1,747

Got errors when I posted this and now it's there three times! And it won't let me delete it, either.

Post #750023

« Prev Topic | Next Topic »

Permissions