Test-SPContentDatabase throws authentication mode error
Consider the following scenario:
- You move a content database from a Windows-claims web app to a new claims-based web application in a SharePoint 2016 or 2019 farm.
- You run Test-SPContentDatabase against the database.
- Example: Test-SPContentDatabase -Name Contoso_Content -WebApplication “https://team.contoso.com”
- The output contains this warning:
“The [contoso.com-443] web application is configured with claims authentication mode however the content database you are trying to attach is intended to be used against a windows classic authentication mode.”
Please note that this is a “warning” and not an error. Note in the screenshot above, “Error” is false, and “UpgradeBlocking” is false. This is a warning only, and will not prevent you from upgrading the database.
Possible Cause:
This can happen if an Active Directory (AD) group was added to Site Collection Administrators.
In a Windows-claims web application, AD groups are stored with an account name starting with “c:” like this: c:0+.w|s-1-5-21-1700552430-3460358242-3531541990-1157
When Test-SPContentDatabase is run, SharePoint checks for Classic authentication accounts with this SQL query:
SELECT TOP 1 [tp_SiteID],[tp_Login] FROM [UserInfo] WITH (NOLOCK) WHERE tp_IsActive = 1 AND tp_SiteAdmin = 1 AND tp_Deleted = 0 and tp_Login not LIKE ‘i:%’
It seems that the query logic is a little flawed and producing a false-positive. It’s expecting that only users will be returned and ignoring the fact that it’s perfectly valid to have an AD group in Site Collection Administrators, in which case, that group record will match the query criteria: tp_IsActive = 1 AND tp_SiteAdmin = 1 AND tp_Deleted = 0 and tp_Login not LIKE ‘i:%’. That is a “false-positive” because those groups are actually already in Windows-claims mode.
Warning: Under no circumstances should you ever edit the UserInfo table, or anything within any SharePoint database. Doing so is unsupported, and will likely lead to problems.
Resolution:
Run the following SQL query against the content database to see which accounts are triggering this error message:
SELECT [tp_SiteID],[tp_Login], [tp_title] FROM [UserInfo] WITH (NOLOCK) WHERE tp_IsActive = 1 AND tp_SiteAdmin = 1 AND tp_Deleted = 0 and tp_Login not LIKE ‘i:%’
If they are AD groups (tp_Login starts with “c:”) then you can simply ignore the Test-SPContentDatabase warning.
If you’d like to verify 100% that the issue is due to the AD group accounts, you can find out which site the groups exist in, remove them from Site Collection Administrators, and then run Test-SPContentDatabase again.
1. Run this SQL query against the problem content database:
— Find Windows auth groups that are added as Site Collection Admin
— Note: A blank “FullURL” column means it’s the root site collection
select AW.SiteId, aw.FullUrl, ui.tp_id, UI.tp_DomainGroup, ui.tp_siteadmin, ui.tp_login, ui.tp_title as GroupName
from userinfo UI (nolock)
join allwebs AW (nolock) on ui.tp_siteid = aw.siteid and AW.parentwebid is NULL
where UI.tp_login like ‘c:0+.w%’ and UI.tp_isactive = 1 AND UI.tp_SiteAdmin = 1 AND UI.tp_Deleted = 0
It should show the relative URL and group name for any site where a group has been added as site collection administrator. Please note that if the FullURL column is blank / empty, it means that is the root site collection in the web application.
2. Browse to the site and check if the group that matches the “GroupName” column from the above SQL query is shown in Site Settings | Site Collection Administrators. If so, remove it.
3. Run Test-SPContentDatabase again.
More Information:
If you’re looking around your UserInfo table and see some user accounts that are still in Windows classic notation, for example: tp_Login shows domain\userName instead of i:0#.w|domain\userName, that can be normal too.
For example, those accounts might not have been migrated to claims because they were no longer valid in Active Directory at the time of migration.
Also, seeing SHAREPOINT\system in your UserInfo table is perfectly normal.
Again, under no circumstances should you ever edit the UserInfo table, or anything within any SharePoint database. Doing so is unsupported.