locked
Writing a Secure WCF Data Service for Excel PowerPivot RRS feed

  • Question

  • Hello,

    I'm having some trouble writing a secure WCF data service to be consumed by PowerPivot. The service works fine, and I can consume the data in PowerPivot without trouble.

    My issue is that when I enter the user ID and password for the Data Feed in PowerPivot (in Data Feed advanced settings), I can't seem to get any access to them from inside the WCF service. I'd like to use both the user ID and password to authenticate against a database, but I need to get at them first. :)

    Are there any good examples of how to write a secure WCF Data Service specifically for PowerPivot?

    Thanks very much.

    Wednesday, December 15, 2010 4:41 PM

Answers

  • Hello, basically you can't obtain the password. First, WCF Data Services are REST services, which don't support SOAP message security. Only transport security is supported. Second, Excel PowerPivot doesn't support message security. Only transport security is supported.

    To configure PowerPivot authentication for your data service, you need to turn on transport security:

        <bindings>
          <webHttpBinding>
            <binding>
              <security mode="Transport">
                <transport clientCredentialType="Basic"/>
              </security>
            </binding>
          </webHttpBinding>
        </bindings>

    Then host the service in IIS (which supports transport security), and configure IIS's basic authentication. IIS will handle the authentication. You won't be able to obtain the user's password, but you'll be able to obtain the user's identity via ThreadCurrentPrinciple, which may contain the username.

    If you want to authenticate against a database, I suggest you to use Windows authentication. Change Basic to Windows from the above config, and configure IIS to use Windows authentication. Your PowerPivot clients can use SSPI Integrated Security (Windows authentication) without specifying a username/password. Then enable impersonation in IIS, and if the database server is on the same machine of IIS, it should work fine. If the database server is on a different machine, you need to solve an additional double hop issue as decribed on http://blogs.msdn.com/b/securitytools/archive/2009/11/04/double-hop-windows-authentication-with-iis-hosted-wcf-service.aspx.

    If you don't want to use Windows authentication, you can try to use OAuth. OAuth doesn't use transport/message security. Instead, the client sends the identity in a HTTP request header (the Authorization header), and your service should be able to obtain this header. But I'm not sure if PowerPivot supports OAuth.


    Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.
    Windows Azure Technical Forum Support Team Blog
    • Marked as answer by Yi-Lun Luo Wednesday, December 22, 2010 9:32 AM
    Friday, December 17, 2010 2:45 AM

All replies

  • Hello, basically you can't obtain the password. First, WCF Data Services are REST services, which don't support SOAP message security. Only transport security is supported. Second, Excel PowerPivot doesn't support message security. Only transport security is supported.

    To configure PowerPivot authentication for your data service, you need to turn on transport security:

        <bindings>
          <webHttpBinding>
            <binding>
              <security mode="Transport">
                <transport clientCredentialType="Basic"/>
              </security>
            </binding>
          </webHttpBinding>
        </bindings>

    Then host the service in IIS (which supports transport security), and configure IIS's basic authentication. IIS will handle the authentication. You won't be able to obtain the user's password, but you'll be able to obtain the user's identity via ThreadCurrentPrinciple, which may contain the username.

    If you want to authenticate against a database, I suggest you to use Windows authentication. Change Basic to Windows from the above config, and configure IIS to use Windows authentication. Your PowerPivot clients can use SSPI Integrated Security (Windows authentication) without specifying a username/password. Then enable impersonation in IIS, and if the database server is on the same machine of IIS, it should work fine. If the database server is on a different machine, you need to solve an additional double hop issue as decribed on http://blogs.msdn.com/b/securitytools/archive/2009/11/04/double-hop-windows-authentication-with-iis-hosted-wcf-service.aspx.

    If you don't want to use Windows authentication, you can try to use OAuth. OAuth doesn't use transport/message security. Instead, the client sends the identity in a HTTP request header (the Authorization header), and your service should be able to obtain this header. But I'm not sure if PowerPivot supports OAuth.


    Lante, shanaolanxing This posting is provided "AS IS" with no warranties, and confers no rights.
    Windows Azure Technical Forum Support Team Blog
    • Marked as answer by Yi-Lun Luo Wednesday, December 22, 2010 9:32 AM
    Friday, December 17, 2010 2:45 AM
  • Hi,

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

    Without any luck, i'm finding that theres no Authorization header when the request comes from powerpivot. Any sugestions?

    Best Regards.

    Saturday, March 12, 2011 4:48 AM