Thursday, March 15, 2018

Why SharePoint 2013 users added as individual magically removed and how to fix the issue

Recently SharePoint 2013 users reported that they added users to access SharePoint site, however, the added users are not able to access the site. As matter of fact, they are removed from the site magically!

After debugging this issue, we found the root cause is SharePoint 2013 user profile SID is out of sync with AD SID. We believe SharePoint recognized as invalid users and removed them from the site. Let’s dig this into more details.

First how user’s AD SID changes? We have worked with AD team and identified two sceneries SID could change.

After user left the company and the AD account terminated, if the same user re-join the same company, AD will assign the same login ID and UPN. However, this user will have different SID. The following query will display one user in this case. You will see two entries and the 2nd is user re- join the company.

Query is here:
Get-ADObject -LDAPFilter "(samaccountname=userId)" -IncludeDeletedObjects -Properties objectsid,whencreated,whenchanged | select name,whencreated,whenchanged,objectsid | fl 

If user change the AD domain like change from “domain1” to “domain2”, AD SID will change but the SID history will track the old SID. Below is the example.

The query is listed here: get-aduser -Identity <userId> -Server -Properties sidhistory

Second let’s identify why SharePoint will remove these users. 

Please find the details in depth to understand what happens internally when you delete a user and recreate it with the same name:

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.

This causes a chain-reaction as below:
  • 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 the UserProfileValue 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.
Note: The above scenario involves Office Web Apps (OWA), but this same thing could happen with any feature that uses OAuth.  This includes (but is not limited to): Office Web Apps, Workflow, Site Mailboxes, SharePoint-hosted Apps, and Provider-hosted Apps).

Third step is to identify users SID out of sync. There are two ways to get users with mismatch SID. 

The first way is to loop through SharePoint user profile and compare the SID with SID from AD query. Here is snippet of the code.

# Get SID from SharePoint user profile

$snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
if ($snapin -eq $null) {
  Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
$ca = Get-spwebapplication -includecentraladministration | where {$_.IsAdministrationWebApplication}
$spsite = $ca.url
$site = Get-SPSite $spsite
$context = Get-SPServiceContext $site
$upsa = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
$profile = $upsa.GetEnumerator() |Where-Object {$_.AccountName -eq $userID}

$userProfSID = $profile["SID"].Value

# Get SID from AD
$adUser = Get-aduser -Server “domain” "ferrari2"
$adUserSID = $adUser.SID.Value

The second way is to identify the Profiles where the SIDs don’t match between UserProfile_Full and UserProfileValue

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

Now the forth step is to fix the issue. 

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).

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 change for the user.  Here’s an example of running this for one user:
$url = ""
$claimsAcc = "i:0#.w|contoso\user1"
$user = Get-SPUser -Identity $claimsAcc -Web $url
Move-SPUser -Identity $user -NewAlias $claimsAcc -IgnoreSID 

If you have a large number of users in this state, you’ll want to run this in a script that loops through each user.  We can use the previous script to dump users with mismatch SID in to a csv file. The use the following script to fix the SIX issue.
#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 = ""  # 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}

You might need to schedule this fix frequently so users will not have such issues.
Thanks Prerna Vashistha from Microsoft for the detailed explanation and the workaround.

Tuesday, March 13, 2018

Procedure and tips to generate OAuth access token to access SharePoint Online all site collections?

In order for external applications like Java application to integrate with SharePoint online (SPO) sites through REST web services, Microsoft provided a way that is OAuth for the authentication. In our case, we have an external application Infomatica needs to integrate with all SPO site collections. Here is the procedure to generate the token that could be used for all SPO sites.

1. Create an app from any SharePoint online site through URL like this

Please note you can enter "localhost" as "App Domain" for SPO app.

After you create the app, you will have the client Id, client Secret as in the below screenshot.

 2. Register the app through SharePoint admin site in order to have the token to access all SPO sites. The URL is like this https://<tenant> This is same step as described by Andrew Koltyakov.

After enter the client Id from the step #1, then enter the permission for the app. In this case, we grant read access to all site connection. The xml file would look like this.

<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="Read" /></AppPermissionRequests>

For complete the list of the SPO permission scope, please refer this article for details. There are more discussions on the permission scope. Then you should trust the app.

If you only want to grant access to one site collection, you could register the app though that site like  https://<tenant><sitename>/_layouts/15/appinv.aspx. The xml permisison configuration can look like below.

<AppPermissionRequests AllowAppOnlyPolicy="true">
<AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl" /></AppPermissionRequests>

3. Generate tenant Id (realm) and SharePoint resource identifier.

There are many different ways you can get realm or tenant Id as I described in early blog. The easiest one is to check any app permission on the site as in the below screenshot. Please note SharePoint resource identifier is a constant highlighted as red. The tenant Id is highlighted as in blue.

4. Get the Authorization code from Azure Access Control Service by using this URL

In our case, this is the URL:
https://<tenant> &response_type=code&redirect_uri=https://localhost/.

Then trust the app. You will see the code in the return URL.

The code looks like this and the code is in red.


Please the code is only valid for FIVE minutes, you need to generate the access token in five minutes. Otherwise, you need to generate another code.

5. Generate SPO access token.
Open Postman and construct the following request.

Request Type: POST
URL:<Realm or Tenant Id>/tokens/OAuth/2
             Key:     Content-Type
             Value:  application/x-www-form-urlencoded
Body: Select "x-www-form-urlencoded" and key value as below.

The postman screenshot is as below.

The access token and refresh token are in the response body as in the screenshot.

6. Use SPO access token to access any SPO site. I'm using postman to query site tile as example.

The POST REST URL is like https://<tenant>$select=Title

The headers are as below:

Key                    Value
Accept                application/json;odata=verbose
Authorization     Bearer  <accessToken>

The result from postman is as below.

7. Generate SPO refresh token. Please note the access token is valid for only one hour.  The refresh token is valid for six months. You can use the refresh token to generate the new access token.

This step is almost identical to step #5 with different header values as below.

The refresh token is display in response body similar to Vikas Kottari described in his article.

Now you have the refresh token valid for six months to generate the access token to access all SPO sites.

There are few things you need to remember.

1. Please remember the app client ID. After you have the access/refresh token, you can delete the app permission for the app. Then there is no UI to manage the app. The only way to see the app is to query the client Id through the URL like below. The URL could be admin or SPO site.


2. If you want to have a one time access token, the procedure provided by SHANTHA KUMAR is little simpler. However, it will not have refresh token and you need to generate code to generate access token after it expired.

3. There are some other ways we could generate the access token through Graph. We will discuss in details in future blob.

Wednesday, December 13, 2017

SharePoint web services always use forms authentication when forms-based authentication with an LDAP provider configured

After we configure forms-based authentication with an LDAP provider for a new SharePoint 2013 web application, users could select either window or form login to SharePoint. However, we have issue to use any SharePoint web services through window accounts. The problem is SharePoint is always try to authenticate the user as form based authentication after debugging with Microsoft. Here is one simple Powershell to illustrate the issue.

if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null )
            Add-PSSnapin Microsoft.SharePoint.PowerShell

$spserver = "";
$sharePointAuthUri = "https://$(spserver)/_vti_bin/authentication.asmx?wsdl";

$sharePointAuth = New-WebServiceProxy -uri $sharePointAuthUri
$sharePointAuth.CookieContainer = New-Object System.Net.CookieContainer
$authResult = $sharePointAuth.Login("username","password")
$mode = $sharePointAuth.Mode()  # Always Forms

The login in the dump also indicates SharePoint try to authenticate the user as LDAP users.

  0x0000006866232a38 System.IdentityModel.Tokens.UserNameSecurityToken
  0000  id            : 00000068662329e8  "LdapMember:LdapRole:True" [24] (System.String)
  0008  password      : 00000068662322c0  "********" [8] (System.String)
  0010  userName      : 0000006866232298  "username" [6] (System.String)
  0018  effectiveTime : 0000006866232a58 11/29/2017 6:13:54 PM (System.DateTime)

There are some other discussions also confirmed this behavior. Microsoft support from SSRS team confirmed this is the behaviors for SSRS web service.

We are working with Microsoft try to see if there is way to authenticate window user on SharePoint forms-based authentication web application. The workaround for any web service against SharePoint forms-based authentication web application is to use the from-based account.

Tuesday, November 28, 2017

The "disaster" of SharePoint Server 2016 MinRole

MinRole is a new farm topology based on a set of predefined server roles introduced in SharePoint Server 2016. When configuring your SharePoint farm, you now select the role of a server when you create a new farm or join a server to an existing farm. SharePoint will automatically configure the services on each server based on the server's role. SharePoint Server 2016 has been optimized for the MinRole farm topology.

After we followed the Microsoft instructions to configure the SharePoint 2016 servers as Front-end with Distributed Cache, Application, Application with Search, we found one issue. All wsp solutions deployed to ALL the servers including WFE, search and app servers!

After looking at the server role in details, we found “Microsoft SharePoint Foundation Web Application” service enabled as default for “Application” role and “Application with Search” role. This will cause few major issues but not limited as listed below.

1. Third party license issues. Most of the third party solutions are licensed by WFEs that is defined that server is running “Microsoft SharePoint Foundation Web Application” service. You may run into license issues if all servers have the service running.

2. Deployment performance issue. The custom solution will take significant time since it will deploy to all servers. We had this issue for one small solution deployment.

3. Potential security issues. The application servers or the search servers can also accept the call event they may not intended to server the end users request. This may cause security concerns since most WFE are behind load balancer.

4. Maintainability issues. The application server and search server will have all files related to custom solution deployed. It’s difficult to manage the configurations.

We have seen people to create custom role to avoid this issue. 

It looks like a "disaster" to include the “Microsoft SharePoint Foundation Web Application” service in the “Application” MinRole.

We have discussed with Microsoft to move the following two services to WFE so we have two custom MinRoles for WFE and App servers. If you need to migrate the default MinRole to custom MinRole, you could refer following two articles.

Friday, November 10, 2017

How to resolve "EPERM: operation not permitted" error when installing Yeoman SharePoint generator?

The SharePointFramework (SPFx) is a page and web part model that provides full support for client-side SharePoint development, easy integration with SharePoint data, and support for open source tooling. With the SharePoint Framework, you can use modern web technologies and tools in your preferred development environment to build productive experiences and apps that are responsive and mobile-ready from day one. The SharePoint Framework works for SharePoint Online and soon also for on-premises (SharePoint 2016 Feature Pack 2).

One step to Set up your SharePoint client-side web part development environment is to Install Yeoman SharePoint generator. We run into the issue the installation failed with the following error.

 131672 verbose stack Error: EPERM: operation not permitted, rename 'C:\Users\harryc\AppData\Roaming\npm\node_modules\@microsoft\generator-sharepoint' -> 'C:\Users\harryc\AppData\Roaming\npm\node_modules\@microsoft\.generator-sharepoint.DELETE'

Here are some steps to help you to resolve this or workaround this issue.

1. First, verify to run the script as administrator.

2. Second verify the npm and node version are in supported version.  You can use the following command to verify the versions. Install the support versions, clean up the npm cache, and try again.

npm -v

node -v

 npm cache clean --force

3. Disable anti-virus. If you have anti-virus (Avast) software, disable it before installation as in other blog.

4. Stop Windows Defender. If you have Windows Defender running, you could as administrator to stop it during the installation.

5. Copy the Yeoman SharePoint generator files from another machine. If none of the above options are available for you, the workaround I used to work around this issue is to copy the Yeoman SharePoint generator files. Here is the details and tricks.

Install the Yeoman SharePoint generator on different machine (VM 2012 R2 for example) that has no issue. Copy all the folders and files under to the machine that failed to install.


If you try to copy the everything in one copy, you might run into copy error. I would recommend to copy few folders and files under the ..\@microsoft\generator-sharepoint\node_modules folder at a time.

After you have all folders and files copied, you could use this machine to develop The SharePoint Framework (SPFx) now.

Thursday, November 2, 2017

How to workaround "Sorry, we are having trouble connecting to the server" error from Chrome and Edge for SharePoint 2016

After configured SharePoint 2016 with FP2, we have seen  "Sorry, we are having trouble connecting to the server" error in many places using Chrome and Edge. This error does not happened when you are using Firefox or IE.  Here are the findings and workaround.

1. Here are some functions but not limited to you will receive this error. 
  • Add users to any permission groups like administrator, the error displayed when you typing the user but could not resolve the name. (See picture #1)
  • Unable to add any list item to the list (See picture #2)
  • Unable to delete any list item from list view
  • Unable to share documents with anyone
  • Unable to add list template to list template gallery
  • Unable to delete list template from list template gallery
  • Constantly getting error "Sorry, we are having trouble connecting to the server".

2. Debugging error from fiddler. 

If you debug the issue through Fiddler, you will find the “403” error and the “Origin” under Security of the request Header is dropping the port number like this below.

3. Steps to reproduce the issues.

We did further debugging with Microsoft and identified this is new security “feature” implemented in SharePoint 2016 that is causing this issue. This is impacting any web applications with SSL that is not in default 443 port number. Here is the step to reproduce the issue.

In SharePoint 2016 create two web applications with root site collections and then enable for SSL
One web applications use default port number and another is something like 51000.
In central admin go to  alternate access mapping settings and add internal URL mapping to the second new web application.  Use the same name and append a port number.
Example map:

Internal URL
Public URL

Go to IIS on the WFE and edit binding, change 443 to port 51000 and apply SSL cert to binding.
On load balance device configure for port redirection and SSL offload
Configure device to listen for https:\\
Configure device to send traffic to WFE node as https:\\
Browse to the site as https:\\
Site settings -> People and Groups ->New ->Add user, people picker should be present now. Type a user name and press to activate name resolution.
Error message "Sorry, we are having trouble connecting to the server" will be displayed

We have tied to add a Custom Rule in the fiddler like below and the issue can be resolved.
    static function OnBeforeRequest(oSession: Session) {
        if ( oSession.HostnameIs("") && oSession.uriContains("/ProcessQuery")) {
       // …

4. Multiple options to work around this issue.

Now we have few options to work around this issue. Here are the options confirmed with Microsoft.
  • Option 1 - Create a rule in the Load balancer
  • Option 2 - Use the same SSL certificate on all the web applications in the farm using a SAN configuration and configure all the web applications to use port 443 and a host header
  • Option 3 - Configure all the VIPs in the LB to forward to the SharePoint servers on port 443 instead of the port the web applications is actually listening on
  • Option 4 - Configure the SharePoint servers to have multiple IP addresses for each web applications so they all can use port 443.

We have implemented the option one by adding the following rule to the Load balancer.
IF the hostname = " " && the URI contains "/ProcessQuery”

You might try other options that should also resolve the issue.

Thursday, August 17, 2017

Ultimate debugging guide for SharePoint 2013 on-premises Office Web Apps (OWA)

When used with SharePoint 2013 on-premises, Office Web Apps (OWA) provides updated versions of Word Web App, Excel Web App, PowerPoint Web App, and OneNote Web App. Users can view and optionally edit Office documents by using a supported web browser on computers and on different mobile devices, such as Windows Phones, iPhones, and iPads. In addition to Office documents OWA can also support PDF view in browser.

After you follow Microsoft instructions to install OWA to facilitate users to view different documents in browser, users might run into issues quite often. Here is the ultimate debugging guide for SharePoint 2013 on-premises Office Web Apps when you have issues.

1. Whenever you have issues on OWA, the first thing to check is if OWA is available. You could use the following URL to verify if OWA is available. If the OWA is available, the result should be similar as the screenshot.


2. Next you will need to verify if SharePoint still connect to OWA servers. You could run the following command from SharePoint WFEs.

Get-SPWOPIBinding -Server "OWASERVER.MYCOMPANY.COM"  (If you know the OWA server URL)

Get-SPWOPIZone | Get-SPWOPIBinding (If you do not know the OWA server URL)

The result should be list of file type configured for OWA to support.

3. The third step is to check the  OWA zone by running the following Powershell.


If the zone is not correct, please set to the correct like this in our environment.

Set-SPWOPIZone -zone "external-https"

4. The third step is to very OWA version. We found different OWA versions may support different type of the files. The easiest way to verify OWA version is to run the following Powershell. You can find the OWA version from the result as highlighted.


Key                                                                                                Value
---                                                                                                -----
X-CorrelationId                                                                                    dfed84e9-ca53-4be6-bec8-399d5e61c9fc
X-OfficeFE                                                                                         5632742190694a8bba2353f6ddea79d8
X-OfficeVersion                                                                                    15.0.4937.1000
Accept-Ranges                                                                                      bytes
Content-Length                                                                                     6241
Cache-Control                                                                                      public,max-age=31536000
Content-Type                                                                                       text/html
Date                                                                                               Thu, 20 Jul 2017 17:22:54 GMT
ETag                                                                                               "01bbdc714e9ce1:0"
Last-Modified                                                                                      Sun, 24 Nov 2013 12:57:50 GMT
Server                                                                                             Microsoft-IIS/8.0
X-Powered-By                                                                                       ASP.NET

There are two other way to verify OWA build number you might use. 

A. If you are end user and do not know how to use Powersell, you can use browser  to hit the URL like below in the browser.

https://owaserver /op/servicebusy.htm

Then you can use Fiddler or F12 to check the request header header as described in the article.

B. You can also use server Powershell to verify the OWA version as described here.

5. The forth step is to verify if any file type are suppressed in the OWA. You can run the following Powerhsell. You should find the suppressed file type as the screenshot.


When OWA Server view mode is used to view workbooks, the following BI features will not be available.

  • Excel Web Access Web Part
  • Refresh OData connections
  • View and interact with Power View reports
  • View and interact with PowerPivot data models
  • Refresh PowerPivot data models
  • Refresh data by using the Excel Services unattended service account
  • Refresh data by using Effective User Name connections
  • Kerberos delegation
You will following the instruction provided by Tom to suppress the excel from OWA to avoid these issues. The Powershell is described in Tom's Microsoft TechNet site.

New-SPWOPISuppressionSetting -Extension "XLS" -Action "view"
New-SPWOPISuppressionSetting -Extension "XLSM" -Action "view"
New-SPWOPISuppressionSetting -Extension "XLSX" -Action "view"

6. The fifth step is to verify the web application "Permissive mode in browser file handling" setting. You could refer this article to verify.

7. The sixth step is to verify if site collection feature "Open Documents in Client Applications by Default" enabled. You could refer to Microsoft instruction to enable it from Powershell or UI.

8. The seventh step is to verify the library setting if  you have correct setting for "Opening Documents in the Browser".

One note to point out is OWA upgrade or patch process. You have to pull the OWA server and upgrade or patch. After that you need to bind the SharePoint server again! Remember to do this after the upgrade or patch. You could use the steps listed above to verify the OWA after each upgrade or patch.

OWA provides document view/edit inside the browser. After you know the tips and tricks, users can enjoy this function.