Post reply

Problem starting SQLServerAgent

  • April 2, 2005 at 9:57 pm

    #87179

    Hi all,

    On SQL server 2000 any time I try to start SQLServerAgent I get the following errors:

    The SQl server Agent service on local computer started and stop. Some services stopped automatically if they have no work to do,

     

    For example, the performances logs and alerts service. 

    In the evnet viewer I have :

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

    Could some one please let me know how to fix this.

     

    Thanks for any help.

    Abrahim

     

     

     

  • April 3, 2005 at 9:43 am

    #550255

    is agent running as domain account or as local system account? sounds like the account is not valid. make sure the account exists, and has rights, then restart agent within context of that valid account.

  • April 3, 2005 at 1:27 pm

    #550257

    Yes,

     

    That was the problem. I changed domain account to local system account.

     

    Thanks,

     

    Abrahim

  • April 4, 2005 at 2:16 am

    #550277

    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)

    This explains it all. You have to add your SQL Server Agent domain account as a login to the SQL Server with the SysAdmin role.

    Also it is very important to let the account have 'logon as service' rights.

  • April 4, 2005 at 9:41 am

    #550365

    Is there any link to the security and user setup for SQL 2000 out there?

    Thanks in advance,

    Abrahim

  • April 4, 2005 at 9:49 am

    #550367

    http://www.microsoft.com/technet/community/chats/trans/sql/sql0723.mspx

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx

     

    Google is your friend! ( security guidelines sql server 2000 site:microsoft.com )

    Good luck!

  • April 4, 2005 at 10:42 am

    #550376

    Thanks,

    Abrahim

  • April 4, 2005 at 5:27 pm

    #550431

    Hi.

    I have if you are using domain account or local account to start our services that should not be a prbolem to start the SQL Agent servives.

    In the Past when i have this problem i resove in the following way:

    I get into the SQL Enterprice, go to the Managamente Folder, then to the SQL Server Agent, then i clcik right button and select properties. What a didi then was define a valid path for the errorLog file in the General Tab. Then i exit this option. And when i start the services it started.

    Also i defind a New Job, in order to run in the server like a test.

     

    It works.

     

    Regards

    JLSS

  • April 4, 2005 at 10:31 pm

    #550438

    Of course is having the valid path's with the proper permissions set very important!

    The accounts running SQL Server service and SQL Agent service need to have full controll rights in the MSSQL$<INSTANCENAME> directory's. If the dump's are written to an other directory they need to have full controll either. If the errorlog files are written to an other directory they need to have full controll also.

    Securing SQL Server is not very ease done if you did not have read the security guidelines on the Microsoft website.

    If I have time this day, I will try to make a full list's of things to do.

  • April 5, 2005 at 9:56 am

    #550584

    Thank you all for the wonderful feedback!!

    I can't wait to see a simple documentation about how to secure the SQL server 2000.

    Best regards,

    Abrahim

  • April 5, 2005 at 10:43 pm

    #550678

    Ok, there it is:

    Make two domain accounts (1 for SQL Server service and 1 for SQL Agent service)

    • In the policy make sure the following things are set for those 2 domain accounts

    • Act as part of the operating system
    • Adjust memory quotas for a process
    • Bypass traverse checking (only the server service account)
    • Lock pages in memory (only the server service account)
    • Log on as a batch job
    • Log on as a service
    • Replace a process level token
    • HKLM\Software\Microsoft\MSDTC - Full rights
    • HKLM\Software\Microsoft\MSSQLServer - Full rights
    • HKLM\Software\Microsoft\Microsoft SQL Server - Full rights
    • Make sure the accounts have List access on root level of all the disks where there are files for SQL Server and that those permissions are inhereted to the subdirectory's
    • Full Controll access to the MSSQL$INSTANCE sub-directory's on every disk you have for your instance
    • If you have a separete dump directory for dumping to disk; Full Control for those accounts in that directory.
    • Last but not least: Not a member of any group on your server!
    • Add these accounts to the instance as login with SA rights

  • I use different domain accounts for SQL access and Server access, I use 1 for installing software (member of local administrators - therefor no access to the instance because of bullet below) and 1 group for SA access to the instance. Only DBA people are member of that group, and not with their normal working domain account. It is a second account for the DBA's.
  • Remove 'Builtin Administrators' from the login's for the instance
  • Add login 'NT AUTHORITY\System' to the instance with SA rights
  • Distributed Transaction Coordinator - Log on as 'NT AUTHORITY\NetworkService'
  • If you can, use only Windows authentication in the instance, If you use applications from third parties, this will be a problem most of the time, then use SQL authentication
  • Use a strong SA password, many characters, not a word, use characters like !#$^&*(), use numbers, use several capital characters and a lot of characters in total (like 20 or so)
  • Use a domain group as a login for DBA's. Make all DBA's, that need those rights, member of that group. Let the group have those rights that is needed for usual administration (NOT SA), strong passwords are of course advised for normal accounts
  • Use a second domain group as a login for DBA's. Make the second domain account for each DBA, member of that group, and give SA rights. Make sure there is a STRONG password for those admin accounts. (use the wonderfull runas function in XP for administration with SA rights when that is needed, this works very well for Enterprise Manager, SQL Profiler and Query Analizer and other tools)

    • I think this is the most important, when I think of more I will add it to the list. Any comments are welcome, if needed I will adjust this.

  • April 8, 2005 at 7:51 am

    #551498

    I use local system account, but it give me start and stop.  how to resolve this?

  • April 8, 2005 at 7:53 am

    #551499

    What does the Agent errorlog say?

  • September 27, 2006 at 11:25 am

    #662801

    Hi..

     I am having similar problem with SQL Server 2005 instance running on a 64-bit environment. 

     The Agnet is set to run under an NT account (same as SQL Service), which belongs to sysadmin group on SQL Server as well as Administrators group on the box.

     To make it even more confusing, it was running this morning.  I was changing memory configuration on the instance and had to reboot the service.  The agent would not come back up.

     Any advices would be greatly appreciated!

    Anastasia

  • September 28, 2006 at 12:12 pm

    #663107

    Please take a look at the errorlog files and post the error...

    Without error messages no one can answer what is wrong.

    • Viewing 15 posts - 1 through 14 (of 14 total)

    You must be logged in to reply to this topic. Login to reply