1

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.

Community
  • 1
DaniellaMercuryFan
  • 249
  • 1
  • 8
  • 17

1 Answers1

2

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.

Massimo
  • 70,200
  • 57
  • 200
  • 323
  • Hi Massimo - thank you. I've done this using the SQL Configuration Manager (not thru Services.msc) using same domain acct for SQL itself and SQL AGENT. I understand about the need for full sysadmin but unless I add a SQL login for this domain acct, I don't think I can give the domain acct sysadmin fixed server role on my SQL instance. Yet if I DO add a LOGIN for that account and give it sysadmin role, does this result in violation of "least privilege" ideas put forth by "Sharepoint George"? Maybe use 2 different domain accounts with only one for Agent having a LOGIN and sysadmin role? – DaniellaMercuryFan Jun 08 '11 at 13:50
  • @John, I thought my answer was clear enough... the service account you use for SQL Server Agent **NEEDS** access to the database. This **is** part of the "least privilege" idea, as it is a **NEEDED** privilege if you want it to work; SQL Server Agent just **can't** work without that kind of access to the database. – Massimo Jun 08 '11 at 13:55
  • @Massimo-Thank you it was clear and I know now it needs SYSADMIN and therefore HAS to have a LOGIN. I am thinking now that I'll establish domain\SQLserverAgent with a LOGIN (and SYSADMIN role) and another account (e.g. domain\SQLservice) to start SQL itself (with no LOGIN required). The expert "Sharepoint George" says nothing about distinctions between the account requirements of MSSQLSERVER vs. SQLSERVERAGENT. Would really appreciate your confirmation of this before I close this with your answer. Thanks for your time. – DaniellaMercuryFan Jun 08 '11 at 16:58
  • You can use different accounts for SQL Server and the SQL Server Agent; howewer, you're wrong in assuming that the SQL Server service account doesn't need to actually access the database instance... it **does**, for exactly the same reasons as the Agent's service account. – Massimo Jun 08 '11 at 17:24