Inconvenient Excel REST Services and anonymous users

SharePoint 2010 ships with Excel REST Services that allow us to request pieces of Excel Sheets using a URL. Unfortunately things get pretty complicated when you try to make Excel REST Services available to your anonymous visitors. Find out how to deal with Excel REST Services and anonymous users.

The Inconvenience

Excel REST Services in SharePoint 2010 are a very convenient way of creating rich dashboard and pages. Using nothing but the URL you can include dynamic content from an Excel Sheet located somewhere in your SharePoint Farm. Using Excel REST Services you can not only display data but also charts. The great thing is that you can retrieve static data but you can also pass parameters to the Excel Sheet what allows you to retrieve data and charts based on your own calculations! So what’s the problem?

For the Excel REST Services to work correctly the user that is requesting the data from the Excel Sheet must have Read permissions on that specific sheet. Additionally if you want to enable your users to pass parameters along with the request, all of a sudden all those users must have Write permissions! Can you imagine allowing all of your anonymous visitors modify the Excel Sheet with your business calculations?

A less desirable solution

One way to work around this issue is to create another Web Application that isn’t accessible by a public URL. You could then create a simple HTTP Handler that would catch all public requests, turn them into internal calls to Excel REST Services, retrieve the content and pass it back to the visitor. Because the internal Web Application is not directly available to your visitors, you could grant everyone Write permissions to the Excel Sheet.

Although this solution requires minimal amount of custom development it’s far from perfect. First of all you have to maintain a whole separate Web Application for the purpose of hosting Excel Sheets. Additionally you have to allow everyone to modify the Excel Sheet which is really terrible from the governance point of view.

A slightly better alternative

An alternative approach would be to create a custom HTTP Handler that works as a man-in-the-middle – just like in the previous approach, but instead of creating a separate Web Application for hosting Excel Sheets, make the HTTP Handler make an impersonated call to a properly secured Excel Sheet.

The following code snippet shows a sample HTTP Handler that does just that:

using System;
using System.Collections.Generic;
using System.IO;
using System.Net;
using System.Runtime.InteropServices;
using System.Security;
using System.Web;
using Microsoft.BusinessData.Infrastructure.SecureStore;
using Microsoft.Office.SecureStoreService.Server;
using Microsoft.SharePoint;

namespace Mavention.AnonymousExcelRestServices {
    [Guid("51dfb6a4-1460-4641-9727-fc1ce0e7c742")]
    public class Chart : IHttpHandler {
        string worksheet;
        string chart;
        string multiplyBy;
        CookieContainer cookies;
        CredentialCache credentialCache;

        #region IHttpHandler Members

        public bool IsReusable {
            get { return false; }
        }

        public void ProcessRequest(HttpContext context) {
            Authenticate(context);
        }

        #endregion

        private void Authenticate(HttpContext context) {
            cookies = new CookieContainer();
            string url = String.Format("{0}/_layouts/viewlsts.aspx",
                SPContext.Current.Site.Url.TrimEnd('/'));
            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(url);
            request.Method = "GET";
            request.Accept = "*/*";
            request.CookieContainer = cookies;
            string username = null;
            string domain = null;
            string password = null;
            GetCredentials(out username, out domain, out password);
            credentialCache = new CredentialCache();
            credentialCache.Add(new Uri(SPContext.Current.Site.Url), "NTLM", new NetworkCredential(username, password, domain));
            request.Credentials = credentialCache;
            request.GetResponse();
            LoadChartImage();
        }

        private void LoadChartImage() {
            InitiateVariables(HttpContext.Current);
            string url = String.Format("{0}/_vti_bin/ExcelRest.aspx/{1}/Model/Charts('{2}'){3}",
                SPContext.Current.Site.Url.TrimEnd('/'),
                worksheet,
                chart,
                !String.IsNullOrEmpty(multiplyBy) ? String.Format("?Ranges('MultiplyBy')={0}", multiplyBy) : String.Empty);

            HttpWebRequest request = (HttpWebRequest)HttpWebRequest.Create(url);
            request.Method = "GET";
            request.CookieContainer = cookies;
            request.Credentials = credentialCache;

            byte[] buffer = null;
            byte[] file = null;

            using (HttpWebResponse chartResponse = (HttpWebResponse)request.GetResponse()) {
                using (BinaryReader br = new BinaryReader(chartResponse.GetResponseStream())) {
                    using (MemoryStream ms = new MemoryStream()) {
                        buffer = br.ReadBytes(1024);
                        while (buffer.Length > 0) {
                            ms.Write(buffer, 0, buffer.Length);
                            buffer = br.ReadBytes(1024);
                        }
                        file = new byte[(int)ms.Length];
                        ms.Position = 0;
                        ms.Read(file, 0, file.Length);
                        ms.Close();
                        br.Close();
                    }
                }

                chartResponse.Close();
            }

            HttpContext.Current.Response.ContentType = "image/png";
            HttpContext.Current.Response.BinaryWrite(file);
        }

        private void InitiateVariables(HttpContext context) {
            worksheet = context.Request.QueryString["w"];
            chart = context.Request.QueryString["c"];
            multiplyBy = context.Request.QueryString["m"];
        }

        private static void GetCredentials(out string username, out string domain, out string password) {
            username = "waldek";
            domain = "win2008";
            password = "pass@word1";
        }
    }
}

Authenticating against SharePoint

The first thing that the request has to do is to authenticate against SharePoint. In this example the HTTP Handler works in the context of a Web Application with Classic Authentication Mode, so we will use NTLM authentication.

If you try to authenticate directly against Excel REST Services you will get a rather unpleasant surprise. Because Excel REST Services don’t send the necessary headers, in spite of sending the credentials with your request, you will still access Excel REST Services as an anonymous user. To avoid this we have to request a page that not only requires authentication but also sends the necessary headers. In the context of a public-facing website, the View All Site Content page is such page (/_layouts/viewlsts.aspx; assuming you have the ViewFormPagesLockDown Feature activated).

So the first step for the handler to do is to construct a request to the View All Site Content page (lines 35-47). Before the request is made the credentials of a user who has Write permissions on the Excel Sheet must be retrieved. To keep the example simple the credentials have been hardcoded (lines 98-100). Later we will take a look at a more secure manner of storing credentials.

An important thing to be able to correctly authenticate our HttpWebRequest against Excel REST Services is storing the credentials and cookies. This is why both variables are defined outside the Authenticate method (lines 18 and 19).

After the request is done (line 48) the cookie container is filled with an authentication cookie that will allow us to make a request to Excel REST Services. Now that we have everything in place, we are ready to retrieve our chart using Excel REST Services.

Requesting data from Excel REST Services using impersonated request

Our custom HTTP Handler accepts three query string parameters: w – which points to the Excel Sheet, c – which contains the name of the chart that we want to display and m – which is an optional parameter that allows us to pass parameter to the chart. A sample call using our HTTP Handler would look as follows:

http://publishing/_layouts/AnonymousExcelRestServices/Chart.ashx?w=Documents/Book1.xlsx&c=Chart 1&m=5

In this call we request the Book1.xlsx Excel Sheet stored in the Documents Library in the Root Web. From that worksheet we want to display chart called Chart 1 and we want it to show the results of a calculation using parameter 5. The above call is an equivalent of the following Excel REST Services request:

http://publishing/_vti_bin/ExcelRest.aspx/Documents/Book1.xlsx/Model/Charts('Chart 1')?Ranges('MultiplyBy')=5

After retrieving the variables (lines 53 and 92-94) we proceed with constructing the URL to call Excel REST Services (lines 54-58). Because the m parameter is optional we have to take that into account while building the URL (line 58).

Once we have the URL we can proceed with constructing the HttpWebRequest object that we will use to retrieve the chart image from Excel REST Services. We pass the URL to the constructor (line 60), we set the request method to GET (line 61) and then we fill the cookies and credentials with our previously stored values (lines 62 and 63). With that we are ready to make the request and process the response (lines 68-85). All this code block does is to retrieve the chart image rendered by the Excel REST Services and to pass it back to the user.

Results

When you make a request to Excel REST Services using our proxy HTTP Handler, although you are visiting the site as an anonymous user, you can see the dynamically rendered chart just as if you had Write permissions on the Excel Sheet.

Chart image rendered by the proxy HTTP Handler for anonymous user

Storing credentials the right way

In the previous example you have seen how to impersonate an HttpWebRequest to make an authenticated call to Excel REST Services. For this we needed a valid set of credentials. As you can expect storing any kind of credentials in code is a bad idea and for this particular purpose SharePoint 2010 provides us with the Secure Store Service.

Secure Store Service is a Service Application that allows you to store credentials in a secure fashion. Once entered the credentials cannot be viewed using the standard SharePoint UI. They can however be read using code which is perfect for our purpose.

Let’s modify our HTTP Handler to use credentials from the Secure Store Service instead of hardcoding them.

Configuring Target Application

Let’s start by configuring credentials for our HTTP Handler. For this we have to go to the Secure Store Service and create a new Target Application:

New Target Application button highlighted in the Ribbon

Next we have to provide some information about our Target Application. The most important part is the Target Application ID which we will have to use in code in order to retrieve the credentials.

Target Application information in the Secure Store Service

Next we have to configure fields for which we will want to store value in our Target Application. Next to the two standard fields for storing the user name and password let’s add another field for storing the domain name. Although we could extract the domain name from the Windows User Name it’s just easier to store it separately.

Configuring Fields for the Target Application

Finally we have to configure the user who will be administering the Target Application.

Configuring Target Application Administrator

With that our Target Application has been created.

Target Application for HTTP Handler created in the Secure Store Service

Now let’s add credentials that we can use to make an authenticated HttpWebRequest to the Excel REST Services. Select the Target Application and from the Credentials group click the Set button.

Set button highlighted in the Ribbon

As the owner of the credentials choose the Application Pool account of your Web Application. Because we will be using elevated privileges to access the credentials you must use the same account as your Application Pool for this to work correctly. As for the credentials choose an account that has Write permissions on the Excel Sheet.

Configuring credentials for the Target Application

This completes the credentials configuration. Now we can proceed with extending the code of our HTTP Handler with support for a Secure Store Service.

Extending the HTTP Handler with support for the Secure Store Service

In the HTTP Handler replace the previous implementation of the GetCredentials method with the following code snippet:

private static void GetCredentials(out string username, out string domain, out string password) {
    username = null;
    domain = null;
    password = null;

    string targetApplicationId = "Publishing_AnonymousExcelRestServices";

    if (SPContext.Current != null) {
        string _username = null;
        string _domain = null;
        string _password = null;

        string siteUrl = SPContext.Current.Site.Url;
        SPSecurity.RunWithElevatedPrivileges(() => {
            using (SPSite site = new SPSite(siteUrl)) {
                SPServiceContext context = SPServiceContext.GetContext(site);
                SecureStoreServiceProxy sssProxy = new SecureStoreServiceProxy();
                ISecureStore secureStore = sssProxy.GetSecureStore(context);
                SecureStoreCredentialCollection credentials = secureStore.GetCredentials(targetApplicationId);
                if (credentials != null) {
                    IList<TargetApplicationField> targetApplicationFields = secureStore.GetApplicationFields(targetApplicationId);
                    foreach (TargetApplicationField field in targetApplicationFields) {
                        switch (field.Name) {
                            case "Windows User Name":
                                _username = ReadSecureString(credentials[targetApplicationFields.IndexOf(field)].Credential);
                                break;
                            case "Windows Password":
                                _password = ReadSecureString(credentials[targetApplicationFields.IndexOf(field)].Credential);
                                break;
                            case "Domain":
                                _domain = ReadSecureString(credentials[targetApplicationFields.IndexOf(field)].Credential);
                                break;
                        }
                    }
                }
            }
        });

        username = _username;
        domain = _domain;
        password = _password;
    }
}

internal static string ReadSecureString(SecureString secureString) {
    string str = null;

    if (secureString != null) {
        IntPtr ptr = Marshal.SecureStringToBSTR(secureString);
        str = Marshal.PtrToStringBSTR(ptr);
        Marshal.ZeroFreeBSTR(ptr);
    }

    return str;
}

You will also need references to the Microsoft.BusinessData and Microsoft.Office.SecureStoreService assemblies.

Before we make the call to the Secure Store Service to retrieve our credentials we have to define the Target Application ID (line 6). It has to be exactly the same as specified while configuring the Target Application in the Secure Store Service.

Because we are using the HTTP Handler as an anonymous user, we have to elevate privileges to access the credentials from the Secure Store Service (line 14). After we get a reference to the Secure Store Service (lines 16-18) we can retrieve our credentials (line 19). From that object we can then retrieve the values for username (lines 24-26), password (lines 27-29) and domain (lines 30-32). Because the obtained string values are encoded we have to decode them before we can use them with our request. For this we’re using the ReadSecureString helper method (lines 45-55).

Summary

SharePoint 2010 ships with Excel REST Services which provide a great opportunity for extending SharePoint Solutions with dynamic data. Unfortunately Excel REST Services require users to have Write permissions to Excel Sheets in order to be able to retrieve information from them which is far from desired on a public-facing website. By creating a custom HTTP Handler you can impersonate requests to Excel REST Services and publish data from Excel Sheets in a secure manner even on your public-facing website.

Others found also helpful: