Thursday, April 26, 2018

Tips to avoid and resolve common issues in Azure Automation

Azure Automation is a software as a service (SaaS) application that provides a scalable and reliable multitenant environment in which you can use runbooks to automate processes. There are many tricks you would need to know before you could enjoy this feature.


1. The first tip is to handle "Automation Account" creation error. The common issue is the error Classic Run As account creation error like "Classic Run As Creation Error ForbiddenError: The server failed to authenticate the request.".

The way to resolve this is to add the Azure Account as co-administrator or the administrator of the subscription. The procedure is described here.

You can then delete the Classic Run As Creation and then recreate it.

2. The second tip is to handle certificate expiration. Azure automation certificates expire in one year.

You will get error message when it expires. You can renew from the UI or run the powershell to set never expire. The example PowerShell is listed here.  Here are some detailed information.

a. Run PowerShell Login-AzureRmAccount to get "Environment" variable.

b. Run PowerShell to get "SubscriptionId" and "AutomationAccountName".
Install-Module -Name AzureAutomationAuthoringToolkit 
Get-AzureRmAutomationAccount -ResourceGroupName "yourresourcegroupname" 

3. The third tip is to update default modules. The default modules for Azure Automation is old version and may functions may not exist for that version, you need to manually click "Update Azure Modules" in the "Modules". Here is the default modules and you can see most of the modules are in version 1.

Here is the screenshot after updates.

There are few other tips already covered in previous blog.

This link walks you through the pricing model of everything available in Azure.

Here are some documents about Update Management, which uses Log Analytics. 

Three different ways to use NextLabs 8.5 policy to block SharePoint site collections

NextLabs 8.5 provides new policy module to block SharePoint site collections. Here are three most popular ways to block SharePoint site collections.

1. Use Resource Components with site url. 

You can add one site collection url to one resource component and the policy can include multiple Resource Components to clock multiple site collection. See screenshots below.

2. Use expression in advanced condition in the policy.

You can add expression on the urls in advanced condition in the policy as in the below screenshot.

3. Use SharePoint site collection site property bag value or sub-site web property value.

a. First, add the SharePoint site collection site property bag key in the SharePoint Enforcer configuration.xml file.  Bounce the enforcer after the change.

Example is below.

    <PropertyBag disabled="false" level="SiteCollection">
      <Property disabled="false" name="SensitiveSiteCollection" attributename="SensitiveSiteCollection" />
    <PropertyBag disabled="false" level="SubSite">
      <Property disabled="false" name="SensitiveSubSite" attributename="SensitiveSubSite" />

b. Add the property to site collection property bag.

c. Construct the component resource in condition with the property bag. Then use the component resource in the policy.

Based on our testing, the option #3 is much simple than other two options. We will implement option #3 in the future for most of the policies. Of cause, the site collection bag can be modified by site owner and the policy can be impacted. As a result, we will need to address the property bag seciruty control if we use option #3.

Wednesday, April 25, 2018

Procedure to add IP condition for NextLabs policy on version 8.5

If you need to block/allow SharePoint request based on the original user request IP address, here are the procedures.

1. Go to the following web site and convert IPv4 to IP Decimal.

2.  Create a resource component. In the conditions, add "inet_address" condition with the decimal value of the IP address from step #1. Then create the policy to include the resource component.

3. If you need to test the original IP in "X-Forwarded-For" that is in the request header but not in the direct request, you can use the "Modify Header Value" add-in for your testing. The screenshots are listed below.

Now you can test any http request header in the NextLabs policy.

Procedure to add IP range like sub-net for NextLabs policy on version 8.5

If you need to block or allow SharePoint request from certain sub-net, here are the procedures.

1. Create the locations file. This is a text file containing any number of location definitions, one per line. Each line has the format    

<LocationName> “<AddressMask>”

where <LocationName> is the name by which you want to refer to the site when referring to it in Control Center tools such as Policy Studio Desktop Client, and <AddressMask> is a CIDR-like mask for the 32-bit IP address of a machine that is in the given location. Note the following requirements:
• Each address mask must be enclosed in double quotes
• The location name may not contain spaces
• The two elements must be separated by a space

For example, to define a location called VPN that represents all PCs connecting through a virtual private network, you might create the following entry:    
VPN ""

Create additional similar lines for all the other hosts that are part of the VPN group. Here is another example:    
intranet “”

You can also put comments in the file by beginning each comment line with #. For example:
# The following lines define the machines in the
# Boston office 

2. Install the location and run import locations utility.

Change to the directory <InstallDir>\tools. By default, the directory is: \Program Files\NextLabs\PolicyServer\tools\ 

Run the Import Locations utility with appropriate values for all parameters, as shown in the following example. This line must provide the name and path of the locations file and connection information for the system database. The last parameter, -i, is required only if the database type is Oracle or SQL Server.

importLocations.bat -l <LocationsFile> -u <DB_user> -w <DB_password> -s <DB_server> -p <DB_port> -d <oracle|postgres|sqlServer> [-i <instance>]

Example is:
importLocations.bat -l locations.txt  -u NextlabsDBUser -w nextlabspassword-s SPSQLSBX -p 1433 -d sqlserver -i NextlabsDB

When the utility finishes running, all the locations defined in the input file are present in the Information Network Directory. You will see ‘Success’ after running the command.

You can use the enrolled sites as values for the Site property when defining Computer components in Policy Studio. 

3. Create the policy using the required subject and resource and action components. Add the Advanced Condition as below.

After save and deploy, you can use this policy. For more information, check "NextLabs Control Center Unified UI Edition Administrator’s Guide".

Monday, April 16, 2018

Tips to use Azure Automation for SharePoint online with PowerShell

Azure Automation delivers a cloud-based automation and configuration service that provides consistent management across your Azure and non-Azure environments. It consists of process automation, update management, and configuration features. Azure Automation provides complete control during deployment, operations, and decommissioning of workloads and resources. This article provides a brief overview of Azure Automation and answers some common questions. For more information about the different capabilities, visit the links throughout this overview.

We have a project that requires a back end scheduled job using PowerShell scripts to process many SharePoint online list items and send notifications. We have evaluated the Azure function, Webjob, and Azure automation. Azure automation looks like the best fit. Since this is the first time we are using Azure automation, there are few tips we would like to share so you could jump start this good feature. You should check best practice here first before going forward.

1. How to import packages required for PowerShell?
Since we need to use Microsoft.SharePoint.Client CSOM packages, we need to to use two package dlls listed below.


This is the way to upload the packages to Azure. You need to zip the dlls into a single zip file. I've zipped them into one file named

Then search the "mudule" in the Azure automation account. Click "Add a mudule". Select the zip file you like to add. The module will be uploaded for you to use as listed below.

2. How to refer the dlls uploaded to module?
You can refer the dlls using the following syntax. Please note the uploaded zip file name "Microsoft.SharePoint.Client" in the path.

Add-Type -Path "C:\Modules\User\Microsoft.SharePoint.Client\Microsoft.SharePoint.Client.dll"

3. How to use encrypted password in the scripts?
In most cases Azure automation needs to connect to external system with user name and password. We could save the password in the Azure automation account object "Credentials". You can add a credential as in the below screenshot.

Then you can use the password in the PowerShell like this.

$creds = Get-AutomationPSCredential -Name 'halotest' 
$userName = $creds.UserName
$password = $creds.Password 

$cred = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($userName, $password)
$ctx.Credentials = $cred

4. How to write the standard output?
You need to change "Write-Host" to "Write-Output" to print out to console. One example is listed below.

write-Output "Connection to SPO successfully " $web.Title

5. How to invoke other PowerShell from PowerShell?
You need to create PowerShell runbook for each PowerShell first. Then refer the other PowerShell like listed below. Please note the slash instead of back slash.


Please note the PowerShell refereed need to be published first!

Now, you should have few PowerShell to run and configure as schedule job.

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.