Service App app pool account and content database SPDataAccess role not assigned automatically

Question

We noticed these events in Event viewer:

Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Date: 28.8.2022 15.56.52
Event ID: 3760
Task Category: Database
Level: Critical
Keywords:
User: DOMAIN\SPServiceAppPoolAccount
Computer: frontendserver.domain.com
Description:
SQL Database 'Content_MySite_59' on SQL Server instance 'SPSQL' not found. Additional error information from SQL Server is included below.

Cannot open database "Content_MySite_59" requested by the login. The login failed.
Login failed for user 'DOMAIN\SPServiceAppPoolAccount'.
Event Xml:
<Event xmlns="http://schemas.microsoft.com/win/2004/08/events/event">
<System>
<Provider Name="Microsoft-SharePoint Products-SharePoint Foundation" Guid="{6FB7E0CD-52E7-47DD-997A-241563931FC2}" />
<EventID>3760</EventID>
<Version>16</Version>
<Level>1</Level>
<Task>3</Task>
<Opcode>0</Opcode>
<Keywords>0x2000000000000000</Keywords>
<TimeCreated SystemTime="2022-08-28T12:56:52.931813100Z" />
<EventRecordID>1236246</EventRecordID>
<Correlation ActivityID="{366B5FA0-4763-609E-6C4E-432F1FB6E0D6}" />
<Execution ProcessID="8212" ThreadID="8268" />
<Channel>Application</Channel>
<Computer>frontendserver.domain.com</Computer>
<Security UserID="S-1-5-21-3521595049-301303566-333748410-24594" />
</System>
<EventData>
<Data Name="string0">Content_MySite_59</Data>
<Data Name="string1">SPSQL</Data>
<Data Name="string2">Cannot open database "Content_MySite_59" requested by the login. The login failed.
Login failed for user 'DOMAIN\SPServiceAppPoolAccount'.</Data>
</EventData>
</Event>

So the Service Application pool account DOMAIN\SPServiceAppPoolAccount is trying to open MySite content database Content_MySite_59, but fails because the account doesn't have permissions. Service App pool account should have SPDataAccess role for that database to login successfully.

MS documentation says:

SharePoint Service Application Pool account

• The SharePoint Service Application Pool account must be a domain user account. This account must not be a member of the Administrators group on any computer in the server farm. OK
• The following machine-level permission is configured automatically: This account is a member of WSS_WPG. OK

The following SQL Server and database permissions are configured automatically:

• This account is assigned to the SPDataAccess role for the content databases. When I create a new content database SPDataAccess role is not given automatically to SP Service Application pool account!!!
• This account is assigned to the SPDataAccess role for search database that is associated with the web application. There is no SPDataAccess role available in User Mapping list for Search database!!!

This account must have read and write access to the associated service application database. Not sure how to confirm this, but I can tell that Service Application pool account has SPDataAccess role in all the associated SA databases where it is available in User Mapping list (+ SharePoint_Shell_Access role in Admin and Config databases)

This account is assigned to the WSS_CONTENT_APPLICATION_POOLS role that is associated with the farm configuration database. OK

This account is assigned to the WSS_CONTENT_APPLICATION_POOLS role that is associated with the SharePoint_Admin content database. OK

I found a similar case from SP2010: https://social.msdn.microsoft.com/Forums/en-US/cde6c88f-2204-4884-9dce-0f82effd02f4/service-app-pool-account-and-content-database-access?forum=sharepointgeneralprevious

I think this is exactly the same situation happening in our client's SP2016 farm. I also made a test farm (SPSE) and it is happening there also. Service application pool account not getting SPDataAccess role automatically to content databases.

I also tried to create a new database with the -DatabaseAccessCredentials parameter:

$Credential = Get-Credential
New-SPContentDatabase "SharePoint_CDB4" -DatabaseServer "SPSE" -WebApplication http://sharepoint.domain.com -DatabaseAccessCredentials $Credential

That didn't add the SPDataAccess role to the Service application pool account for "SharePoint_CDB4" either.

So the only thing that works is to grant SPShellAccess role to the Service application pool account for all content databases, but that seems to be overkill since it more permissions than is needed.

Answer 1

Hi anonymous user ,

I am not able to reproduce this issue. In this scenario, I would suggest you open a ticket with Microsoft for further help.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 2

Actually now I just realized the "extra" permissions that SPShellAccess adds in addition to the SPDataAccess-role are related to Project Server... am I right? By extra permissions I mean these: - PSDataAccess - PSReportingSchemaAdmin ![245523-image.png][1] In the beginning I thought that "PS" refers to PowerShell, but can you confirm that actually means Project Server? If that is correct then there should be no worries adding ShellAccess rights to Service application pool account since our client does not have Project Server installed. [1]: /api/attachments/245523-image.png?platform=QnA

Answer 3

Ok,

I found from official Project Server installation documents that PSDataAccess and PSReportingSchemaAdmin refers to Project Server and not to "PowerShell". So I don't have to worry about that.

I also found that I don't need to give SPShellAdmin rights to Service App Pool account to grant SPDataAccess role to content dababases, instead I can run this:

$WA = Get-SPWebApplication http://sharepoint.domain.com/
$WA.GrantAccessToProcessIdentity(“DOMAIN\ServiceAppPoolAccount”)

...which gives Full Control permission to the above webapplication and also adds these roles to all databases belonging to the above webapplication:

• PSDataAccess
• PSReportingSchemaAdmin
• SPDataAccess

That command has to be ran everytime new databases are created, but that's not a problem.

You can mark this question answered.