Home → LOGbinder for SP KB → How To → Workaround if LOGbinder for SharePoint is having SQL database issues

3.5. Workaround if LOGbinder for SharePoint is having SQL database issues

A problem that might occur when using LOGbinder for SharePoint stems from the fact that SharePoint does not behave the same way through its web interface as through its API.

As a result, even though the account has been added correctly via Central Administration or the SharePoint site collection settings page, and has no problem when using the account in the SharePoint web interfaces, the privileges granted are not sufficient when third-party software uses the public SharePoint APIs, resulting in an ‘access denied’ error.

SYMPTOMS:

Even though the LOGbinder user is definitely a farm administrator, you get an event from LOGbinder like this:

Unable to configure SharePoint export. Details: Cannot open database "WSS_Content" requested by the login. The login failed. Login failed for user 'SHAREPOINTSERVER\logbinderaccount'. SQL Database 'WSS_Content' on SQL Server instance 'SHAREPOINTSERVER\OfficeServers' not found. Additional error information from SQL Server is included below. Cannot open database "WSS_Content" requested by the login. The login failed. Login failed for user 'SHAREPOINTSERVER\logbinderaccount'.

CAUSE:

SharePoint behaves differently when accessing it via its web interface versus accessing it via standard Microsoft SharePoint API’s in third-party software. As a result, it might happen that you are able to perform certain operations through the SharePoint web interface, but when doing the same from a third-party application (such as LOGbinder for SharePoint) that is using only standard, published SharePoint API’s, the same operations performed by the same user do not work.

WORKAROUND:

In the past we have explained how LOGbinder for SharePoint uses SQL privileges. We also informed you about the unfortunate workaround of giving dbo access to certain DB's in SQL in the sporadic cases when the SharePoint API interferes with access to the databases.

This was never a "workaround" that we were really happy with. Giving dbo access is not only like giving the bank the title to your home as collateral for the mortgage but also giving them a letter that says "Stop by anytime you want and while you're here feel free to repaint the walls and help yourself to the scotch in the pantry."

Thankfully, we have found a proper workaround that does not require dbo access. There is a role on the SharePoint SQL DB's named "SPDataAccess". We have found that giving the service account this role grants enough access for LOGbinder for SharePoint to function properly. Again we would like to specify that this is not the standard configuration needed with LOGbinder for SharePoint. This is only used in the rare situations when the SharePoint API is giving issues with DB access. For most of our customers the permissions set within SharePoint itself for the service account is all that is needed.

There are two ways to give the service account this role. One is using the SharePoint Management Shell and the other is directly in SQL (in our example below using SSMS).

Our preferred method is making the changes directly in SQL. We noticed that when using the SP Management Shell an extra role is given. We also noticed that this is not always the case as well. Sometimes the extra role is given and sometimes it is not. Why?  We don't know. Maybe it's a hidden Microsoft feature.

If this occurs, you will likely want to perform the following workaround, so please follow these steps:

1. Go to Central Administration and under “System Settings” click on “Manage servers in this farm”.

2. Make a note of the “Farm Information” at the top of the page, for example:

3. Using the server/instance specified above in the Farm Information, open SQL Server Management Studio.

4. In SSMS add your service account as a login.

5. Open the logins properties and locate the three databases that you're SharePoint farm is using for the Admin Content, Configuration and WSS Content databases. In this instance we have SharePoint_AdminContent(GUID), SharePoint_Config2019 and WSS_Content(GUID).

6. For each database map the SPDataAccess role to the login. You will notice that for the WSS_Content db, after saving the role change SSMS also grants the PSDataAccess and the PSReportingSchemaAdmin role. If you have more than one content db, then you will have to perform these steps on all applicable db's with the WSS_Content prefix. For more information on how to set SPDataAccess on a large number of content databases, click here.

You can also perform the steps above with a simple cmdlet using the SharePoint Management Shell. Run the following cmdlet:

Get-SPContentDatabase | Add-SPShellAdmin -UserName domain\ServiceAccount

So in our example below we ran "Get-SPContentDatabase | Add-SPShellAdmin -UserName lab\sp2019srvacct". Notice that doing this grants an additional role on all three databases; the SharePoint_Shell_Access role. As security experts our recommendation is obviously whichever process results in the least privilege needed to get the job done which, in this case, is making the changes via SSMS.

What does the SPDataAccess role allow?  According to TechNet, the SP_DATA_ACCESS role will have the following permissions:

​​Reference:  TechNet

Additional note:

A similar issue may occur with administrator privileges to SharePoint site collections: even though the service account is listed as a site collection administrator in SharePoint’s user interface, you receive an error that the user is not a site collection administrator.

If this occurs, perform similar steps as described above, but to the WSS_Content database. In this case, you would need to add only the LOGbinder SP service account, since the account you use to run the LOGbinder GUI does not need site collection administrator privilege.

It has to be emphasized that we don’t consider the above steps to be a fix, just a workaround to this SharePoint problem, which affects not only LOGbinder, but many other applications too. See, for example this, this, or this article. Even Microsoft says that it can happen and that sometimes “you cannot open a database in the SharePoint Management console of SharePoint Foundation 2010 or SharePoint Server 2010 even though you are a farm administrator who has full administrator rights”, unless you are a member of the db_owner fixed database role for the database. But we have found that the above SPDataAccess role is enough in these situations.

As a security company we strongly advocate the principles of least privilege, which we also apply in the design of our LOGbinder products. There is no reason why the LOGbinder service account should be granted any rights in SQL server. However, until Microsoft fixes this, the only way to get a third-party application work through SharePoint API is to implement the workaround outlined above.

This page was: Helpful | Not Helpful
← LOGbinder SP use of SQL Privileges How To Set SPDataAccess on Large Number of Content Databases →