Migrating Sharepoint 2013 on prem to Office365 using Sharegate

Recently I completed a migration project which brought a number of sub-sites within Sharepoint 2013 on-premise to the cloud (Sharepoint Online). We decided to use Sharegate as the primary tool due to the simplistic of it.

Although it might sound as a straightforward process, there are a few things worth to be checked pre and post migration and I have summarized them here. I found it easier to have these information recorded in a spreadsheet with different tabs:

Pre-migration check:

  1. First thing, Get Site Admin access!

    This is the first and foremost important step, get yourself the admin access. It could be a lengthy process especially in a large corporation environment. The best level of access is being granted as the Site Collection Admin for all sites, but sometimes this might not be possible. Hence, getting Site Administrator access is the bare minimum for getting migration to work.

    You will likely be granted Global Admin on the new tenant at most cases, but if not, ask for it!

  2. List down active site collection features

    Whatever feature activated on the source site would need to be activated on the destination site as well. Therefore, we need to record down what have been activated on the source site. If there is any third party feature activated, you will need to liaise with relevant stakeholder in regards to whether it is still required on the new site. If it is, it is highly likely that a separate piece of license is required as the new environment will be a cloud based, rather than on-premise. Take Nintex Workflow for example, Nintex Workflow Online is a separate license comparing to Nintex Workflow 2013.

  3. Segregate the list of sites, inventory analysis

    I found it important to list down all the list of sites you are going to migrate, distinguish if they are site collections or just subsites. What I did was to put each site under a new tab, with all its site contents listed. Next to each lists/ libraries, I have fields for the list type, number of items and comment (if any).

    Go through each of the content, preferably sit down with the site owner and get in details of it. Some useful questions can be asked

  • Is this still relevant? Can it be deleted or skipped for the migration?
  • Is this heavily used? How often does it get accessed?
  • Does this form have custom edit/ new form? Sometimes owners might not even know, so you might have to take extra look by scanning through the forms.
  • Check if pages have custom script with site URL references as this will need to be changed to accommodate the new site url.

It would also be useful to get a comprehensive knowledge of how much storage each site holds. This can help you working out which site has the most content, hence likely to take the longest time during the migration. Sharegate has an inventory reporting tool, which can help but it requires Site Collection Admin access.

  1. Discuss some of the limitations

    Pages library

    Pages library under each site need specific attention, especially if you don’t have site collection admin! Pages which inherit any content type and master page from the parent site will not have these migrated across by Sharegate, meaning these pages will either not be created at the new site, or they will simply show as using default master page. This needs to be communicated and discussed with each owners.

    External Sharing

    External users will not be migrated across to the new site! These are users who won’ be provisioned in the new tenant but still require access to Sharepoint. They will need to be added (invited) manually to a site using their O365 email account or a Microsoft account.

    An O365 account would be whatever account they have been using to get on to their own Sharepoint Online. If they have not had one, they would need to use their Microsoft account, which would be a Hotmail/ Outlook account. Once they have been invited, they would need to response to the email by signing into the portal in order to get provisioned. New SPO site collection will need to have external sharing enabled before external access can happen. For more information, refer to: https://support.office.com/en-us/article/Manage-external-sharing-for-your-SharePoint-Online-environment-C8A462EB-0723-4B0B-8D0A-70FEAFE4BE85

    What can’t Sharegate do?

    Some of the following minor things cannot be migrated to O365:

  • User alerts – user will need to reset their alerts on new site
  • Personal views – user will need to create their personal views again on new site
  • Web part connections – any web part connections will not be preserved

For more, refer: https://support.share-gate.com/hc/en-us/categories/115000076328-Limitations

Performing the migration:

  1. Pick the right time

    Doing the migration at the low activity period would be ideal. User communications should be sent out to inform about the actual date happening as earlier as possible. I tend to stick to middle of the week as that way we still have a couple of days left to solve any issues instead of doing it on Friday or Saturday.

  2. Locking old sites

    During the migration, we do not want any users to be making changes to the old site. If you are migrating site collections, fortunately there’s a way to lock it down, provided you having access to the central admin portal. See https://technet.microsoft.com/en-us/library/cc263238.aspx

    However, if you are migrating sub-sites, there’s no way to lock down a sole sub-site, except changing its site permissions. That also means changing the site permissions risk having all these permissions information lost, so it would be ideal to record these permissions before making any changes. Also, take extra note on lists or libraries with unique permissions, which means they do not inherit site permissions, hence won’t be “locked unless manually changed respectively.

  3. Beware of O365 traffic jam

    Always stick to the Insane mode when running the migration in Sharegate. The Insane mode makes use of the new Offie 365 Migration API which is the fastest way to migrate huge volumes of data to Office365. While it’s been fast to export these data to Office365, I did find a delay in waiting for Office365 to import these into Sharepoint tenant. Sometimes, it could sit there for an hour before continuing with the import. Also, avoid running too many sessions if your VM is not powerful enough.

  4. Delta migration

    The good thing with using Sharegate is that you could do delta migration, which means you only migrate those files which have been modified or added since last migrated. However, it doesn’t handle deletion! If any files have been removed since you last migrated, running a delta sync will not delete these files from the destination end. Therefore, best practice is still delete the list from the destination site and re-create it using the Site Object wizard.

Post-migration check:

Doing the migration at the low activity period would be ideal. User communications should be sent out to inform about the actual date happening as earlier as possible. I tend to stick to middle of the week as that way we still have a couple of days left to solve any issues instead of doing it on Friday or Saturday.

Things to check:

  • Users can still access relevant pages, list and libraries
  • Users can still CRUD files/ items
  • Users can open Office web app (there can be different experience related to authentication when opening Office files, in most cases, users should only get prompted the very first time opening)

Create a PowerApp from SharePoint list

In my last post we explored PowerApps and the associated development tools available like PowerApps desktop studio and PowerApps web studio. Without writing a single line of code we created our first basic three screen PowerApp and were able to perform CURD (create, update, read, delete) operations on our SharePoint data source.

In this, we will try an create a App from SharePoint list. we’ll create an app from a “Roster” SharePoint list.

First, we will see how PowerApps is integrated into SharePoint Online. Second, we will try to customize the app from the basic app created by PowerApp.

Lets get going…

To start here:

  • Login on to our SharePoint Online site.
  • Go to the list, “Roster”in our case.
  • List has columns like Employee Name, Skills, Shift Timings (Choice) , Image (Picture), Hours (Number), Shift Frequency, Overtime (Calculated).

PowerAppRosterList.png

To build an app, click PowerApps and then Create an app. In the right hand pane, enter a name of the app “RosterDetails”, then click Create.

Things to note:

  • The App will open an PowerApp web studio.
  • It will use our SharePoint list as our datasource

PowerAppCreatePowerAppFromList.png

So, after much of hard work!!! Yes I know… I get my Roster App as below, which to be honest looks very funny… and not very useful.

GeneratedApp.png

Lets make it better presentable…

Lets try quickly changing the theme:

  •  From left pane ‘Screens’, select thumbnail view and select ‘BrowseScreen1’

ScreensThumbnail.png

  • Click Home -> Theme and select ‘Coral’ (thats my choice, we can chose any we want…)

ScreensTheme.pngScreensCoralThemeView.png

Lets add more information on browse screen:

  • On the middle section, select browse gallery -> Layouts -> and select Layout as Image, title, subtitle and body
  • We set the values respective to reach property like Title 4 as ‘Title’, Body3 as ‘Hours’ and so on…

ScreensUpdateBrowseView.png

Small Confession to make…

I added the Images in picture library on SharePoint and linked it with my custom list ‘Roster’… but GUESS WHAT… PowerApp didn’t loaded the images.

But, when I linked the Image column with public URL for the image, it works. And yes… I did tried absolute URL but no luck…

So, after updating my custom list, my RosterDetails App look like:

ScreensNewBrowseView.png

Time for updating Detail screen

ScreensDetailsView.png

You know what, I really like this view, but I don’t want to show field Compliance Assest Id and Overtime field looks to be having too many 000000000000000, so we need to fix it.

  • We select the DetailForm on detailscreen
  • Click Layouts on right -> Uncheck the checkbox next to Compliance Assest Id field.
  • The field is no more on the view.

ScreenDetailFormView.png

  • On the same screen press ‘…’ next to Overtime field -> click Advanced options -> click Unlock to change properties

ScreenDetailFormUnlockPropertiesView.png

  • Unlockling the card will make this card as ‘Custom card’, do this will make all the properties of the field editable
  • Click next to label showing the overtime value, then on the right panel, click Text property and we update the function next to Text property from

Parent.Default ——–> Value(Parent.Default)

So, in order to get rid of ‘0’ decimals, we used function Value which converts a string to a number.

ScreenDetailFormFormula.png

Point to note:

  • I tried using formula as Text(Parent.Default,”#.00″), but it doesn’t do anything, but if use the formula as Text(2.0000000,”#.00″) it would give us 2.00
  • ‘[$-en-US]’ the language placeholder can appear anywhere in the custom format but only once. While writing a formula, if we do not provide a language placeholder, the format string is ambiguous from a global standpoint, the authoring tool will automatically insert the language tag for your current language.[$-en-US] is assumed if this placeholder is not present when your app is run.NOTE: In a future version, the syntax of this placeholder may change to avoid confusion with a similar, but different, placeholder supported by Excel.

Time to make changes to Edit Screen

ScreenEditScreen.png

  • Skills can be more then one so, lets change skills to have multi-line input field.

To do this, we just select Edit form -> click Layouts -> click ‘…’ next to Skills -> select Edit multi-line text.

ScreenEditScreenMultiline.png

  • Overtime needs to be same as detail screen

We will follow exactly the same steps as we did for Browse Screen.

  • Remove Compliance Assest Id

We will follow exactly the same steps as we did for Browse Screen.

And, yes we are done!!!

 

Conclusion

We created an PowerApp from SharePoint in no time, updated all the three views with minimum effort and got ourself a fine looking App which displays all the records, add and allow edit of the items in SharePoint.

References

Connect SharePoint Online and SQL Server On-Premises with BCS/SharePoint Apps using Hybrid Connection and WCF Services

SharePoint Online cannot directly connect to on-premises data sources such as SQL Server. A recommended approach is to use Hybrid with SharePoint 2013/2016 but adds an overhead of infrastructure and maintenance costs. Hence to overcome it, I am going to describe in this blog how to use the Azure PaaS workloads and connect to on-premises data sources using BCS.

Using Azure Hybrid Connection (refer this post) and BCS with Azure Web App hosting WCF endpoint, we can now expose on-premises SQL data to SharePoint Online and Cloud by external content types (ECTs) or SharePoint Hosted Apps.

Below are two approaches by which BCS can connect these data sources to SharePoint.
1. Azure Web App hosting WCF Service and External Lists
2. Azure Web App hosting WCF Data Service and Hosted Apps

Azure WCF Service Web App and External Lists
SPOAzureBCSHybrid
Pros: The advantage of using this approach is the reusability of External Content Types (ECT). ECTs can be used across multiple lists and sites in the same site collection. ECTs can also be used for complex associations across multiple types of data.

Cons: Some shortcomings of this approach are:
– Dependency on pass through authentication for users and/or implement custom authentication to authenticate with WCF by passing SQL authentication
– Added development effort because of WCF build and hosting

High-Level Steps:
1. Create a WCF Solution using Visual Studio
2. Use ADO.Net and WCF Service calls to fetch data using web methods. Implement at least two web methods – one to return all items and one to return a specific item
3. Update Web.Config of the WCF service with required configuration for data calls
4. Create an Azure Web App
5. Publish the WCF Service to Azure Web App and get the single wsdl signature from the WCF service
6. Create an External Content Type using SharePoint Designer using the WSDL signature
7. Add GetItems and GetItem finder to ECT
8. Create an External List from ECT

Azure Web App hosting WCF Data Service and Hosted Apps
SPOAzureAppsHybrid
Pros: The advantages of using a WCF Data Service is that the OData method maps directly to the schema of the SQL table which makes it easy to build and maintain. Additionally, using SharePoint hosted apps isolates the CRUD operations from the Host Web decreasing the overhead of external content types and external lists.

Cons: The disadvantage of using this approach is that the data is scoped within the app and cannot be exposed to Host Web components making interaction limited to Web App only. There is a customization requirement to expose and operate on this data in the App Web.

High-Level Steps:
1. Create a WCF service project using Visual Studio
2. Install the EntityFramework Nuget package
3. Add a WCF data service file and implement EntityFrameworkDataService instead of DataService
4. Override the “InitializeService” method as below
5. Add an ADO.Net Entity Data Model project and configure it to fetch data from SQL Tables you want
6. Update Web.config with required configuration for data calls
7. Create an Azure Web App and enable SSL on it
8. Publish the WCF Service to Azure Web App
9. Next create a new SharePoint hosted app solution in Visual Studio
10. In the SharePoint hosted app solution, add an External Content type and select the Azure Web Application hosting the WCF data service as source
11. After the External Content type is created, then create an External List using ECT created above
12. The external list is now added to the Hosted app which can then be referenced in the app default page and app part


Hence in this blog, we have seen the two choices to host BCS connectivity services via Azure PaaS workloads, advantages and disadvantages of each and broad level steps to configure them.

Building my first PowerApp, a basic roster, pulling data from SharePoint Online

What is PowerApps?

PowerApps is a set of services and apps, that enable power users to build line of business application rapidly. It can connect to the cloud services and data sources that we may be already using.

Why PowerApps?

PowerApps gives power user ability to quickly build apps that suit specific needs. They can share apps instantly with Team across the web, tablets, and mobile devices. To list down, few of the advantages of PowerApps are:

  • Simple and fast – capable of producing an app in minutes that pulls in data from Excel or a cloud service.

  • Can be integrated with Microsoft Flow, making it possible to trigger workflows from within apps.

  • Robust and enterprise-grade, so can be used for complex requirements.

What you need to get started?

We can chose from two options:

  • PowerApps Studio for web

  1. Go to the url: https://web.powerapps.com

  2. You need to use your tenant account which would be something like xxx@yyy.onmicrosoft.com

  3. Choose from any of the start option
  • PowerApps Studio for Windows

  1. Go to the url: https://powerapps.microsoft.com/en-us/downloads/

  2. Download the App for windows.

  3. You need to login using your tenant account which would be something like xxx@yyy.onmicrosoft.com

We would need to use web.powerapps.com to configure and manage data connections and on-premises gateways, and to work with the Common Data Service.

Few points to note:

After we create an app, we can administer it in the admin center.

We run apps in a browser from Microsoft Dynamics 365 or by using PowerApps Mobile, which is available for Windows, iOS, and Android devices.

PowerApps components:

  • web.powerapps.com – manage and share the apps we build
  • PowerApps Studio – build powerful apps with easy to use visual tools
  • PowerApps Mobile – run apps on Windows, iOS, and Android devices
  • PowerApps admin center – administer PowerApps environments and other components

PowerApps Studio

PowerApps Studio has three panels and a ribbon, which gives the view same as creating a PowerPoint presentation:

  1. Left navigation bar: shows thumbnail
  2. Middle pane: shows the screen that you’re working on
  3. Right-hand pane: shows options such as layout and data sources
  4. Property drop-down list: where you select the properties that formulas apply to
  5. Formula bar: where you add formulas
  6. Ribbon: where you add controls and customize design elements

PowerAppStudio.png

PowerApps Mobile

PowerApps Mobile for Windows, iOS, and Android provides an environment where instead of going to separate app stores, we stay in PowerApps and have access to all the apps that we have created and that others have shared with us.

Admin center

The PowerApps admin center is the centralized place to administer PowerApps for an organization. This is where we define different environments, data connections, and other elements. The admin center is also where we create Common Data Service databases, and manage permissions and data policies.

Lets create our first App

Ok, now time to create our first PowerApp… so, lets fire up PowerApps desktop studio. So, for this demo, we will use data source as SharePoint and layout as phone layout

  • Click New -> SharePoint -> Phone layout

PowerAppStudioDesktopStart.png

Connect to a data source

On the next screen we need to specify the connection for SharePoint, where we will enter SharePoint Url and click Go.

PowerAppSourceConnection.png

On the next screen, we can select the list on the specified SharePoint site and select the respective list, in our example we will chose the list “Roster” and click Connectafter which PowerApps then start generating the app.

powerappsourceurl2.png

The generated app is always based on a single list and we can add more data to the app later. An app with default screen is built for us, which we can see in action by click play icon Start app preview arrow.

powerappsharepointbaseapp1.png

Our app in action

MyFirstPowerApp.gif

Our three screen roster app opens in PowerApps Studio. All apps generated from data have the same set of screens:

  • The browse screen: On this, we can browse, sort, filter, and refresh the data pulled in from the list, as well as add items by clicking the (+) icon.
  • The details screen: On this, we view detail about an item, and can choose to delete or edit the item.
  • The edit/create screen: On this, we edit an existing item or create a new one.

Conclusion

So, in this post, we tried exploring PowerApps and the development tool available like PowerApps desktop studio and PowerApps web studio. Without writing a single line of code we created our first basic three screen PowerApp and were able to perform CURD (create, update, read, delete) operations on our SharePoint data source.

In the next post we will try creating PowerApp from SharePoint list.

Resolving “User not found” issue while assigning permissions using SharePoint CSOM

I was recently working on a SharePoint Online project where we were trying to automate library creation and provide required permissions on those libraries. We had an issue while modifying permissions with CSOM code on SharePoint libraries when the Created By user had left the company.

In this post I will outline the cause and the resolution as there was no online reference for resolving this error.

Issue: The CSOM code was throwing an error “User not found” even when creating a User object from web.EnsureUser() method.

Cause: The User object returned by web.EnsureUser() method was empty but not null and hence couldn’t be instantiated while adding after breaking permissions.

Resolution: The resolution to this issue was to explicitly load of the user object, then catch the exception while loading, and set a flag to false which could be later be checked to prevent the add method from erroring out. Yeah, this is a roundabout way of overcoming the issue but it works. Hopefully it will save you some hours.

Below is the code that could be used to do that.

Azure Functions Cold Start Workaround

Intro

I love Azure Functions. So much power for so little effort or cost. The only downside is that the consumption model that keeps the cost so dirt-cheap means that unless you are using your Function constantly (in which case, you might be better off with the non-consumption options anyway), you will often be hit with a long delay as your Function wakes up from hibernation.

So very cold…

This isn’t a big deal if you are dealing with a fire and forget queue trigger scenario, but if you have web app that is calling the HTTP trigger and you need to wait for the Function to do it’s job before responding with a 200 OK… that’s a long wait (well over 15 seconds in my experience with a PowerShell function that loads a bunch of modules).

Now, the blunt way to mitigate this (as suggested by some in github issues on the subject) is to set up a timer function in the same Function App to run every 5 minutes to keep things warm. This to me seems like a wasteful and potentially expensive approach. For my use-case, there was a better way that would work.

The Classic CRUD Use-case

Here’s my use case: I’m building some custom SharePoint forms for a customer and using my preferred JS framework, good old Angular 1.x. Don’t believe the hype around the newer frameworks, ng1 still gets the job done with no performance problems at the scale I’m dealing with in SharePoint. It also comes with a very strong ecosystem of libraries to support doing amazing things in the browser. But that’s a topic for another blog.

Anyway, the only thing I couldn’t do effectively on the client-side was break permissions on the list item created using the form and secure it to the creator and some other users (eg. their manager, etc). You need elevated permissions for that. I called on the awesome power of the PnP PowerShell library (specifically the Set-PnPListItemPermission cmdlet) to do this and wrapped it in a PowerShell Azure Function:

Pretty simple. Nice and clean – gets the job done. My Angular service calls this right after saving the item based on the form input by the user. If the Azure Function is starting from cold, then that adds an extra 20 seconds to save operation. Unacceptable and avoidable.

A more nuanced warmup for those who can…

This seemed like such an obvious solution once I hit on it – but I hadn’t thought of it before. When the form is first opened, it’s pretty reasonable to assume that (unless the form is a monster), the user should be hitting that submit/save button in under 5 mins. So that’s when we hit our function with a modified HTTP payload of ‘WARMUP’.

Just a simple ‘If’ statement to bypass the function if the ‘WARMUP’ payload is detected. The Function immediately responds with a 200. We ignore that – this is effectively fire-and-forget. Yes, this would be even simpler if we had a separate warmup Function that did absolutely nothing except warm up the Functions App, but I like that this ensures that my dependent PnP dlls (in the ‘modules’ folder of my Function) have been fired up on the current instance before I hit the function for real. Maybe it makes no difference. Don’t really care – this is simple enough anyway.

Here’s the Angular code that calls the Function (both as a warmup and for real):

Anyway, nothing revolutionary here, I know. But I hadn’t come across this approach before, so I thought it was worth writing up as it suits this standard CRUD forms over data scenario so nicely.

Till next time!

Global Navigation and Branding for Modern Site using SharePoint Framework Extensions

Last month at the Microsoft Ignite 2017, SharePoint Framework Extensions became GA. It gave us whole new capabilities how we can customize Modern Team sites and Communication sites.

Even though there are lots of PnP examples on SPFx extensions, while presenting at Office 365 Bootcamp, Melbourne and taking hands-on lab, I realised not many people are aware about the new capabilities that SPFx extensions provide. One of the burning question we often get from clients, if we can have custom header, footer and global navigation in the modern sites and the answer is YES. Here is an example where Global Navigation has been derived from Managed Metadata:

Communication Site with header and footer:

Modern Team Site with header and footer (same navigation):

With the latest Yeoman SharePoint generator, along with the SPFx Web Part now we have options to create extensions:

To create header and footer for the modern site, we need to select the Application Customizer extension.

After the solution has been created, one noticeable difference is TenantGlobalNavBarApplicationCustomizer is extending from BaseApplicationCustomizer and not BaseClientSideWebPart.

export default class TenantGlobalNavBarApplicationCustomizer
extends BaseApplicationCustomizer

Basic Header and Footer

Now to create a very basic Application Customizer with header/footer, make sure to import the React, ReactDom, PlaceholderContent and PlaceholderName:

import * as ReactDom from 'react-dom';
import * as React from 'react';
import {
  BaseApplicationCustomizer,
  PlaceholderContent,
  PlaceholderName
} from '@microsoft/sp-application-base';

In the onInit() function, the top(header) and the bottom (footer) placeholders need to be created:

const topPlaceholder =  this.context.placeholderProvider.tryCreateContent(PlaceholderName.Top);
const bottomPlaceholder =  this.context.placeholderProvider.tryCreateContent(PlaceholderName.Bottom);

Create the appropriate elements for the header and footer:

const topElement =  React.createElement('h1', {}, 'This is Header');
const bottomElement =   React.createElement('h1', {}, 'This is Footer');

Those elements can be render within placeholder domElement:

ReactDom.render(topElement, topPlaceholder.domElement);
ReactDom.render(bottomElement, bottomPlaceholder.domElement);

If you now run the solution:

  • gulp serve –nobrowser
  • Copy the id from src\extensions\.manifest.json file e.g. “7650cbbb-688f-4c62-b5e3-5b3781413223”
  • Open a modern site and append the following URL (change the id as per your solution):
    ?loadSPFX=true&debugManifestsFile=https://localhost:4321/temp/manifests.js&customActions={“7650cbbb-688f-4c62-b5e3-5b3781413223”:{“location”:”ClientSideExtension.ApplicationCustomizer”}}

The above 6 lines code will give the following outcome:

Managed Metadata Navigation

Now back to the first example. That solution has been copied from SPFx Sample and has been updated.

To get the above header and footer:

Go to command line and run

  • npm i
  • gulp serve –nobrowser
  • To see the code running, go to the SharePoint Online Modern site and append the following with the site URL:

    ?loadSPFX=true&debugManifestsFile=https://localhost:4321/temp/manifests.js&customActions={“b1efedb9-b371-4f5c-a90f-3742d1842cf3”:{“location”:”ClientSideExtension.ApplicationCustomizer”,”properties”:{“TopMenuTermSet”:”TopNav”,”BottomMenuTermSet”:”Footer”}}}

Deployment

Create an Office 365 CDN

Update config\write-manifests.json file “cdnBasePath” to the new location. E.g.

"cdnBasePath":"https://publiccdn.sharepointonline.com/<YourTenantName>.sharepoint.com//<YourSiteName>//<YourLibName>/<FoldeNameifAny>"

In the command line, run

  • gulp bundle –ship
  • gulp package-solution –ship

and upload all artefacts from \temp\deploy\ to the CDN location

Upload \sharepoint\solution.sppkg to the App Library

Go to the Modern Site > Site Content > New > App > select and add the app:

Hopefully this post has given an overview how to implement SPFx Application Customizers. There are many samples available in the GitHub SharePoint.

Use Azure Hybrid Connections to get on-premises data from SQL to SharePoint Online

Azure Hybrid Connections are an easier and less complicated way to connect cloud applications with on-premises SQL data. This provides great extensibility options for SharePoint Online such as,

  1. Provider Hosted Apps hosted in Azure
  2. Business Data Connectivity using WCF services hosted in Azure
  3. SharePoint Hosted Apps using BCS external sources.

In this blog, I will illustrate the steps to configure Azure Hybrid Connections. In a nutshell, the diagram below outlines the data flow in Hybrid connections.

AzureHybridConnection1_Asish

Firstly, in the on-premises SQL server, if you have a named instance then assign a static port to it and expose it through the firewall. If SQL is installed on the default instance, then make sure 1433 is exposed outside the firewall.

Next, log into the Azure Portal and create a Resource Group, add an Azure Web App, and then add a Hybrid Connection from Networking section
(Azure Web App -> Networking -> Configure Hybrid Connection)

AzureHybridConnection2_Asish

Note:Hybrid connections can also be added by other resources such as Azure Functions or other apps that can be tied to an App Service plan.
Note:The number of Hybrid Connections are limited by the type of App Service Plan.  A brief table of allowed connections is below. It is important to note that the Free App Service Plan doesn’t have any Hybrid Connections. It is shown in the table below
Pricing Plan Number of hybrid connections usable in the plan
Basic 5
Standard 25
Premium 200
Isolated 200

Next, add a New Hybrid Connection. In Endpoint Host, enter the fully qualified name of your SQL server along with domain. In the port field as in the below screenshot, provide the details of the SQL server port the instance is exposed at.

Note: No need to qualify the details of instance as server\instance in the endpoint host field as the application code will have to specify the connection details in it. The Hybrid connection will only need to just know the endpoint.
Note: You could also select existing hybrid connections from other resource groups.

AzureHybridConnection3_Asish

After the Hybrid connection is created, it will show up in the Azure Portal as in below screenshot

AzureHybridConnection4_Asish

Next, download the Connection Manager using Download Connection Manager. It is basically a download with pre-configured Azure subscription details which, when installed in an on-premises system environment (preferably in the same Data center as the SQL Server), acts as a listener to Azure Web App requests.

After installing the Hybrid Connection UI manager, connect to the Azure Subscription account to find the available hybrid connections. After selecting the connection, if the listener can connect to SQL it would show as Connection Successful.

AzureHybridConnection5_Asish

After the connection is successful, in the Azure Portal, the number of listeners will show as 1 and connection status to Connected.

In this blog, we saw how we could create Azure Hybrid connections to connect an on premises SQL with an Azure App Service. In the next blog, we will discuss the steps to consume this connection and connect SPO with the SQL data sources.

Restoring deleted OneDrive sites in Office365

A customer has requested whether it was possible to restore a OneDrive site that had been deleted when the user’s account was marked for deletion in AD. After a bit of research, I was able to restore the site back and retrieved the files (luckily it was deleted less than 30 days ago).

Read More

Integrating Yammer data within SharePoint web-part using REST API

Background

We were developing a SharePoint application for one of our client and have some web-parts that had to retrieve data from Yammer. As we were developing on SharePoint Online (SPO) using a popular SharePoint Framework (SPFx), so for the most part of our engagement we were developing using a client-side library named React to deliver what is required from us.

In order for us to integrate client’s Yammer data into our web-parts, we were using JavaScript SDK provided by Yammer.

Scenario

We were having around 7-8 different calls to Yammer API in different web-parts to extract data from Yammer on behalf of a logged-in user. Against each API call, a user has to be authenticated before a call to Yammer API has been made and this has to be done without the user being redirected to Yammer for login or presented with a popup or a button to log in first.

If you follow Yammer’s JavaScript SDK instructions, we will not be meeting our client’s requirement of not asking the user to go Yammer first (as this will change their user flow) or a pop-up with login/sign-in dialog.

Approach

After looking on the internet to fulfill above requirements, I could not find anything that serves us. I have found the closest match in PnP sample but it only works if a client has already consented to your Yammer app before. In our case, this isn’t possible as many users will be accessing SharePoint home page for the first them and have never accessed Yammer before.

What we have done is, let our API login calls break into two groups. Randomly one of the calls was chosen to let the user login to Yammer and get access token in the background and cache it with Yammer API and make other API login calls to wait for the first login and then use Yammer API to log in.

Step-1

This function will use standard Yammer API to check login status if successful then it will proceed with issuing API data retrieval calls, but if could not log in the first time; it will wait and check again after every 2 sec until it times out after 30 sec.

  public static loginToYammer(callback: Function, requestLogin = true) {
    SPComponentLoader.loadScript('https://assets.yammer.com/assets/platform_js_sdk.js', { globalExportsName: "yam"}).then(() => {
      const yam = window["yam"];

        yam.getLoginStatus((FirstloginStatusResponse) => {
        if (FirstloginStatusResponse.authResponse) {
          callback(yam);
        }
        else {
          let timerId = setInterval(()=>{
              yam.getLoginStatus((SecondloginStatusResponse) => {
                if (SecondloginStatusResponse.authResponse) {
                  clearInterval(timerId);
                  callback(yam);
                }
              });
          }, 2000);

          setTimeout(() => {
              yam.getLoginStatus((TimeOutloginStatusResponse) => {
                if (TimeOutloginStatusResponse.authResponse) {
                  clearInterval(timerId);
                }
                else {
                  console.error("iFrame - user could not log in to Yammer even after waiting");
                }
              });
          }, 30000);
        }
      });
    });
  }

Step-2

This method will again use the standard Yammer API to check login status; then tries to log in user in the background using an iframe approach as called out in PnP sample; if that approach didn’t work either then it will redirect user to Smart URL in the same window to get user consent for Yammer app with a redirect URI set to home page of  your SharePoint where web-parts with Yammer API are hosted.

  public static logonToYammer(callback: Function, requestLogin = true) {
    SPComponentLoader.loadScript('https://assets.yammer.com/assets/platform_js_sdk.js', { globalExportsName: "yam"}).then(() => {
      const yam = window["yam"];

      yam.getLoginStatus((loginStatusResponse) => {
        if (loginStatusResponse.authResponse) {
          callback(yam);
        }
        else if (requestLogin) {
          this._iframeAuthentication()
              .then((res) => {
                callback(yam);
              })
              .catch((e) => {
                window.location.href="https://www.yammer.com/[your-yammer-network-name]/oauth2/authorize?client_id=[your-yammer-app-client-id]&response_type=token&redirect_uri=[your-sharepoint-home-page-url]";
                console.error("iFrame - user could not log in to Yammer due to error. " + e);
              });
        } else {
          console.error("iFrame - it was not called and user could not log in to Yammer");
        }
      });
    });
  }

The function _iframeAuthentication is copied from PnP sample with some modifications to fit our needs as per the client requirements were developing against.


  private static _iframeAuthentication(): Promise<any> {
      let yam = window["yam"];
      let clientId: string = "[your-yammer-app-client-id]";
      let redirectUri: string = "[your-sharepoint-home-page-url]";
      let domainName: string = "[your-yammer-network-name]";

      return new Promise((resolve, reject) => {
        let iframeId: string = "authIframe";
        let element: HTMLIFrameElement = document.createElement("iframe");

        element.setAttribute("id", iframeId);
        element.setAttribute("style", "display:none");
        document.body.appendChild(element);

        element.addEventListener("load", _ => {
            try {
                let elem: HTMLIFrameElement = document.getElementById(iframeId) as HTMLIFrameElement;
                let token: string = elem.contentWindow.location.hash.split("=")[1];
                yam.platform.setAuthToken(token);
                yam.getLoginStatus((res: any) => {
                    if (res.authResponse) {
                        resolve(res);
                    } else {
                        reject(res);
                    }
                });
            } catch (ex) {
                reject(ex);
            }
        });

        let queryString: string = `client_id=${clientId}&response_type=token&redirect_uri=${redirectUri}`;

       let url: string = `https://www.yammer.com/${domainName}/oauth2/authorize?${queryString}`;

        element.src = url;
      });
    }

Conclusion

This resulted in authenticating Office 365 tenant user within the same window of SharePoint home page with the help of an iframe [case: the user had consented Yammer app before] or getting a Yammer app consent from the Office 365 tenant user without being redirected to Yammer to do OAuth based authentication [case: the user is accessing Yammer integrated web-parts for the 1st time].

We do hope future releases of Yammer API will cater seamless integration among O365 products without having to go through a hassle to get access tokens in a way described in this post.