Post reply

Login Failure for SQL Agent

  • February 19, 2003 at 4:49 am

    #101385

    In order to stop local admin users doing whatever they want in SQL Server on a new installation I have denied access for the Builtin/Administrators login.

    Domain Accounts with local admin rights on the machine have been created for the mssqlservice and mssqlagent services.

    I created SQL logins with sa rights for the mssqlservice and mssqlagent.

    The problem is that the SQL Agent service will not start. The mssql service is okay and will stop and start fine. The SQL agent will only start if I grant the Builtin/Administrators login access. The password is correct I've checked this by logging on the server with it, can't be password problems anyway as it is okay if I grant access to builtin/admins. I've also tried to start the mssqlagent service in Services but it still fails.

    Any ideas??

    Thanks in advance

  • February 19, 2003 at 4:56 am

    #449249

    WHat account is SQLAgent running under. If the account is an Admin and not given access to it's user account then you have denied it.

  • February 19, 2003 at 5:02 am

    #449251

    SQL Agent is running under a domain user account with local admin rights on the machine. I have added it as a login with sys admin rights. I have also changed the Agent account to run under the mssqlservice account as the mssqlservice is okay but it still will not start.

  • February 19, 2003 at 9:46 am

    #449278

    Because you have already denied the access for BUILTIN\Administrators, so any local admin accounts will be denied too.

    Remove BUILTIN\Administrators from your SQL Server logins and grant the local admin account with 'sa' right and see what will happen when starting the Agent Serveic.

  • February 19, 2003 at 10:23 am

    #449284

    I see your point about denying the login as a deny supersedes anything else but this doesn't explain why the MSSQLService account is okay?

    Do you know of any possible problems with completely removing the builtin/admin login?

    Thanks for the reply.

  • February 19, 2003 at 10:25 am

    #449285

    As an aside I did manage to get round it by changing the connection details in the Agent properties to connect with the sa user instead on Windows authentication. But I didn't want to go down this road as I'm uncertain as to maybe causing problems further down the road.

  • February 19, 2003 at 12:36 pm

    #449299

    If you have to run Full Text search service, you have to add 'NT Authority\System' to your SQL Server logins with 'sa' right. You also need add your own NT id with 'sa' to mamage SQL Serevr.

    I removed login 'BUILTIN\Administrators' from all my SQL Servers including Cluster SQL Server too.

  • February 20, 2003 at 6:05 am

    #449350

    i agree with shirley.scott

    removing the 'BUILTIN\Administrators' is really bad from my experiences. Any scheduled backups and other 'automated' sql tasks cannot be performed unless 'BUILTIN\Administrators' is there.

  • February 20, 2003 at 7:49 am

    #449362

    I don't agree with the BOL. As long as you add the cluster service account into SQL Server login with 'sa' right, your cluster server will work fine.

    I don't think the jobs depend on the login 'BUILTIN\Administrators'. The jobs are really depend on who is the job owner. It the job owner is the member of 'BUILTIN\Administrators', remove 'BUILTIN\Administrators' will definitely make the jobs fail. Change the job owner to 'sa' and I believe the jobs will work fine too.

  • February 20, 2003 at 9:09 am

    #449383

    Allen is right... there is a KB article that talks about this:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;263712

    Do note, you can only ever impede administrators. We can still get in, if by no other way than changing your password.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • February 24, 2003 at 10:52 am

    #449600

    builtin/administrators can be removed to deny access to sqlservers for local admins.

    problems would arise if jobs need to do network access on that server, then not only "SA" level access on the local server is necessary ,but have network access too like sqlservice does. The same is the case with clustered servers where "is active" thread cannot work.

    So my two cents on the matter is you can do it , but not in all situations.

Viewing 11 posts - 1 through 10 (of 10 total)

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