SharePoint 2016 :: PSConfig execution fails post July 2018 update


On a Sharepoint 2016 Farm ,   which has a  Search service application which was created thru PowerShell & the  Service Account used was other than farm account , Farm upgrade might fail after the  July 10 update for  SharePoint 2016 .

 After you apply this update (4022228), the PSConfig tool may fail and generate the following error messages:

An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown. Additional exception information:

Upgrade [SearchAdminDatabase Name=SEARCH_DB] failed. (EventID:an59t)

 Exception: The database principal owns a database role and cannot be dropped. The proposed new database owner is already a user or aliased in the database. (EventID:an59t)

Upgrade Timer job is exiting due to exception: System.Data.SqlClient.SqlException (0x80131904): The database principal owns a database role and cannot be dropped. The proposed new database owner is already a user or aliased in the database.

 

Cause

----------

To fix a permission issue that is caused by provisioning the Search Service Application with PowerShell, this update (4022228) includes a change to re-link the DBO user to the Farm Service account for the search databases. However, the Farm Service account may already Marked as Owner of  SPSearchDBAdmin  or Any other  database role in the SharePoint Farm. This prevents dropping the account in SQL and causes the PSConfig tool to fail.

In ULS  logs  confirm the  Account  which is being set as Owner of the  database

<Date-Time>        OWSTIMER.EXE (PID)        0x13 SERVERNAME        SharePoint Server        General        en41        High        Changing owner of database 'SQLServer\Instanc1.SSA_Search_CrawlStore' to 'Domain\ServiceAccount'.  

Note: This can  affect any of the  4 Search Databases  of the Search Service application .

 

Workaround

-------------------

To work around this issue, From SQL Management Studio , Review all database roles of  each database of Search SSA & Ensure that  Owner of All roles is “dbo” & not the  Farm Service account  . If this is the case ,  Use following Steps to update

  1. Double Click the  Role on the Search DB , For eg. SPSearchDBAdmin
  2. Set the Owner as dbo role rather than the service account Domain\ServiceAccount as seen in ULS logs accordingly .

Then, you can run the PSConfig tool again.

 

Additional Info

Alternatively the sys.database_role_members Query can be used to Dump All Roles & their memberships of a Database

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql?view=sql-server-2017

 

SELECT DP1.name AS DatabaseRoleName,

isnull (DP2.name, 'No members') AS DatabaseUserName

FROM sys.database_role_members AS DRM

RIGHT OUTER JOIN sys.database_principals AS DP1

ON DRM.role_principal_id = DP1.principal_id

LEFT OUTER JOIN sys.database_principals AS DP2

ON DRM.member_principal_id = DP2.principal_id

WHERE DP1.type = 'R'

ORDER BY DP1.name;

 

Status

----------

Microsoft is aware of this issue. The change has been removed from the August 2018 update, and we are working on permanent fix that will be released in a future update.

 

POST BY : Rajan Kapoor [MSFT]


Comments (0)

Skip to main content