I am trying to follow best practices for Sharepoint 2010 installation and SQL Server Agent will not start. My first reference is:
Installing SharePoint 2010 using Least 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:
Selecting an Account for the SQL Server Agent Service
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.
Best Answer
This has nothing to do with SharePoint and everything to do with SQL Server.
The account under which the SQL Server Agent service runs effectively needs full sysadmin access to the SQL Server instance it operates on, because its role is to perform jobs on the instance and its databases.
If you select the Agent's service account using SQL Server Configuration Manager, the access and privileges are granted automatically (as well as the required "log on as a service" Windows right).
If you select it by manually editing the service's properties, you will need to grant them manually.
The recommended way to select the Agent's service account is via the Configuration Manager.