Friday, April 9, 2021

How to test certificate-based authentication in Azure functions for Sharepoint Online on local PC

If you develop Azure function you probably often run them locally on dev PC rather than in Azure. It simplifies debugging and development. In this post I will show how to test certificate-based authentication for Sharepoint Online in Azure functions running locally. First of all we need to register AAD app in Azure portal and grant it Sharepoint permissions:

Don't forget to grant Admin consent after adding permissions.

After that generate self-signed certificate using Create-SelfSignedCertificate.ps1 script from here: Granting access via Azure AD App-Only:

.\Create-SelfSignedCertificate.ps1 -CommonName "MyCertificate" -StartDate 2021-04-08 -EndDate 2031-04-09

It will generate 2 files:

  • private key: .pfx
  • public key: .cer

Go to registered AAD app > Certificates & secrets > Certificates > Upload certificate and upload generated .cer file. After upload copy certificate thumbprint - it will be needed for Azure functions below.

In Azure function certificate-based authentication for Sharepoint Online can be done by the following code (using OfficeDevPnP.Core):

using (var authMngr = new OfficeDevPnP.Core.AuthenticationManager())
{
    using (var ctx = authMngr.GetAzureADAppOnlyAuthenticatedContext(siteUrl, clientId, tenant, StoreName.My, StoreLocation.CurrentUser, certificateThumbprint))
    {
        ...
    }
}

Here we specified clientId of our AAD app, copied certificate thumbprint and tenant in the form {tenant}.onmicrosoft.com.

Before to run it we need to perform one extra step: install certificate to local PC certificates store. It can be done by double click on .pfx file. After that Windows will open Certificate import wizard:


Since our code is using Personal store use Store Location = Current User. Then specify password and import your certificate to the store. You may check that certificate is installed properly by opening MMC console > Add/Remove snapin > Certificates. Imported certificate should appear under Personal > Certificates:

After that you will be able to run Azure functions locally which communicate with Sharepoint Online using certificate-based authentication.

Tuesday, March 30, 2021

Create new site collections in Sharepoint Online using app-only permissions with certificate-based authentication (without appinv.aspx and client secret)

In the past if we needed to create site collections in Sharepoint Online we registered new SP app on appregnew.aspx page and granted Full control permissions on tenant level using appinv.aspx (alternatively instead of appregnew.aspx we could register new AAD app in Azure portal and then use it's clientId on appinv.aspx). But nowadays it is not preferred way to achieve this goal. In this article I will show ho to create new site collections using app-only permissions with certificate-based authentication (without appinv.aspx and client secret).

First of all we need to register new AAD app in Azure portal and grant Sites.FullControl.All API permissions (after granting permissions admin consent will be also needed):


Now for testing purposes let's try to create Modern Communication site using clientId and clientSecret:

using (var ctx = new OfficeDevPnP.Core.AuthenticationManager().GetAppOnlyAuthenticatedContext("https://{tenant}-admin.sharepoint.com", "{clientId}", "{clientSecret}"))
{
    ctx.RequestTimeout = Timeout.Infinite;
    var tenant = new Tenant(ctx);
    var properties = new SiteCreationProperties
    {
        Url = "https://{tenant}.sharepoint.com/sites/{siteUrl}",
        Lcid = 1033,
        TimeZoneId = 59,
        Owner = "{username}@{tenant}.onmicrosoft.com",
        Title = "{siteTitle}",
        Template = "SITEPAGEPUBLISHING#0",
        StorageMaximumLevel = 0,
        StorageWarningLevel = 0
    };

    var op = tenant.CreateSite(properties);
    ctx.Load(tenant);
    ctx.Load(op);
    ctx.ExecuteQueryRetry();

    ctx.Load(op, i => i.IsComplete);
    ctx.ExecuteQueryRetry();

    while (!op.IsComplete)
    {
        Thread.Sleep(30000);
        op.RefreshLoad();
        ctx.ExecuteQueryRetry();
        Console.Write(".");
    }
    Console.WriteLine("Site is created");
}

If we will try to run this code we will get Microsoft.SharePoint.Client.ServerUnauthorizedAccessException:

Access denied. You do not have permission to perform this action or access this resource.

Now let's try to create self-signed certificate like described here: Granting access via Azure AD App-Only. Then upload public key (.cer file) to our AAD app:


After that let's change C# example shown above to use certificate private key (.pfx file) and password for authentication. Code for creating site collection will remain the same:

using (var ctx = new OfficeDevPnP.Core.AuthenticationManager().GetAzureADAppOnlyAuthenticatedContext(
    "https://{tenant}-admin.sharepoint.com",
    "{clientId}",
    "{tenant}.onmicrosoft.com",
    @"C:\{certFileName}.pfx",
    "{certPassword}"))
{
    ctx.RequestTimeout = Timeout.Infinite;
    var tenant = new Tenant(ctx);
    var properties = new SiteCreationProperties
    {
        Url = "https://{tenant}.sharepoint.com/sites/{siteUrl}",
        Lcid = 1033,
        TimeZoneId = 59,
        Owner = "{username}@{tenant}.onmicrosoft.com",
        Title = "{siteTitle}",
        Template = "SITEPAGEPUBLISHING#0",
        StorageMaximumLevel = 0,
        StorageWarningLevel = 0
    };

    var op = tenant.CreateSite(properties);
    ctx.Load(tenant);
    ctx.Load(op);
    ctx.ExecuteQueryRetry();

    ctx.Load(op, i => i.IsComplete);
    ctx.ExecuteQueryRetry();

    while (!op.IsComplete)
    {
        Thread.Sleep(30000);
        op.RefreshLoad();
        ctx.ExecuteQueryRetry();
        Console.Write(".");
    }
    Console.WriteLine("Site is created");
}

This code will work and site collection will be successfully created. This is how you may create site collections in Sharepoint Online using app-only permissions and certificate-based authentication.

Tuesday, March 16, 2021

Attach onClick handler on div dynamically in runtime using css class selector in React and Fluent UI

Sometimes in React we may need to attach javascript handler on html element using "old" way which closer to DOM manipulation which we did in pure javascript code. I.e. if you can't get component reference because of some reason and the only thing you have is css class of html element in the DOM you may still need to work with it via DOM manipulations.

As example we will use Search element from Fluent UI. It renders magnifier icon internally and it is quite hard to get component ref on it. Highlighted magnifier is rendered as div with "ms-SearchBox-iconContainer" css class:

Imagine that we want to attach onClick handler on magnifier icon so users will be able to click it and get search results. Here is how it can be achivied:

private _onClick(self: Search) {
  // search logic
}

public componentDidMount() {
  let node = document.querySelector(".ms-SearchBox-iconContainer");
  if (node) {
    node.addEventListener("click", e => this._onClick(this));
  }
}

public componentWillUnmount() {
  let node = document.querySelector(".ms-SearchBox-iconContainer");
  if (node) {
    node.removeEventListener("onClick", e => this._onClick(this));
  }
}

So we add handler in componentDidMount and remove it componentWillUnmount. Inside these methods we use document.querySelector() for finding actual div to which we need to attach onClick handler. Note that we pass this as parameter of _onClick since this inside this method will point to div html element but not to search component itself.

Wednesday, March 10, 2021

Get Sharepoint site collection id (SPSite.ID) and web id (SPWeb.ID) from followed sites returned from REST API

In Sharepoint we can fetch all followed sites for the current user by the following REST API endpoint:

http://example.com/_api/social.following/my/Followed(types=4)

(instead of http://example.com you should use url of your SP site). It will return collection of site objects which will contain such properties as name, url, etc. However often we need to know also site collection id (SPSite.ID) and web id (SPWeb.ID). We can of course go through all returned sites and fetch their ids by separate JSOM/CSOM calls but it will affect performance (it is classic n+1 problem when we at first get list of items (1st call) and then for each item in the list make separate API call (n calls)).

Fortunately it is possible to get these ids right from REST API response. There is one strange field called "id" which looks like this:

"Id": "8.b4af2aa5fb834daa87aa9fb4155abd7d.b14bd3b3d6084dadb0fc7b79679fc767.
b4af2aa5fb834daa87aa9fb4155abd7d.00000000000000000000000000000000",

So there are several strings divided by dot. If we will check site and web id we will see that second string looks like SPSite.ID and 3rd string like SPWeb.ID:


The only difference is that they don't contain dashes. But it is quite easy do add them by ourselves in the code:

id.substr(0, 8) + "-" + id.substr(8, 4) + "-" + id.substr(12, 4) + "-" + id.substr(16, 4) + "-" + id.substr(20)

Using this approach we can get ids of Sharepoint site collections and web sites directly from REST API response.

Thursday, February 25, 2021

Fetch Sharepoint Online sites which are not associated with O365 groups via Sharepoint Search KQL

In the previous article I showed how we can use Search API in order to fetch sites which are associated with O365 groups:

(contentclass:STS_Site OR contentclass:STS_Web) GroupId<>""

But what if we need to get opposite result: fetch only those sites which are not associated with O365 groups (sites without groups). Our knowledge about GroupId indexed property bag property will help also here since we need to fetch those sites which don't have any value in GroupId property.

First (naive) attempt to do that would be trying something like that:

(contentclass:STS_Site OR contentclass:STS_Web) GroupId:""

However it won't work because KQL supports not equal operation with empty string but doesn't support equal to empty string operation. My colleague Juha Alhojoki pointed me to the original trick which allows to do that: Check Not Null condition in Keyword Query Language for SharePoint Search. So idea is that if we know possible start letters for managed property (remember that KQL supports prefix matching but doesn't support suffix matching, i.e. we may search by "StartsWith" operator but not with "EndsWith") we may enumerate them all and then apply NOT operator to the final condition.

In our example GroupId contains guid i.e. it may start with Latin alphabet symbols and digits. So we will have the following condition to fetching sites which are not associated with O365 groups:

(contentclass:STS_Site OR contentclass:STS_Web) NOT(GroupId:a* OR GroupId:b* OR GroupId:c* OR GroupId:d* OR GroupId:e* OR GroupId:f* OR GroupId:g* OR GroupId:h* OR GroupId:i* OR GroupId:j* OR GroupId:k* OR GroupId:l* OR GroupId:m* OR GroupId:n* OR GroupId:o* OR GroupId:p* OR GroupId:q* OR GroupId:r* OR GroupId:s* OR GroupId:t* OR GroupId:u* OR GroupId:v* OR GroupId:w* OR GroupId:x* OR GroupId:y* OR GroupId:z* OR GroupId:0* OR GroupId:1* OR GroupId:2* OR GroupId:3* OR GroupId:4* OR GroupId:5* OR GroupId:6* OR GroupId:7* OR GroupId:8* OR GroupId:9*)

What we did here is enumerated all Latin symbols and digits and applied NOT operator to exclude sites which match enumerated criterias.

Thursday, February 18, 2021

Fetch Sharepoint Online sites associated with O365 groups via Sharepoint Search KQL

As you probably know O365 groups have associated Sharepoint Online site behind. It may be needed to fetch only those sites which have associated O365 group via Sharepoint Search and KQL. In this post I will show how to do that and in the future post I will describe how to fetch only those sites which are not associated with O365 groups.

Modern Team sites which are associated with O365 group are created using Groups template. I.e. in order to get all sites associated with groups we may use the following KQL query:

(contentclass:STS_Site OR contentclass:STS_Web) WebTemplate:GROUP

It will work however there may be problem with old sites which were "groupified". I.e. regular site could be connected to O365 site - this procedure is called groupifying (see Connect to a Microsoft 365 group). Above query won't return such sites.

Another approach is based on the fact that groupified sites contain special property bag properties which contain information about connected group:

  • GroupAlias
  • GroupDocumentsListId
  • GroupDocumentsUrl
  • GroupId
  • GroupType

By default custom property bag properties are not searchable but they can be made searchable by adding them to Indexed Property Bag which can be done via PowerShell:

Connect-PnPOnline -Url "http://{tenant}.sharepoint.com"
Set-PnPPropertyBagValue -Key "customProperty" -Value "foo" -Indexed  

Here is good article which explains this topic: What Are Indexed Property Bags And How To Use Them For SharePoint Site.

The good thing is that at least GroupId looks like indexed by default. This gives us possibility to use another KQL for getting sites connected with O365 groups:

(contentclass:STS_Site OR contentclass:STS_Web) GroupId<>""

This query will work also with groupified sites.

Update 2021-02-25: see also article which shows how to fetch sites which are not associated with O365 groups: Fetch Sharepoint Online sites which are not associated with O365 groups via Sharepoint Search KQL.

Wednesday, February 17, 2021

Get list items from Sharepoint using lists.asmx web service

In one of my previous articles I showed how to get authentication cookies (FedAuth) from Sharepoint FBA using authentication.asmx web service (see Authenticate in Sharepoint on-prem FBA site via OTB /_vti_bin/Authentication.asmx web service). In this post I will show how to use these cookies and get list items from Sharepoint list using standard lists.asmx web service.

It can be done using the following code:

Cookie authCookies = ...;
string listTitle = ...;
string soapBody =
	"<soap:Envelope xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' xmlns:xsd='http://www.w3.org/2001/XMLSchema' xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'>" +
	"  <soap:Body>" +
	"    <GetListItems xmlns='http://schemas.microsoft.com/sharepoint/soap/'>" +
	"      <listName>" + listTitle + "</listName>" +
	"    </GetListItems>" +
	"  </soap:Body>" +
	"</soap:Envelope>";
var cookies = new CookieContainer();
cookies.Add(authCookies);
var handler = new HttpClientHandler();
handler.CookieContainer = cookies;
using (var httpClient = new HttpClient(handler))
{
	var req = new HttpRequestMessage(HttpMethod.Post, "http://example.com/_vti_bin/lists.asmx")
	{
		Content = new StringContent(soapBody, Encoding.UTF8, "text/xml")
	};
	var res = httpClient.SendAsync(req).GetAwaiter().GetResult();
	string result
	if (res.StatusCode == HttpStatusCode.OK)
	{
		result = res.Content.ReadAsStringAsync().Result;
	}
}

Here we use auth cookies from previous article. We again create special SOAP body where pass list title. In this example we will get all list items but it is also possible to fetch list items which match CAML query - for that you will need to use quert, viewFieldsm rowLimit and queryOptions tags in SOAP body. For more information about these properties refer to documentation of lists.asmx web service. Id everything was done correctly it will return you list items from specified lists in SOAP format.