none
Access denied starting SQL Server agent RRS feed

  • Question

  • I've been struggling to figure out how to set the "Log on as" login for a SQL Server agent that isn't a member of the Administrators group.

    Is this even possible?  No matter what policy settings I assign the the user, I get an access denied error when I try to start the SQL Server Agent service.  If that user is part of the administrators group, there are no errors.

    I have no problems with other services and "log on as" logins...

    Monday, March 5, 2007 9:32 PM

Answers

All replies

  • As I recall, the SQL Server Agent service must act as a member of the local admins group.
    Monday, March 5, 2007 9:45 PM
    Moderator
  • Read Selecting an Account for SQL Server Agent Service  in BOL

    From BOL

     

    You select an account for the SQL Server Agent service by using SQL Server Configuration Manager where you can choose from the following options:

    • This account. Lets you specify the Windows domain account in which the SQL Server Agent service runs. The domain account that you specify must be a member of the sysadmin fixed server role on the local instance of SQL Server. We recommend that the Windows user account you choose is not a member of the Windows Administrators group.

    • Local System account. The built-in local system administrator account. It is a member of the Administrators group on the local computer, and is therefore a member of the sysadmin role within SQL Server.

      The Local System account is provided for backward compatibility. For improved security, use a Windows domain account with the permissions listed in the following section.

    Madhu

    Tuesday, March 6, 2007 5:02 AM
    Moderator
  •  Madhu K Nair wrote:

    Read Selecting an Account for SQL Server Agent Service  in BOL

    From BOL

     

    You select an account for the SQL Server Agent service by using SQL Server Configuration Manager where you can choose from the following options:

    • This account. Lets you specify the Windows domain account in which the SQL Server Agent service runs. The domain account that you specify must be a member of the sysadmin fixed server role on the local instance of SQL Server. We recommend that the Windows user account you choose is not a member of the Windows Administrators group.

    • Local System account. The built-in local system administrator account. It is a member of the Administrators group on the local computer, and is therefore a member of the sysadmin role within SQL Server.

      The Local System account is provided for backward compatibility. For improved security, use a Windows domain account with the permissions listed in the following section.

    Madhu

    Thanks for the link, it re-enforces that I don't want the Agent to use a Windows login in the Adminstrators group.  But, I have two installations of SQL Server 2005 (Express, and non-express) and "SQL Agent" doesn't appear in the list of services in SQL Server Configuration Manager (I only get "SQL Server" and "SQL Server Browser").  I'm not in a clustered environment and not in a domain environment; the detail about Windows accounts in that BOL article doesn't help...

    Tuesday, March 6, 2007 3:18 PM
  •  Arnie Rowland wrote:
    As I recall, the SQL Server Agent service must act as a member of the local admins group.
    The links that Madhu has referenced suggest this is counter to recommended practice...
    Tuesday, March 6, 2007 3:40 PM
  • When you read these sources, you will find that SQL Agent needs a high level of local access and permissions.

    Definintely should have a domain account (if in a domain), and be limited as to network resources. Not a domain admin. Probably not a local admin -however, that is often chosen for ease of use. It is best to 'hand craft' an account with just the right type of permissions for Agent to do its job. If a job needs access to resources, then either Agent will need those permissions, or you will set up an Agent Proxy account. Hope these help.

    Configuration -Service Accounts, SQL Server or SQL Server Agent service account
    http://support.microsoft.com/kb/283811/en-us
    http://msdn2.microsoft.com/en-us/library/ms143691.aspx

    Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
    http://msdn2.microsoft.com/en-us/library/ms191543.aspx
    http://support.microsoft.com/kb/907557

     

    Tuesday, March 6, 2007 4:40 PM
    Moderator
  •  Arnie Rowland wrote:

    Probably not a local admin -however, that is often chosen for ease of use. It is best to 'hand craft' an account with just the right type of permissions for Agent to do its job. If a job needs access to resources, then either Agent will need those permissions, or you will set up an Agent Proxy account. Hope these help.

    Configuration -Service Accounts, SQL Server or SQL Server Agent service account
    http://support.microsoft.com/kb/283811/en-us
    http://msdn2.microsoft.com/en-us/library/ms143691.aspx

    Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
    http://msdn2.microsoft.com/en-us/library/ms191543.aspx
    http://support.microsoft.com/kb/907557

    I'm finding that I cannot even get the Agent service to start by using hand-crafted log on as account that isn't an administrator (which is what I would prefer to do).  Nothing in any of the references regarding configuration of the SQL Agent service (including the ones posted in this thread) have provided any information that has allowed me to create a user account, that isn't in the Administrators Group, with the SQL Agent service (and get it to start).
    Tuesday, March 6, 2007 5:59 PM
  •   But, I have two installations of SQL Server 2005 (Express, and non-express) and "SQL Agent" doesn't appear in the list of services in SQL Server Configuration Manager (I only get "SQL Server" and "SQL Server Browser").

    SQL Server 2005 Express donot have SQLServerAgent Service...

    Refer  : http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

    the other installation is of SQL 2000 or SQL 2005 ? i doubt its SQL Server 2000

     

    Madhu

    Wednesday, March 7, 2007 5:13 AM
    Moderator