none
No authorisation header in data feed request.

    Question

  • Hi All,

     

    I'm trying to access an in-house REST feed from within PowerPivot (Get external data from data feeds --> From other data feeds). I add in the correct url, click on the advanced button, and enter in the User Id and Password. I also ensure that integrated security is set to Basic. Using fiddler to check the request I notice there there is no Authorization: Basic XXXXXX hash string, which causes the request to fail with a 401 response - since the application can't find the header to verify the user. Is there something that I'm missing with regards to this? Any help would be greatly received.

     

    Thanks,

    R.

    Friday, May 14, 2010 4:23 PM

Answers

  • Thanks to Alex James, I have finally found the solution to this problem. It works perfectly if you return 401 Unauthorized and a proper WWW-Authenticate response header:

     

    Is your service responding with a WWW -a uthenticate response?
     
    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)
        {
            context.AuthenticateRequest
               += 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");
                application.CompleteRequest();
            }
        }
        public void Dispose() { }
    }

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

    Wednesday, October 13, 2010 9:15 AM
  • Well, after doing some research with Fiddler, i found that, from some reason, Excel needs the machine name and not the IP.

    So, putting http://machineName:1337/Service.svc instead of http://localhost:1337/Service.svc/ did the trick.

    Thanks anyway.

    Monday, July 02, 2012 1:20 PM

All replies

  • Ruaghin, we've been pretty busy and will take some time for us to get back to your posts.
    Friday, May 14, 2010 10:05 PM
  • If you could, that would be brilliant Vikram. I'm at my wits-end at the minute.
    Monday, May 17, 2010 1:27 PM
  • Hello Rughain,

    Can you provide some details on the REST feed?

    Regards,

    Linda

    Thursday, May 20, 2010 2:14 PM
  • Hi Linda,

    I can of course. Is there anthing specific that you're looking for?

     

    The REST feed searches for a header value of Authorization which would contain the Base64 encoded value of the User Name and Password. I presumed that the authorization header value would get populated with the user name and password that I entered-in, in the Advanced menu, but when I analyse the request from PowerPivot using fiddler, I get no header information whatsoever. 

     

    Regards,

    R

    Friday, May 21, 2010 3:38 PM
  • In the Advanced tab, there is also an option  “Persist Security Info”  with a default value of false. Please make sure that option is set that true.

    Thanks,

    Yimin


    Yimin Wu [MSFT - SQL Server Data Mining]
    Friday, May 28, 2010 12:43 AM
  • Hi Yimin,

     

    I did that too, without success. There was still no header definition for authorization - I am of course assuming that PowerPivot should be doing this i.e. it should be creating an authorization header when I have the username and password entered in, and integrated security is set to basic? I'd imagine that this would be the case, is there any way that I would be able to debug exactly what's happening on PowerPivot, does it generate some sort of logging?

     

    Thanks,

    R.

    Friday, May 28, 2010 12:13 PM
  • I have the same problem as I posted earlier?

    The users should be able to edit the ATOMSVC file (which is an XML file) and save it so that there is no hassle in going forward. The users should get the correct syntax for entering the authentication header.

     

    The Report Server when exports should enter this information in the feed so that one extra entry is avoided.


    mysorian
    Wednesday, June 02, 2010 8:37 PM

  • This might be caused by Proxy settings. If you have some specific proxy setting, you may want to add into you machine config or  create a Excel.config file.

    Please refer to the following url for Porxy setting:

    http://msdn.microsoft.com/en-us/library/dkwyc043.aspx

    Thanks,


    Yimin Wu [MSFT - SQL Server Data Mining]
    Thursday, June 03, 2010 12:18 AM
  • Hi Yimin,

    We do have specific proxy settings that would be needed, however I'm having problems getting this to work with an excel.exe.config file. I created the config file with the following:

    <?xml version="1.0"?>
    <configuration>
     <startup>
      <system.net>
       <defaultProxy>
        <proxy proxyaddress="http://proxymachine.com:80" bypassonlocal="true"/>
        <!--bypasslist>
          <add address="localhost" />
          <add address="127.0.0.1" />
        </bypasslist-->
      	</defaultProxy>
      <system.net>
     </startup>
    </configuration>

    Based on the example given in the link you provided, as well as searches on the internet (and combinations of adding and removing nodes i.e. configuration and startup). However, no matter what I try I get the following error message

    "This application has failed to start because the application configuration is incorrect, Reinstalling the application may fix this problem"

    Could you possibly give any hints as to what the problem could be with the XML above?

    Thanks again.

    R.

    Thursday, June 10, 2010 9:08 AM
  • I'm also facing the similar issue. The data service to which I want to point needs Windows credentials and is in different domain. I'm able to access the service end point in IE.

    Any help here would really be appreciated. I'm kinda stuck for presenting the use of PowerPivot in reference to my site.

    Thanks,

    Atul

    Saturday, August 07, 2010 11:38 AM
  • I am also having the same problem. I have analyzed the request originating from PowerPivot with fiddler and ascertained that there is indeed no authorization header whatsoever. I tried with / without persisting the credentials, it did not make any difference. More details here: http://social.msdn.microsoft.com/Forums/de-DE/sqlkjpowerpivotforexcel/thread/bc8ee951-e347-40b0-8df1-9699e15c0474

    Please help!
    Tuesday, August 10, 2010 5:03 PM
  • Any updates here.
    Thursday, September 23, 2010 6:43 AM
  • @Microsoft: If you have any news regarding this (good or bad)  I would very much appreciate hearing about them. I have several clients who want to access my odata API with Excel and I need to take a decision on how to proceed.
    Thursday, October 07, 2010 5:50 AM
  • Thanks to Alex James, I have finally found the solution to this problem. It works perfectly if you return 401 Unauthorized and a proper WWW-Authenticate response header:

     

    Is your service responding with a WWW -a uthenticate response?
     
    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)
        {
            context.AuthenticateRequest
               += 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");
                application.CompleteRequest();
            }
        }
        public void Dispose() { }
    }

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

    Wednesday, October 13, 2010 9:15 AM
  • I am having the same problem.

    I have implemented the module as described at:

    http://blogs.msdn.com/b/astoriateam/archive/2010/07/21/odata-and-authentication-part-6-custom-basic-authentication.aspx

    And this is working correcly on LinqPad.

    PowerPivot seems not to send the Authorization header, even we send the www-authenticate.

    Can you share the web.config or other settings that you used to solve the problem?

    Monday, July 02, 2012 9:11 AM
  • Well, after doing some research with Fiddler, i found that, from some reason, Excel needs the machine name and not the IP.

    So, putting http://machineName:1337/Service.svc instead of http://localhost:1337/Service.svc/ did the trick.

    Thanks anyway.

    Monday, July 02, 2012 1:20 PM
  • I too am facing this issue.  I can definitely see that the authorization header is not being sent when using PowerQuery in Excel 2013.

    I have tried FQDNs, Localhost, all sorts of options and putting the machine name in made no difference.  This works fine in a browser and using PowerShell Invoke-RestMethod

    Wednesday, January 08, 2014 12:59 PM
  • Well actually it is the forward-slash at the end of the URL which makes the difference for some reason.

    http://localhost:61337/Service.svc doesn't work but http://localhost:61337/Service.svc/ works OK. Took a while to figure that out :/

    • Proposed as answer by Lukas Neumann1 Tuesday, April 15, 2014 12:22 PM
    Tuesday, April 15, 2014 12:22 PM