SQL Server Performance


Discussion in 'SQL Server 2005 General DBA Questions' started by Akthar, Dec 10, 2007.

  1. Akthar New Member

    Hi guys,
    I am unable to start the SQL Agent Service. When i Start the SQL Server Service with LOCAL SYSTEM ACCOUNT, the agent service Starts. But when i specify a local account or a domain account the agent service return an error
    "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). "
    The domain user i have used has domain admin rights as well as local admin rights.
    Any ideas guys...
  2. simondm New Member

    If you've dropped the BUILTINAdministrators from SQL Sys Admins then giving domain level admin access will not have any effect.
    Add the Domain user you have created in SQL and assign admin permissions.
  3. Akthar New Member

    the builtinadministrators is here , no changes has been made to that.
    However i noticed that if i Start the MSSQL server service with local system account. then the agent starts. if i use any other account to start the MSSQLSERVER service the agent wont start.;(
  4. Adriaan New Member

    You replied "the builtinadministrators is here , no changes has been made to that."
    Please report the server and/or database roles to which the group is assigned. The group being listed does not mean much in itself.
  5. Akthar New Member

    builtinadministrators is in the sysadmin role.
  6. Akthar New Member

    gurus.. advice please
  7. ghemant Moderator

    [quote user="Akthar"]SQLServerAgent must be able to connect to SQLServer as SysAdmin, but '(Unknown)' is not a member of the SysAdmin role)[/quote]
    what surprise me is that it is unable to identify the user name, can you plz confirm that this machine has proper trust relationship with domain!!
  8. Akthar New Member

    Yes the machine is properly connected and trusted on the domain
  9. Akthar New Member

    any help from someone
  10. satya Moderator

    I guess AGENTXPs are not enabled, run:
    sp_configure 'show advanced options', 1;
    sp_configure 'Agent XPs', 1;
    ...and then start the service.
  11. Akthar New Member

    hi Satya,
    i have tried that , but the script generates some errors. But the same script runs fine if the agent is started.
  12. Akthar New Member

    still no luck with that guys
  13. satya Moderator

    What kind of errors, that will help to suggest a way out.
  14. shearn New Member

    I assume we are talking about SQL 2005?
    I dont think this will fix the problem considering that the account you are using is a local admin and that BUILTINAdministrators is a sysadm, but worth checking just in case. . .Ensure that the SQLServer2005SQLAgentUser$servername$instancename local group exists on the server and that this group has a login in SQL Server and is a member of the sysadm server role. Then ensure that the domain account that you are trying to start the SQL agent as is a member of the SQLServer2005SQLAgentUser$servername$instancename local group.
    The SQLServer2005SQLAgentUser$servername$instancename local group is granted various NTFS folder permissions and rights in the local security policy of the server.
    When you use SQL Server Configuration Manager to change service accounts, the account should get added to the group automatically, however I have known this not to happen before.
  15. satya Moderator

    I would ask you to check Security log under Event viewer in order to see why this is failing, as the failure reason will be cited there and also look for SQLAgent log.
  16. Akthar New Member

    as per the error log, it seems that the account used to start the Agent Service depends on the account of the sql server service . There are some permission issues even if i start the Agent with a SYSAdmin Account.
  17. iwan_drago New Member

    I had the same issue: couldn't start SQL Server Agent or read the SQL Server logs from Management Studio after restoring the system databases from a backup.
    I just dropped and recreated the login SQLServer2005SQLAgentUser$servername$instancename with the same grants that it had before and then I was able to start the SQL Server Agent.
    Hope this helps!
  18. Akthar New Member

    i can found this error in the event log
    "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). "
    It seems that if the SQL SERVER service is started with any account , the agent wont start.
  19. satya Moderator

    Have you followed as per Iwan's post above?

Share This Page