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.


Hello guys.

I've got the following methods:

private Boolean PossuiPermissoesParaOcorrencia(Int32 id){
    var ocorrencia = _repositorioOcorrencias.Obtem(id);
    return ocorrencia == null
               ? true
               : _roleValidation.IsCurrentInRole(
                   SecurityManager.GetRequiredGroupNameFromType(SecurityManager.OperacaoOcorrencias,ocorrencia.TipoOcorrencia, SecurityManager.PermissaoEscrita));

and the IsCurrentInRole looks like this:

public interface ISecurityValidation {
    Boolean IsCurrentUserAllowed(IEnumerable<String> allowedRoles);
    Boolean IsCurrentInRole(String role);

public sealed class SecurityValidationContract:ISecurityValidation {
    public Boolean IsCurrentUserAllowed(IEnumerable<string> allowedRoles){
        Contract.Requires(allowedRoles != null);
        return default(Boolean);

    public Boolean IsCurrentInRole(string role){
        Contract.Requires(role != null);
        return default(Boolean);

As you can see, IsCurrentInRole requires a non-null String. The GetRequiredGroupNameFromType method looks like this:

public static String GetRequiredGroupNameFromType<T>(String operation, T valor, String permissao) where T:struct {
    Contract.Requires(operation != null);
    Contract.Requires(permissao != null);
    Contract.Ensures(Contract.Result<String>() != null );
    if(!typeof(T).IsEnum) throw new ArgumentException("can only be applied to enums");
    return operation + "-" + valor.ToString() + permissao;

I've added the Ensures method to guarantee that this method will never return null. Shouldn't this be enough for not getting an exception in the PossuiPermissoesParaOcorrencia method? (I had to change my code and add an explicit Contract.Assume for the SecurityManager.GetRequiredGroupNameFromType return value )





does anyone know of a sp2007 rss feed webpart which shows the feeds in side by side columns rather than 1 under the other. 



I just finished upgrading one of the servers.  The only problem is the ATOM feed wouldn't appear in either the Report Manager site or in the client web pages that we created using the VS2010 ReportViewer control.  I fixed the report manager by adding the correct render line in the RS config file in the ReportServer folder.  However, I can't for the life of me figure out how to make it appear in the VS2010 Report Viewer Control that is on the client ASPX pages.


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.





I have a problem when try import data from a sharepoint list using Data Feed mode.
My case is:

- I export SP2010 list as a Data FEed and generate Atomsvc file.
- Then I Open Excel 2010, and open Powerpivot and try to get data from this list using the atomsvc file.
- Sharepoint give me this error:

WebHost failed to process a request.

Sender Information: System.ServiceModel.Activation.HostedHttpRequestAsyncResult/58451146

Exception: System.ServiceModel.ServiceActivationException: The service '/_vti_bin/ListData.svc' cannot be activated due to an exception during compilation. The exception message is: Could not load file or assembly 'Microsoft.Data.Services, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.. ---> System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Data.Services, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. The system cannot find the file specified.

File name: 'Microsoft.Data.Services, Version=, Culture=neutral, PublicKeyToken=b77a5c561934e089'

at System.RuntimeTypeHandle._GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark, Boolean loadTypeFromPartialName)

at System.RuntimeTypeHandle.GetTypeByName(String name, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark)

at System.RuntimeType.PrivateGetType(String typeName, Boolean throwOnError, Boolean ignoreCase, Boolean reflectionOnly, StackCrawlMark& stackMark)

at System.Type.GetType(String typeName, Boolean throwOnError)

at System.ServiceModel.Activation.ServiceParser.GetType(String typeName)

at System.ServiceModel.Activation.ServiceParser.GetCompiledType(Assembly compiledAssembly)

at System.ServiceModel.Activation.ServiceParser.CreateParseString(Assembly compiledAssembly)

at System.ServiceModel.Activation.ServiceBuildProvider.GetCustomString(CompilerResults results)

at System.Web.Compilation.BuildProvider.CreateBuildResult(CompilerResults results)

at System.Web.Compilation.BuildProvider.GetBuildResult(CompilerResults results)

at System.Web.Compilation.BuildManager.CompileWebFile(VirtualPath virtualPath)

at System.Web.Compilation.BuildManager.GetVPathBuildResultInternal(VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)

at System.Web.Compilation.BuildManager.GetVPathBuildResultWithNoAssert(HttpContext context, VirtualPath virtualPath, Boolean noBuild, Boolean allowCrossApp, Boolean allowBuildInPrecompile)

at System.Web.Compilation.BuildManager.GetCompiledCustomString(String virtualPath)

at System.ServiceModel.ServiceHostingEnvironment.HostingManager.GetCompiledCustomString(String normalizedVirtualPath)

at System.ServiceModel.ServiceHostingEnvironment.HostingManager.CreateService(String normalizedVirtualPath)

at System.ServiceModel.ServiceHostingEnvironment.HostingManager.ActivateService(String normalizedVirtualPath)

at System.ServiceModel.ServiceHostingEnvironment.HostingManager.EnsureServiceAvailable(String normalizedVirtualPath)

WRN: Assembly binding logging is turned OFF.

To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.

Note: There is some performance penalty associated with assembly bind failure logging.

To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].

--- End of inner exception stack trace ---

at System.ServiceModel.AsyncResult.End[TAsyncResult](IAsyncResult result)

at System.ServiceModel.Activation.HostedHttpRequestAsyncResult.End(IAsyncResult result)

Process Name: w3wp

Process ID: 2148

Any suggestion?



Hi -

I originally posted this question over here, and it was suggested it would be better suited here.



I have a authenticated XML data feed that won't refresh the data feed. I can access it directly, I can set it up initially within Excel 2003, I just get a 'data not found' error when I attempt to refresh it.

Any help is appreciated!!



hi i was wondering if i can create a live feed in to the popfly block? like getting camera feed in to virual earth or something like that

For every list there will be RSS feeds so i just want to get that data from RSS feed and through C# i just want to present it on the page and through 'created date' i just want to show the lists which have created before 15 days from the current date?
Thanks & Regards,



Can anybody tell me how to customize the OData feed for WCF Data Services using Entity Framework (Code First)?

There is no EDMX file (as it is Code Only).

I have tried adding the attribute:

[EntityPropertyMapping( "Id" , SyndicationItemProperty.Title , SyndicationTextContentKind.Plaintext , true )]

To my POCO classes.

But it doesn't show anything in the entry title tags!

I have tried with standard POCO's (with no parent class).

I have also tried extending POCO's from the EntityObject class.

EDIT: Anybody?





Are there any resources on how to implement feed-friendly output when using a custom data service provider? The only information I could find was related to the EF provider.




There are tons of example services out there for GET based queries (SOAP or REST oriented). Especially in the syndicated data feed arena (Atom, RSS, JSON). There are no decent examples of a WCF service that receives a data feed. At least none that I can find. I need to write a service that accepts data feeds, Atom, RSS and POX primarily. The service will validate the data and return any validation errors to the sender. The data will then be processed in some manner.

Does anyone have any links to examples for this? I'm not 'consuming' another feed, data is being pushed to me, I'm not pulling it. And I want to leverage .Net 4.0 enhancements for dealing with Atom and other syndication formats.

I've thought about a RESTful service that just does WebInvoke, it has no WebGet methods, but that seems odd. And even there, all the examples I see are for one item at a time, There are no examples for receiving a whole feed of items. I'm coming up with several ideas, but they all seem like I'm building a one-off solution. Does anyone have any ideas that are more of a standard pattern or framework?




When I refer to the post at http://blogs.msdn.com/phaniraj/archive/2009/03/21/ado-net-data-services-friendly-feeds-mapping-clr-types.aspx which says that ado.net data services acutally allows the returned atom feed to be customized using: EntityPropertyMapping

However, when I use the entity framework which generates the class automatically, i place
[global::System.Data.Services.Common.EntityPropertyMapping("Latitude", "lat", "geo", "http://www.georss.org/georss", true)]
[global::System.Data.Services.Common.EntityPropertyMapping("Longitude", "long", "geo", "http://www.georss.org/georss", true)]
on top of the partial class generated to have additional customized tag to make the returned feed GeoRss atom feed, but it doesn't work.

Do I need to config the mapping in the edmx file also? Does anyone know how should I achive this?
(using this method is because I can still query over the data but returned format is GeoRss)



I was reading about .atomsvc data feed definition files. I'm wondering if there is a sample  (and the data source) that I could look at to get an idea of how to create one. I searched on technet and msdn, but did not see any sample files.

Any help would be greatly appreciated.

Thank you





The website we are developing comes with an OData data feed which will be consumed by Excel PowerPivot. I'd like to create a relatively complex workbook for the API and offer that as a boilerplate project to my customers.


While I am working on the workbook it should connect to our development web server. Before giving the workbook to our customers, I need to change the URL for the data feed so that it points to our production web server. Is there any way to change the URL? I only know how to create new tables based on different feeds, but that way my excel spreadsheets still point to my development server tables.





I want to customize Author element in a feed with a Custom Data Service Provider. I use ResourceType.AddEntityPropertyMappingAttribute method. Do WCF Data Services support adding of several Author elements within single entry? If yes then how it can be done?


Here’s my problem. I am pulling data from RoseReport summaries using the data feed button on each page. I connect to 8 different data feeds this way and combine the imported data into a master PowerPivot table. Refreshing the PowerPivot connections works fine. At first.


The problem is that a day later, refreshing the PowerPivot data (ie, clicking Refresh All in the PowerPivot window) generates the following error for every data feed (except one):

Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: The remote server returned an error: (500) Internal Server Error..

An error occurred while processing the 'LegacyLink' table.

The operation has been cancelled.

Out of line object 'DataSourceView', referring to ID(s) 'Temp_DSV', has been specified but has not been used.


Also, checking the Table properties for these failing tables yields the error, "Cannot load table "Policheck". Make sure the data source is available, and that Source Table Name and Schema Name are valid. Details: Unable to obtain schema for data feed 'table1'. Please make sure this feed exists."


The reports are updated overnight, so something tells me that the updating process is causing the error (the one report that does not cause data feed problems is a little used report which I suspect is no longer active; the data has not changed in several days). This morning, for instance, everything was still working, then 2 hours later failing. Checking the reports, it appears they were updated in the interim.


Needless to say data feeds are useless if they fail when the underlying data is updated. There must be a solution, but I have no ideas.

I implemented an ADO.NET EF layer to expose a few tables from a SQL Server database. I would like to implement an aggregator data feed, similar to the listdata.svc in SharePoint, that exposes these tables so a client application can query each table over REST. How can I implement the aggregator service?

Here is what the output of the SharePoint listdata.svc in SharePoint looks like. Each collection element represents a SharePoint list.

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

-<service xml:base="http://elite/_vti_bin/listdata.svc/" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:app="http://www.w3.org/2007/app" xmlns="http://www.w3.org/2007/app">
-<collection href="Announcements">
-<collection href="Attachments">



I develop and host a WCF Data Services that delivers data via ATOM Feeds. I use the XmlHttpRequest object to load the data asynchronously. Unfortunately in IE 8 I always get an empty xml document. It seems to be a behavior (or better say bug?) of the IE that it does only load xml data when the response content-type is text/xml. The content-type of the ATOM feed is always something like application/atom+xml.

The idea I had is to set the response content-type to  text/xml in code. I tried to manage this in several ways, but the result remains that the content-type is always application/... .

Does anybody knows how to achieve this.

Any hints are welcome


Rolf Cerff


Product : Excel 2007 (also unseccesfully try with Excel 2010)


I had a look at the following features :


Usecase :

A XML file is hosted on a web server using authentication. In a new workbook, I add a datasource from the web using the data tab. I am prompted my login/password. Once authenticated, the preview is filled with the XML body. Once clicked on "import", I'm promted whether I want to fill the current sheet or add a new one with the data. I choose to add a new worksheet and click OK. A new worksheet is added to the workbook and filled with data from the XML file. I can right click on any cell in the table to update the data.

Then, I close the workbook. If I open it later and try to update the data, it fails displaying an error message : "Cannot find the data source" just as if the given URL is not a valid one. If I unzip the workbook package and open the connection file, the URL stored in it is the one provided; If I copy it and open it in any browser (IE, firefox, opera), the XML is displayed after authentication.

We tried to store the XML file on Apache and IIS web server. On IIS, we tried to change the authentication method (windows, simple). This was unsuccessful too. Note that independantly from the web server and the authentication method, it was always possible to access the XML file from a browser and data upate in Excel always failed after having closed the workbook once.

We also tried to manually change the connection setting according to ECMA  376 Office Open XML standard in the xlsx file. It hasn't worked yet.

We cannot afford disabling authentication as a safe access to data is required. I'havn't read much in MS knowledge about security and most about MS Sharepoint facilities.


Many thanks





In a VERY detailed explanation... could someone explain how to use to WIF to accomplish the following requirements:

A WCF service (SERVICE1) exists to provide data to clients using the NETTCP Transport Protocol A WPF client application will be used by employees An ASP.NET MVC web application be used by customers Employees need to connect from the WPF client application to SERVICE1 using their Active Directory user account (the computer IS on the domain) Employees need to connect from the WPF client application to SERVICE1 using their Active Directory user account (the computer IS NOT on the domain so the employee must provide their AD user account username/password) Customers need to connect from the ASP.NET MVC web application to SERVICE1 (possibly use another service for this) using forms-based authentication Employee user accounts need to be provisioned from within the WPF client application by an authorized employee... once the AD user account has been created Customer user accounts need to be provisioned from within the WPF client application by an authorized employee The application has a database with a users table where ALL user accounts are stored (AD users and customer user accounts) with a unique GUID... additional user information will be included here. The application will have many, many, many types of claims... employee user accounts will have different claims vs customer user accounts. Claims need to be managed/provisioned from within the WPF client application by an authorized employee Claims need to be available on the WPF client application and ASP.NET MVC web application for evaluation

Any number of servers/services can be installed if needed.
ADFS 2.0 can be installed if needed.
Any version of Windows Server 2008/2008 R2 can be installed if needed.
A certificate authority (CA) is in place and can be modified in any way needed.

Please do not refer me to any labs... I have looked at all of them. They are simple and not very helpful.



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure