SharePoint user permissions are stripped when using office web apps

  • Article

I recently had the privilege of working with office web apps and SharePoint 2013.  Consider the following scenario:

Users are given access to the Site via SharePoint groups or permissions directly to the site, not via AD Security groups.  Intermittently you'll notice the user has all their permissions stripped from the site thus resulting in access denied.  Currently their resolution was to remove the user account and re-sync from AD and grant permissions.  This works as the user will have permissions again, but has the potential for data loss due to custom user attributes that was populated after the sync.

 

My issue was around using Office Web Apps and oAuth.  The end user would attempt to open a document with Office Web Apps.  They would receive the error "Something went wrong" with a correlation ID.

 

 

Cause:

At some point the user had been imported by User Profile Synchronization (Profile Sync), deleted from Active Directory, recreated in Active Directory with the same account name, and then re-imported by Profile Sync.  When the user is re-imported, their SID is not updated in the UserProfile_Full table.  Now the SID in the UPA doesn’t match the SID in the UserInfo table for the site collections.

 

  • Import a user using Profile Sync. – They get a record created with proper SID in UserProfile_Full table and UserProfileValue table in the Profile database. The SIDs match in both tables at this point.
  • Delete and re-create that user in Active Directory. – They will have the same account name, but a new SID.
  • Run another Profile Sync. – The existing profile record will be updated with the new (good) SID in theUserProfileValue table, but the SID stored in UserProfile_Full will not be updated. It will retain the old (bad) SID. We now have a SID mismatch.
  • Give the user permission to a site, list, document, etc. -- It will be added to the site permissions with the new (good) SID.
  • The user opens a file in Office Web Apps. -- Part of the Office Web Apps authentication process (OAuth) is to call out to the User Profile Service Application (UPA) to get information about the user to augment their claims set and use that to open the file.
  • The UPA returns the old (Bad) SID in the Oauth token.
  • The Oauth token is presented to the SharePoint site to try to open the document.
  • The authorization process finds the user by account name in site permissions – Since the user has the same account name but different SID, the existing user record gets deleted from the site collection, removing all user permissions.
  • In SharePoint, the SID is treated as the unique ID for the user. It doesn’t matter what the account name is, if you have a different SID, you are a different user.
  • Since we can’t have more than one user with the same account name active at any given time, the original user record is marked as deleted and all of the permissions for that user are removed.  
  • This is why the user gets “Access Denied” and must be added back to site permissions.
  • When the user is added back to the site, they are added back using their correct (good) SID.  This effectively marks their ‘Bad’ record in the UserInfo table as deleted, and re-activates their ‘good’ record. – The user is fine until they go through the Oauth process again.

 

 

Resolution:

We need to update the SID in the UserProfile_Full table.  One way to do this would be to delete all the of the problem profiles and re-import them.  However, all of those users would lose profile data that is manually entered (like “About Me”, “Ask me about”, “Skills”, “Interests”, etc).

 

KB article states delete the profile, but we can't just delete the profile as this will sometimes result in data loss

https://support.microsoft.com/en-us/kb/2908321

 

Instead, you can run Move-SPUser to update the SID in the UserProfile_Full table to be the “Good” SID for the user. Since we’ll be passing the same account name as both the ‘old’ and ‘new’ account, the SID will be the only changed for the user.  Here’s an example of running this for one user:

 

 

 

 

Scripts:

Verification of users having this problem:

We can run the following query against the Profile database to identify users that are in this state

 

select upf.RecordId, upf.NTName, upf.PreferredName,  upv.PropertyVal as  [SIDfromUserProfileValue], pl.PropertyName, upv.PropertyID
into #temp
from UserProfile_Full upf (nolock)
join UserProfileValue upv (nolock)on upf.RecordID = upv.RecordID
join PropertyList pl (nolock) on pl.PropertyID = upv.PropertyID
where upv.propertyid = 2
select upf.RecordId, upf.NTName, upf.PreferredName, upf.SID  as  [SIDfromUserProfile_Full], #temp.SIDfromUserProfileValue
from UserProfile_Full upf (nolock)
join #temp on  upf.RecordID = #temp.recordid
where upf.SID != #temp.SIDfromUserProfileValue
drop table  #temp

 

Preferred resolution:

$url = "http://www.contoso.com/"

$claimsAcc = "i:0#.w|contoso\user1"

$user = Get-SPUser -Identity $claimsAcc -Web $url

Move-SPUser -Identity $user -NewAlias $claimsAcc -IgnoreSID

 

For multiple users: we can simple create a csv file containing the user accounts.

The CSV file should have a ‘header’ called “NTName”.  It should look like this:

 

NTName

CONTOSO\VP1

CONTOSO\jack

CONTOSO\VP2

CONTOSO\usera

CONTOSO\userx

CONTOSO\VP3

 

############################## -- Script for multiple users -- ##############################

#This script is provided as-is with no warranties expressed or implied. Use at your own risk.

#Synopsis: Use this to run move-spuser against a list of account names stored in a CSV

#The script calls move-spuser to fix the issue.  Move-spuser is a farm-wide operation, so it only needs to be run once per-user.

#The “$URL” variable can really be any site collection in the farm.  The script just requires a single "spweb" object so that it can establish the proper context.

#Just set the top three variables: $url, $path, $logfile

 

$url = "http://team.contoso.com"  # Any site collection

$path = "c:\problemUsers.csv" # The input file with user names to migrate

$logfile = "c:\move-SPUserLog.txt" # The output log file

 

Add-PSSnapin microsoft.sharepoint.powershell -ea SilentlyContinue

$date = Get-Date -Format U

"Started Move-SPUser at " + $date + " (UTC time)" | out-file $logfile -append

"===============================================" | out-file $logfile -append

$ErrorActionPreference = "stop"

$csv = Import-Csv -Path $path

[array]$NeedtoFix = @()

$web = get-spweb $url

foreach($line in $csv)

{$NeedtoFix += $line}

$fixTotal = $NeedtoFix.Count

for($j=0; $j -lt $fixTotal; $j++)

{

$acc = $NeedtoFix[$j].ntname

$claimsAcc = "i:0#.w|"+$acc

"Fixing user: " + ($j+1) + " out of " + $fixTotal + " --> " + $claimsAcc | out-file $logfile -Append

    try{$user = $web.EnsureUser($claimsAcc)

        Move-SPUser -Identity $user -NewAlias $user.UserLogin -IgnoreSID -confirm:$false

        write-host "Fixed user: " ($j+1) " out of " $fixTotal " --> " $claimsAcc

        }

   catch [system.Exception]

        {"ERROR!!! for user: " + $claimsAcc + " -- " + $_.Exception.Message | out-file $logfile -append}

}

############################## -- Script -- ############################## 

 

 

More Information:

ULS Logs you might see while troubleshooting this issue:

 

09/22/2015 09:53:15.72 w3wp.exe (0xA2F4) 0x7268 Office Web Apps WAC Hosting Interaction adhrj Monitorable HttpRequestAsync (WOPICheckFile,WACSERVER), request failure [HttpResponseCode:Unauthorized, HttpResponseCodeDescription:Unauthorized, url:https://site.contoso.com/_vti_bin/wopi.ashx/files/912bd33e83f046f58542aa588e3abcba?access_token=REDACTED_1052&access_token_ttl=1442969592697] a0f7ae82-2d27-4f8c-881c-9e08a02b6c8f

 

09/22/2015 09:53:15.72 w3wp.exe (0xA2F4) 0x7268 Office Web Apps WAC Hosting Interaction adhrr Medium HttpRequestAsync (WOPICheckFile,WACSERVER) Setting Completion [Time in ms: 1992, Bytes Read: 0] a0f7ae82-2d27-4f8c-881c-9e08a02b6c8f

 

09/22/2015 09:53:15.72 w3wp.exe (0xA2F4) 0x7268 Office Web Apps WAC Hosting Interaction ajdv9 Medium HttpRequestAsync (WOPICheckFile,WACSERVER) SetCompletion Track start | setting trackers | WebRequest.Create() | Create() returned | _req.ContentLength 0 | setting request headers | setting user agent | setting keep alive | setting timeout callback | Start calling StartResponseProcessing | StartResponseProcessing._req.BeginGetResponse() | BeginGetResponse() returned | StartResponseProcessing RETURNS | Start RETURNS | End.AsyncWaitHandle.WaitOne() | GetResponseCallback isSync:False | _req.EndGetResponse() | GetResponseCallback WebException | RecordWebException | RecordResponse Unauthorized | SetCompletion False |  a0f7ae82-2d27-4f8c-881c-9e08a02b6c8f

 

09/22/2015 09:53:15.72 w3wp.exe (0xA2F4) 0x9E50 Office Web Apps WAC Hosting Interaction ajahx Unexpected (WOPICheckFile,WACSERVER) Host reports UPA out of sync [status:Unauthorized, url:https://site.contoso.com/_vti_bin/wopi.ashx/files/912bd33e83f046f58542aa588e3abcba?access_token=REDACTED_1052&access_token_ttl=1442969592697] a0f7ae82-2d27-4f8c-881c-9e08a02b6c8f

 

09/22/2015 09:53:15.72 w3wp.exe (0xA2F4) 0x9E50 Office Web Apps WAC Hosting Interaction ajahy Medium Error message from host: Unauthorized Access to SharePoint objects from WOPI context., host correlation:  a0f7ae82-2d27-4f8c-881c-9e08a02b6c8f

 

09/22/2015 09:53:15.72 w3wp.exe (0xA2F4) 0x9E50 Office Web Apps WAC Hosting Interaction ajryn Unexpected WOPICheckFile,WACSERVER ConfigError [error:The SharePoint site appears to have its UPA in an improper sync state.  Please contact the SharePoint administrator.  The SharePoint site's WOPI handler has determined that the user permissions for this file/folder do not match the user permissions when wopiframe.aspx was loaded. | WOPICheckFile,WACSERVER | Host Status Code: Unauthorized | Host Error Info: SyncUPA, url:hhttps://site.contoso.com/_vti_bin/wopi.ashx/files/912bd33e83f046f58542aa588e3abcba?access_token=REDACTED_1052&access_token_ttl=1442969592697, host correlation:] a0f7ae82-2d27-4f8c-881c-9e08a02b6c8f

 

09/22/2015 09:53:15.72 w3wp.exe (0xA2F4) 0x9E50 Office Web Apps WAC Hosting Interaction adhsk Unexpected WOPI CheckFile: Catch-All Failure [exception:Microsoft.Office.Web.Common.EnvironmentAdapters.ConfigErrorException: The SharePoint site appears to have its UPA in an improper sync state.  Please contact the SharePoint administrator.  The SharePoint site's WOPI handler has determined that the user permissions for this file/folder do not match the user permissions when wopiframe.aspx was loaded. | WOPICheckFile,WACSERVER | Host Status Code: Unauthorized | Host Error Info: SyncUPA     at Microsoft.Office.Web.Apps.Common.WopiDocument.LogAndThrowWireException(HttpRequestAsyncResult result, HttpRequestAsyncException delayedException)     at Microsoft.Office.Web.Apps.Common.HttpRequestAsync.End()     at Microsoft.Office.Web.Apps.Common.WopiDocument.GetWopiRequestResultWithRetry(Int32 maxSize, MemoryStream ms, WopiRequest wopiRequest)     at Microsoft.Office.Web.Apps.Common.WopiDocument.CheckWopiFile()] a0f7ae82-2d27-4f8c-881c-9e08a02b6c8f