Home » SQL Server

Data feed authentication methods


I'm trying to use PowerPivot to import data from an Odata service. The data service is an API to a website that contains information belonging to many different users. Therefore the data service requires proper authentication. The service supports Basic Authentication, via GET parameter or via http headers.

Now the question is how to make PowerPivot provide any kind of authentication. I'm choosing basic authentication and supply a user name and password:


But Powerpivot does not actually supply an basic-authentication header. You can see in this screenshot of the request made by Powerpivot and captured in Fiddler:


Note the lack of any kind of authentication when processing the request, although I did select Basic Authentication. Why is this so?

I noticed that Forms authentication works, but I can't easily support in my api for technical reasons.Nor is Windows authentication an option for me.

The service supports Basic Authentication, via GET parameter or via http headers. Is there any way to make any of these methods work with Powerpivot?





1 Answer Found


Answer 1

Thanks to Alex James, I have finally found the answer to this problem:


Is your service responding with a WWW-authenticateresponse?
Some clients (perhaps PowerPivot too) only send Auth information in response to a failure, and if you just do a 401 without the www-authenticate header the client won’t  even retry with the credentials.
Definitely something to check.
And in particular this:
public class BasicAuthenticationModule: IHttpModule
    public void Init(HttpApplication context)
           += new EventHandler(context_AuthenticateRequest);
    void context_AuthenticateRequest(object sender, EventArgs e)
        HttpApplication application = (HttpApplication)sender;
        if (!BasicAuthenticationProvider.Authenticate(application.Context))
            application.Context.Response.Status = "401 Unauthorized";
            application.Context.Response.StatusCode = 401;
            application.Context.Response.AddHeader("WWW-Authenticate", "Basic");
    public void Dispose() { }

After applying these changes to my code, I could access the odata api with powerpivot without any problems.


<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure