none
Cannot start SQL Server Agent with a domain account that DOES successfully start MSSQLSERVER itself

    Question

  • I am trying to follow best practices for Sharepoint 2010 installation and SQL Server Agent will not start. My first reference is:

    http://sharepointgeorge.com/2010/installing-sharepoint-2010-privilege-service-accounts/

    In short, I am using a domain account for starting MSSQLSERVER itself and SQLSERVERAGENT services for both as he suggests (that is, I am using myDomain\SQLsrvcs)


    Here is the error log (SQLAGENT.OUT) for the agent when it fails:


    2011-06-07 15:06:02 - ! [298] SQLServer Error: 15247, User does not have permission to perform this action. [SQLSTATE 42000] (DisableAgentXPs)

    2011-06-07 15:06:02 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_has_server_access', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

    2011-06-07 15:06:02 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_get_startup_info', database 'msdb', schema 'dbo'. [SQLSTATE 42000] 

    2011-06-07 15:06:02 - ! [298] SQLServer Error: 229, The INSERT permission was denied on the object 'syssessions', database 'msdb', schema 'dbo'. [SQLSTATE 42000] 

    2011-06-07 15:06:02 - ! [000] Error creating a new session

    2011-06-07 15:06:02 - ? [098] SQLServerAgent terminated (normally)

    MSSQLSERVER starts fine but the agent fails as above. The advice of "Sharepoint George" does not indicate that the instance of SQL needs a LOGIN (and as I read the "least privilege" message it should not have that).


    Yet, here is another place where advice is given:

    http://msdn.microsoft.com/en-us/library/ms191543.aspx

    In this article, Microsoft states: "The account that the SQL Server Agent service runs as must be a member of the following SQL Server roles - The account must be a member of the sysadmin fixed server role."

    I don't know how to make this domain account a member of the sysadmin role without it first being added as a LOGIN - but that seems to defeat the guidance of the first expert.

    I must be missing something or infering something I should not. Please advise best practice for these SQL service accounts (especially when Sharepoint 2010 will be installed on the same server). Thanks.


    John
    Tuesday, June 07, 2011 9:03 PM

Answers

  • I don't think George specifies one way or another that the SQL Server Service Account should (or should not) be added as a SQL Login. You should add it as a SQL Login, doing so will not give that account any privileged permissions.
    Wahid Saleemi Sr. Consultant, Avanade http://www.wahidsaleemi.com
    Wednesday, June 08, 2011 3:36 AM

All replies

  • I don't think George specifies one way or another that the SQL Server Service Account should (or should not) be added as a SQL Login. You should add it as a SQL Login, doing so will not give that account any privileged permissions.
    Wahid Saleemi Sr. Consultant, Avanade http://www.wahidsaleemi.com
    Wednesday, June 08, 2011 3:36 AM
  • Hi Wahid,

    Thanks for your time on this. To followup:

    I think you are correct - "Sharepoint George" gives very good guidance but omits any mention of need for SQL Login. Clearly, it is not necessary for the domain account to have a SQL login to start SQL service itself because that works fine now; but clearly SQL agent needs more and the 2nd link referenced above states that the SQL agent needs sysadmin role. 

    If I DO add a LOGIN for that this domain account and give it sysadmin role, does this result in violation of "least privilege" ideas put forth by "Sharepoint George"? You suggest above that adding it as a SQL Login will not give that account any privileged permissions but apparently this Login also needs to be granted sysadmin role. 

    Maybe the best "best practice" would be to use 2 different domain accounts:

    • one for SQLAgent having a LOGIN and sysadmin role
    • another domain account for running SQL itself would be as it is now - without a SQL login and therefore without sysadmin role

     As I write this, it occurs to me that past guidelines I've read suggest different domain accounts for each SQL service (George is the first excellent article I've read which suggests one domain account is good for both services). Maybe something new for SQL 2008 or SQL 2008 R2? 

    Thank you for your patience with my followup seeking clarity. 


    John
    Wednesday, June 08, 2011 2:08 PM