Part 1: Caching Credentials in an Azure SQL Database for a SharePoint Provider Hosted App

Ostrich no more

So, unless you’ve been living in a cave for the past couple of years this newfangled cloud app model thing is unlikely to have escaped your attention.  Maybe, like me, you’ve been an ostrich burring your head the sands of full trust farm solutions hoping that this thing is just going to blow over.  Hey I took that approach for Silverlight and that worked out ok!

The problem is that I have a nasty feeling that the Cloud App Model isn’t just going to go away and so it’s a case of adapt, die or run away.  In January I decided that I could hide from this no longer and embarked on a journey to take my first commercial app to the SharePoint App Store (you can find the fruits of my labour here in case anyone is interested).  My rationale was that in order to do that then I would simply have to learn stuff along the way and learn stuff I did.

The Unexpected Journey

I decided to share the journey and so embarked on writing a soup to nuts travelogue on my blog. This post is the first part of a not too sexy but nevertheless important side street and covers how to cache credentials pulled from the Azure Access Control Service (ACS).  Why would you need to do that I hear you cry, well read on and I shall explain.

However, just before we get started I should explain that my preferred route to the SharePoint App Store is via the Provider Hosted App model using an Azure Web App as the back end.  Using the Provider Hosted Approach means that I can leverage the full strength double expresso of server side managed code (albeit through a client object model CSOM).  I’ve tried the skinny latte of SharePoint Hosted Apps with the seemly endless stream of JavaScript libraries and decided that it’s not for me.

This is a decision that should not be taken lightly as the Provider Hosted App approach is not without its downsides.  The most obvious of which is that it now up to the developer (me) to provide the infrastructure on which the remote code will run because it for sure can’t run on SharePoint anymore.  Now this means that in an O365 environment I now have 3 touch points, namely:

• The user

• The SharePoint host site running on an on premise farm or O365 tenancy somewhere

• My provider hosted app running somewhere else – in Azure in my case

Actually there are 4 touch points, we mustn’t forget the ACS.

Mmm, 4 touch points which could in theory be in 4 different corners of the globe means we have to all of a sudden take performance very, very seriously if we are going to deliver anything that can even be used in interactive way – and hence the need for caching.

In this post I shall just cover the plumbing of how to provision an Azure Web App and use it as a provider host site.  In part 2, I’ll explain how to use an Azure SQL database to persist ACS credentials.

Is the pain going to be worth the gain?

Now, I have worked with CSOM before and know that establishing a client context can be quite an expensive operation (in that it can take a while).  If you’ve ever worked with CSOM then you will know that can’t do anything until you have bagged a client context.  However, in those scenarios my code was running in close proximity to the SharePoint deployment and spinning for a couple of hundred milliseconds was not a big deal. 

But now we’re on a world stage and close proximity is almost certainly not the norm and so the first thing we need to do is establish what sort of latency we might have to deal with.  Then, when we implement a credential caching solution we will have something to benchmark against.  If credential caching saves us only 100 milliseconds in a page load then it’s probably not worth the hassle but if it can save us a second or more then it most certainly is worth doing.

But how do we grab these metrics and how do we set up the environment to give us a realistic latency scenario.

Crude but effective

The way I chose to gather so metrics is basic to say the least but I would argue that it’s effective enough for our purpose, which is just to see how long it takes to grab a client context and run a simple one line query to load up the SharePoint host web object and output its title.

And do you know what, if you use the standard out-of-the-box-no-code-added Provider Hosted App template given to us as a start point when creating a solution in Visual Studio 2013 that’s more or less what we get given.  I just created a new project in Visual Studio called CacheTest as shown below:

Now, I’m assuming here that you have access to a developer site in an O365 somewhere and you provide URL to that site in the “New app for SharePoint” dialog in place of my redacted screenshot.

In the next page we want a Web Forms application:

And because we are considering the App Store here then we must use the ACS for our authentication provider.  High-trust S2S certification base solutions simply aren’t supported in the App Store.

Clicking finish will spit us out a plain vanilla solution consisting of 2 projects, namely:

• The SharePoint hosted declarative project (the bit that gets in installed on your customer’s SharePoint deployment

• The App Web which we can use for test and debugging

Looking at the App Web project (CacheTestWeb) we can see that Microsoft of kindly provided us with a Default.aspx page, thrown in jQuery (always useful) and added a bag of helper classes defined in couple of code files (TokenHelper.cs and SharePointContext.cs).

 If we look at the code behind the Default.aspx page we can see that on page load the code that Microsoft gives us uses the GetSharePointContext method in the SharePointContextProvider class to establish the client context, load up the title of the host web site, execute the query and pump out the title text to the page.

protected void Page_Load(object sender, EventArgs e)

{

            // The following code gets the client context and Title property by using TokenHelper.

            // To access other properties, the app may need to request permissions on the host web.

            var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);

            using (var clientContext = spContext.CreateUserClientContextForSPHost())

            {

                clientContext.Load(clientContext.Web, web => web.Title);

                clientContext.ExecuteQuery();

                Response.Write(clientContext.Web.Title);

            }

        }

If you simply F5 this solution you will get sent to the fabled “Do you trust me” page, where you of course click “Trust It”.

Then you’ll be redirected to the Default.aspx page for your app where the title of your developer web site will be shown true and proud!  I, rather unoriginally, gave mine the title ‘Developer Web Site’.

Ok, so that works but no metrics.  So all I do is add a label control to the mark-up:

<html xmlns="http://ift.tt/lH0Osb">

<head runat="server">

    <title></title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <asp:Label ID="labPageLoadTime" runat="server" Font-Bold="true"></asp:Label>       

    </div>

    </form>

</body>

</html>

And then add a simple bit of start/end timer code and spit the time difference out to the label.

protected void Page_Load(object sender, EventArgs e)

{

    DateTime Start = DateTime.Now;

 

    var spContext = SharePointContextProvider.Current.GetSharePointContext(Context);

 

    using (var clientContext = spContext.CreateUserClientContextForSPHost())

    {

         clientContext.Load(clientContext.Web, web => web.Title);

         clientContext.ExecuteQuery();

         Response.Write(clientContext.Web.Title);

    }

    ReportProcessingTime(Start);

}

private void ReportProcessingTime(DateTime Start)

{

    DateTime End = DateTime.Now;

    TimeSpan TS = End - Start;

    string LoadTimeString = "Page loaded in" +

           string.Format(" {0}.{1} ", TS.Seconds.ToString(), TS.Milliseconds.ToString()) +

           "seconds";

    labPageLoadTime.Text = LoadTimeString;

}

Crude, but effective enough to give us a benchmark to work with.

5.62 seconds, yikes we’d better pray we can do better than that.

Hold your horses!

The more astute reader will have picked out the obvious (when you think about it) fact that we are currently using an App Web for development and testing and that simply can’t be a true and accurate reflection of our end user experience.

The super astute reader will have picked up on the fact that the GetSharePointContext method makes use of the SaveSharePointContext and the LoadSharePointContext methods.

protected override SharePointContext LoadSharePointContext(HttpContextBase httpContext)

{

    return httpContext.Session[SPContextKey] as SharePointAcsContext;

}

 

protected override void SaveSharePointContext(SharePointContext spContext, HttpContextBase httpContext)

{

    SharePointAcsContext spAcsContext = spContext as SharePointAcsContext;

 

    if (spAcsContext != null)

    {

        HttpCookie spCacheKeyCookie = new HttpCookie(SPCacheKeyKey)

        {

            Value = spAcsContext.CacheKey,

            Secure = true,

            HttpOnly = true

        };

 

        httpContext.Response.AppendCookie(spCacheKeyCookie);

    }

 

    httpContext.Session[SPContextKey] = spAcsContext;

}

And, as can be seen these methods are already cache enabled as they save and read SharePointContext objects to the Session cache as highlighted above.

We’ll get to publish our app out to Azure in the next section but let me first clear up the 2^nd point about session state. 

The eleventh hour

I guess caching credentials to session state make sense.  It’s an easy way to grab an access token for the user once per browser session, persist and subsequently reuse that token for the duration of that session.  The problem is that in Azure we don’t have session state – it’s simply not available.  Yes, I know there are ways to get session state in Azure but they involve using a 3rd party service provider, which costs money.

Also, the access tokens returned by the ACS have life span which is typically longer that the average browser session.  Currently a token expires 12 hours after issue (although that might change) so we are not eking out the best return on our investment here because we can use the same access token right up to the eleventh hour – literally.  In other words, if we can find a way to save the access token to somewhere more persistent than session state (to say a SQL Server Database) then we can exploit the token to its full potential – useful if the user closes down their browser over lunch.

How we might do that is the ultimate purpose of this post but first we have to publish our app to Azure.

Azure me!

It’s not entirely obvious how to publish our fledgling app to Azure so let me guide you through the steps.  It’s not hard but there are a few of them:

• Create an Azure Web App with a SQL Server database as an attached resource

• Grab a Client Id and a Client Secret

• Configure the Azure Web App with the Client Id and Client Secret

• Publish our app to the Azure Web App

• Upload our SharePoint solution to the App Catalog on a SharePoint deployment site

• Add the app to a test site somewhere and check that it works.

The next sub-sections look at each of these steps in turn.

Create an Azure Web App

Creating a Web App in Azure (even one with a SQL Server database attached) is quite a simple job and I’ll guide you through it.  I am of course assuming that you have registered with Azure and have an active account.  You can get a 30 day free trial or, if like me you have an MSDN subscription you’ll most likely find that you have some bundled credits.

Actually you can do this without it costing a penny as you can opt for the free Azure plans – ideal for testing and that’s what we’ll do here.

I mentioned earlier that there are 3 touch points (not including the ACS).  There is the user, the SharePoint/O365 deployment and the Azure Web App.  Now, me (as the user) sitting here is not so sunny Adelaide and there’s not much I can do about that but I do have control over where the other touch point reside geographically that is.  It would be oh so tempting to park everything in one of the Australian data centers and if this was for an Australian based company then that is for sure what I would be doing.  But we’re on a world stage here folks so we’ve got to think global.

So my logic goes like this.  I want to set up my development and test environment with the worst possible latency scenario on the understanding that if I can get my apps to work well under these circumstances then I can reasonably expect my customers to get a better experience. 

So for this reason I am using an O365 subscription based in Europe and I’ll create my Azure Web App somewhere in the US.

Ok, let’s get provisioning.  Log into the Azure Management portal (here I’ll use the current live portal rather than the new front end which is still in review)

In the portal select the WEB APPS menu item on the left and click the New button (bottom right) and go to CUSTOM CREATE as highlighted below.

I use CUSTOM CREATE (as opposed to QUICK CREATE) as it will allow me to provision a SQL Server database at the same time.

Here are my settings on the first page of the wizard.

Obviously you have to pick an unused URL and the select service plan (or create a new one in my case).  On the second page of the wizard create a new SQL Server database and provide some log in credentials.

Now you could host the database in some other data center but that make no sense at all.  Firstly, we don’t which to introduce any unnecessary latency and second Microsoft charge on the basis of data egress (that’s to say data that escapes from a data center) but does not charge for data bouncing around the same data center.

A few moments later our Web App and database are provisioned and if you drill into the settings you can see the database already there as a linked resource.

Just to check all is well you can pop the URL into your favourite browser.

Note how I have used the https protocol to access the page.  This is important because your remote app will be accessed using SSL/HTTPS, it needs to be or else CSOM won’t run.  Fortunately, Microsoft has enabled SSL and provided all the necessary wild card certification if that is you use the azurewebsites.net domain.  If you want to use a custom domain then you’ll have to set up SSL yourself – I’d rather not do that.

Grab a Client Id and Client Secret

Next up, we need to get a Client Id and a Client Secret.  I should first explain what purpose these items server.  Basically, they provides a way to join the dots and make sure that everything is in order and as expected.  The ACS and your provider hosted app (in Azure) will know both the Client Id and the Client Secret but the SharePoint deployment knows only the Client Id.  Going into the details of how this machinery all works together is beyond what I intend for this article but you can (and should) absorb those details from http://ift.tt/1LZ8jCj.

So, from where do we get these elusive artefacts?  Well actually there are 2 places:

• Appregnew.aspx page:  You can browse to this magical hidden page in SharePoint at /_layouts/15/appregnew.aspx and from there generate new client secrets.

• Seller Dashboard:  At http://ift.tt/1aoeNYr

I’m not going to go into the Appregnew.aspx page here but be aware if you do use this page to created you Client Id and Client App these objects will then be tied to that specific deployment environment.  If you use the Seller Dashboard then you can use them on any deployment and so this is the better method in my view and that’s what I’ll be using.

Bear in mind that you can’t just rock up to the seller dashboard and create an Id and Secret, first you have to register with the dashboard (and that’s the subject for an entirely different post).

From the client ids tab on the Seller Dashboard click on the ‘add a new oauth client id’ button.

Next you can provide a friendly name, the App Domain as generated by Azure and the start page for our app which will be the pages/default.aspx page as it will be when we publish the app.

You can set the expiry period for the secret and you want this to be available world-wide (China has some weird business constraints which makes it special).

Click on the GENERATE CLIENT ID button.

This is your one and only time to grab the Client Secret so copy it (along with Client ID) to Notepad and park its somewhere safe.

Configure the Web App with the Client Id and Client Secret

We now have to configure the Azure Web App with the newly generated credentials.  To do that head back over to Azure and on the CONFIGURE tab of the Web App properties scroll down to the app settings section and add two new properties as shown below:

Provide your ClientId and ClientSecret values obviously and take care to spell these properties correctly using the exact case as shown or else it won’t work and you’ll bash your head for hours trying to figure out why.  Don’t forget to click the SAVE button to update the Web App configuration.

Publish to Azure

Getting there.  Now head back to Visual Studio where we can publish the app to the Azure Web App.  However, just before we do that you should set the app’s permissions in the AppManifest.xml file.  Set the scope to Web and we only need Read level permissions.

Now select the App Web project in the Visual Studio Solution Explorer and from the BUILD menu select the Publish menu item.

From the Publish Web dialog select Microsoft Azure Websites as the publish target.

Then select the newly created Web App (for some reason you might need to sign out and back in again to trigger a refresh of the list of web sites).  Take care to select the correct web app or else you might overwrite any existing site and click the OK button to trigger the download of connection settings.

You can click the Validate Connection button and if all is well a green check mark will appear.

This is all we actually need to configure so we can actually click the Publish button.

This rebuilds the app and pushes it to the Azure Web App.  Note that first time you do this it might take a couple of minutes as everything is pushed down the line, including the CSOM assemblies.  Subsequent publishing just pushes differential changes and so is much quicker.

All being well Visual Studio will report that the publishing was successful.  If you access the default.aspx page in the browser you will most likely see an error message.

This is to be expected because we are accessing the page without the context of a SharePoint host site.

In the Azure Web App you might want to set the pages/default.aspx as the default document.

This will mean the target page gets loaded in the browser whenever we publish the app subsequently.

Upload our SharePoint solution to the App Catalog

Nearly there now.  Next we need to publish our SharePoint App and add it to the App Catalog of a test deployment site.

Select the SharePoint App project in the Visual Studio Solution Explorer and from the BUILD menu once again select Publish.  From the Publish your app page you will notice a yellow warning symbol.  This is there to tell you that you app has not been configured with the Client Id or Client Secret.  You can add these values by clicking the Edit button and pasting in the app identity credentials.  These of course must match what is set in the Azure Web App and associated with the app domain in the ACS.

Click Finish to close the dialog and the warning symbol should disappear.

Now click the Package the app button and from the dialog change the URL to our start page.  Note that you’ll need to use the https protocol here.  Click Finish.

This will package the app and pop up the Windows Explorer.  Copy the file path to the app file to the clipboard because we’ll need that in a moment.

Now browse to your App Catalog.  If you haven’t set one of these up yet then you’ll need to do so first.  It’s fairly straight forward and Microsoft guidance is available here http://ift.tt/1FZY00H

Upload the app file to the catalog using the browse button and pasting in the URL of the app file.

The app will be shown in the catalog.

Add and test the app

The final step is to add the app to a test site somewhere in the same deployment as the app catalog and check that it works as expected.

From the Add an app page select the From Your Organisation category and click on the CacheTest app.

After a few moments the Trust It dialog will appear and of course click Trust It.

A few moments later your app will appear on the site.

Finally, click on the app tile and marvel at your own genius!

Notice how the URL is to you Azure Web App as proof that we are no longer in the development world of the App Web.

Plumbing’s done

Now a page load of 1.688 seconds isn’t too bad, especially considering that my 3 touch points are split across the globe but I reckon that we can do better than that.

Now that the plumbing is done you can of course build and deploy something a bit more useful and I do know that it’s been a journey and a half to get this far but stay with me for the final instalment in part 2 where I’ll go on to explain how to cache the credentials to the SQL Server database we have set up in Azure.

 

 

 

 

 

by Colin Gardner via Everyone's Blog Posts - SharePoint Community