Hosting a WCF Data Service, with Basic Authentication for PowerPivot Clients
Introduction
This walkthrough demonstrates how to host a WCF Data Service in an ASP.NET web site in a way which allows PowerPivot to connect to the service using basic authentication. Some relevant areas have already been covered in a lot of detail elsewhere on MSDN or in blogs. When this is the case links to the other sources are provided instead of going through all the details here.
The problem
PowerPivot supports two forms of authentication: SSPI and Basic. This can come as a bit of a surprise if you are used to using ‘Forms’ authentication when securing a WCF Data Service.
For many scenarios using basic authentication will be the only option. When connecting to a WCF Data Service using basic authentication PowerPivot first tries to connect without an authentication header. If it receives a 401 error with a WWW-authenticate basic response header it will then retry with a request including a basic authorization header.
In order for PowerPivot to authenticate successfully the web site hosting the service must provide basic authentication, and not redirect on requests which have failed.
The walkthrough
Step 1: create a new ASP.NET Empty Web Application
This is fairly self explanatory. One thing worth mentioning is that you can use an ASP.NET MVC 4 application with an empty template as well. If you do make sure to exclude the .svc extension from the route config as shown below.
public class RouteConfig
{
public static void RegisterRoutes(RouteCollection routes)
{
routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
routes.IgnoreRoute("{resource}.svc/{*pathInfo}");
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);
}
}
Step 2: install the necessary NuGet packages.
The required packages are: EntityFramework, WCF Data Services Server and Microsoft ASP.NET Web Pages 2 Web Data.
Step 3: remove forms authentication from site
This requires the following two changes to the Web.config.
<system.web>
...
<authentication mode="None"/>
...
</system.web>
<system.webServer>
...
<modules>
<remove name="FormsAuthentication" />
</modules>
</system.webServer>
*If you are using an MVC 4 application the modules declaration will need the attribute runAllManagedModulesForAllRequests="true".
This is very important in terms of consuming the service in PowerPivot. Without doing this the service will return a redirect to login response when PowerPivot initially tries to connect. This will cause it to give up on connecting straight away.
Step 4: add the WCF Data Service.
This article assumes that you are already know how to add a model and data service yourself. Here is a good starting point if not: Creating a WCF Data Service.
Step 5: setup simple membership.
Add the membership and role provider to Web.config.
<system.web>
...
<roleManager enabled="true" defaultProvider="simple">
<providers>
<clear/>
<add name="simple" type="WebMatrix.WebData.SimpleRoleProvider,WebMatrix.WebData"/>
</providers>
</roleManager>
<membership defaultProvider="simple">
<providers>
<clear/>
<add name="simple" type ="WebMatrix.WebData.SimpleMembershipProvider,WebMatrix.WebData"/>
</providers>
</membership>
</system.web>
Add a Global application class (Global.asax). In the Application_Start method initialize the database connection for the SimpleMembershipProvider. Use the connection that will be used by Entity Framework, ‘DefaultConnection’ in the example below. As simple membership is using the model database make sure it has been initialized first.
protected void Application_Start(object sender, EventArgs e)
{
//make sure the database has been created before initializing the database connection
using (BookContext context = new BookContext())
{
context.Database.Initialize(false);
}
if (!WebSecurity.Initialized)
WebSecurity.InitializeDatabaseConnection("DefaultConnection", "UserProfile", "UserId", "UserName", autoCreateTables: true);
}
*Global.asax will already exist if you are using an MVC application
You may want to add entities to your model to represent the simple membership entities at this point. In the code sample which accompanies this example a UserProfile entity has been added to give access to users from the DbContext. You do not have to do this. The call to InitializeDatabaseConnection will create the tables it needs anyway and you could exclusively use the SimpleMembershipProvider for access to users, roles etc.
Step 6: add custom basic authentication
This step is already well documented, particularly in these two articles: Securing OData Services using Basic Authentication and OData and Authentication – Part 6 – Custom Basic Authentication. For completeness I will include the full code here. The part to pay attention to is the TryAuthenticate method on the SimpleMembershipAuthenticationProvider shown below. Here SimpleMembershipProvider is being used to validate and return the user.
private static bool TryAuthenticate(string userName, string password, out IPrincipal principal)
{
var smp = (SimpleMembershipProvider)System.Web.Security.Membership.Provider;
if (smp.ValidateUser(userName, password))
{
MembershipUser user = smp.GetUser(userName, true);
principal = new GenericPrincipal(new GenericIdentity(userName, "Basic"), null);
return true;
}
else
{
principal = null;
return false;
}
}
}
Here is the full code listing for the BasicAuthenticationModule,
public class BasicAuthenticationModule : IHttpModule
{
public void Init(HttpApplication context)
{
context.AuthenticateRequest
+= new EventHandler(context_AuthenticateRequest);
}
void context_AuthenticateRequest(object sender, EventArgs e)
{
HttpApplication application = (HttpApplication)sender;
if (!SimpleMembershipAuthenticationProvider.Authenticate(application.Context))
{
application.Context.Response.Status = "401 Unauthorized";
application.Context.Response.StatusCode = 401;
application.Context.Response.AddHeader("WWW-Authenticate", "Basic");
application.CompleteRequest();
}
}
public void Dispose() { }
}
and for the SimpleMembershipAuthenticationProvider.
public static class SimpleMembershipAuthenticationProvider
{
public static bool Authenticate(HttpContext context)
{
string authHeader = context.Request.Headers["Authorization"];
IPrincipal principal;
if (TryGetPrincipal(authHeader, out principal))
{
HttpContext.Current.User = principal;
return true;
}
return false;
}
private static bool TryGetPrincipal(string authHeader, out IPrincipal principal)
{
string user;
string password;
if (TryParseAuthorizationHeader(authHeader, out user, out password))
{
return TryAuthenticate(user, password, out principal);
}
principal = null;
return false;
}
private static bool TryParseAuthorizationHeader(string authHeader, out string user, out string password)
{
user = "";
password = "";
if (string.IsNullOrEmpty(authHeader) || !authHeader.StartsWith("Basic"))
{
return false;
}
string base64EncodedCreds = authHeader.Substring(6);
string[] creds = Encoding.ASCII.GetString(Convert.FromBase64String(base64EncodedCreds)).Split(new char[] { ':' });
if (creds.Length != 2 || string.IsNullOrEmpty(creds[0]) || string.IsNullOrEmpty(creds[1]))
{
return false;
}
user = creds[0];
password = creds[1];
return true;
}
private static bool TryAuthenticate(string userName, string password, out IPrincipal principal)
{
var smp = (SimpleMembershipProvider)System.Web.Security.Membership.Provider;
if (smp.ValidateUser(userName, password))
{
MembershipUser user = smp.GetUser(userName, true);
principal = new GenericPrincipal(new GenericIdentity(userName, "Basic"), null);
return true;
}
else
{
principal = null;
return false;
}
}
}
The final step is to add the module in the Web.config.
<modules>
<remove name="FormsAuthentication" />
<add name="CustomBasicAuthenticationModule" type="TestSite.BasicAuthentication.BasicAuthenticationModule" />
</modules>
Step 7: host the web site in either IIS Express or IIS
The Visual Studio Development Server does not support basic authentication so host the web site in either of the above in order to test it. For this walkthrough I am assuming it is just going to be over HTTP but in a real world scenario this is not secure and HTTPS should be used.
Step 8: test the data service.
Assuming you have Excel 2010 or greater installed and PowerPivot installed/enabled you can now test the service. When connecting make sure that in the advanced options Integrated Security is set to Basic and that the user information is correct. Set ‘Persist Security Info’ to True to avoid having to re-enter the password every time.
Finally
A code sample to accompany this article is available here.