Setting Up BCS Hybrid Features End to End In SharePoint 2013

For those of you who will be attending SharePoint Conference 2014 (www.sharepointconference.com), I’m going to be teaching a half-day course after the three main days with Bill Baer and some other folks on SharePoint Hybrid features.  This is firming up to be a pretty good session from a presenter standpoint, because we’ll have folks from SharePoint Marketing (Bill), other members of the SharePoint Product Group (Luca, Steve W. and Neil), the Microsoft Technology Center in Irvine (Ali), and our lead / best escalation engineer for SharePoint hybrid (Manas).  That’s a whole lot of hybrid expertise in one location so if you have a chance to attend check out the “SharePoint Server 2013 and Office 365 Hybrid” session in the listings at http://www.sharepointconference.com/content/pre-post-days.

One of the things that I’ll be covering in that session is getting the BCS hybrid features to work.  At a high level, the goal of the BCS hybrid features are to allow you to access your on-premises line of business data from within your Office 365 tenant.  As I’ve started scraping together some content for this topic, I realized that I did not see a really comprehensive “end-to-end” walk through of how to make this work; it’s really a series of documents and blog posts.  So…while getting a sample up and running in the lab I made a list of all the different resources I pulled info from to get things going.  In this post I’m just going to do an outline of what those steps and resources are; if there is need / demand still, then once we’ve delivered on the SharePoint Conference I may try and flesh this out with pictures, examples and more detailed instructions.  For now I’m hoping you can just follow the steps in order, track down the links I provide to pull together the data you need, and use that to build your BCS hybrid applications.

1.  Find / Create an OData data source:  See 2nd half of http://blogs.technet.com/b/speschka/archive/2012/12/06/using-odata-and-ects-in-sharepoint-2013.aspx
2.  Publish your OData endpoint (https):  you need to have an HTTP/HTTPS endpoint from which your OData content is accessible
3.  Create an External Content Type based on your OData data source:  http://msdn.microsoft.com/library/office/jj163967.aspx
4.  Make your ECT file “tenant ready”:  See 1st half of http://blogs.technet.com/b/speschka/archive/2012/12/06/using-odata-and-ects-in-sharepoint-2013.aspx
5.  Create a connection to your on premises services:  Follow the tips described here:  http://blogs.technet.com/b/speschka/archive/2013/04/01/troubleshooting-tips-for-hybrid-bcs-connections-between-office-365-and-sharepoint-2013-on-premises.aspx
6.  Upload your model (.ECT) file to o365 BCS:  Follow the rest of the tips here:  http://blogs.technet.com/b/speschka/archive/2013/04/01/troubleshooting-tips-for-hybrid-bcs-connections-between-office-365-and-sharepoint-2013-on-premises.aspx
7.  Create a new External List and use the BCS model you uploaded:  once your model is uploaded successfully you can create a new External List in o365 and use that to work with your on-premises LOB data.

Let me know how far this gets you, or the trouble spots you might run up against and we’ll see what we can do to address them as time permits.  If you’re coming to the SharePoint Conference hopefully I’ll get a chance to meet up and talk with you there.

Oh No – Your BCS Model Doesn’t Show Up When You Create a Content Source For BDC

First let me point out that I gratuitously co-mingled the terms “BCS” and “BDC” in the title because I honestly never know what to call this anymore (I’ve heard the marketing language before but it just ain’t stickin’).  So…today’s post is something that I thought I had added to the blog before, but searching the site is rendering no results so here we are with a new post – hopefully the first on this topic.  Event today have led me to believe that my gray matter may be a bit off.  So here’s the scenario:

You create a new BCS model; for example, you create a SharePoint App, and then create an ECT on top of it that you can use with your App or as an External Content Type for an External List (kind of along the lines I described here: http://blogs.technet.com/b/speschka/archive/2014/01/06/setting-up-bcs-hybrid-features-end-to-end-in-sharepoint-2013.aspx).  Now you go into the Search Service application and create a new content source so you can crawl it (because we won’t index an external list, since the content is rendered asynchronously).  You select the “Line of Business Data” content type, and then you get a message that says “There are no external data sources to choose from.” (please tell me some search engine will index that phrase)  Confusing as heck, because you can create an External List from your ECT and get data back, so it should work just great, right?

In the interest of avoiding sarcasm in response to my rhetorical question, let me just say “no”.  To be able to add it to a search content source, you need to add the following properties:

1. <Property Name=”ShowInSearchUI” Type=”System.String”></Property>  – to the <LobSystemInstances><LobSystemInstance><Properties> section.  This is near the top of the Xml.
2. <Property Name=”RootFinder” Type=”System.String”></Property> – to the properties for the Method that is used to read all items for the list.  By default it will be called “ReadAll{entityName}”, where “{entityName}” is the name of the table, like “Customers”.
3. <Property Name=”RootFinder” Type=”System.String”></Property> – to the properties for the MethodInstance that implements the method in #2; by default it will have the same name as #2.  You can also find this by searching for Type=”Finder” Default=”true” in your Xml.

Now, you DO NOT need to add an actual value to these properties when you plug them into your ECT; they are like flags, so they infer a value just by being present.  Once you’ve made these changes to your model you’ll need to delete the existing model and external content type (both done in the BDC / BCS admin page), then import your ECT again.  After you do that, your external list(s) that used it before should still work, and when you go into create a new content source in Search it should show up there as well.  Once you get that set up, remember to check the advice in this blog post to finish setting up the content source: http://blogs.technet.com/b/speschka/archive/2013/02/04/resolving-the-directory-links-across-partitions-are-not-allowed-error-when-crawling-odata-bdc-sources.aspx.

Another BCS Hybrid Tip – Fixing “The Type name for the Secure Store provider is not valid” Error

Here’s another error that are actually pretty likely to see if you are setting up BCS hybrid.  When you configure your BCS connection in o365 to connect to your on premises data source, one of the things you need to configure is the security that’s going to be used to make the call into your data source.  In many cases you will want to use a Secure Store Service application and keep a set of credentials in there.  You can configure your connection to do that just fine, but when you try and import your data model in o365 you will get an error message that says “The type name for the secure store provider is not valid”.  If you look into this error further in your on prem ULS logs you will see something to the effect that it’s asking for version 16.0.0.0 of the secure store assembly.  That’s the version that’s running in o365 today, but on premises today you have version 15.0.0.0.

After looking at some different options, we ultimately decided that for now the best way to work around this problem is to add an assembly binding redirect to the web application in the on premises farm.  When I say “the web application”, what I mean is the web application your BCS connection in o365 points at in the connection.  In the connection itself, you will need to have an endpoint for the client.svc, and to do that you use a web application that you have published through your reverse proxy.  So if your web application is at https://www.foo.com, then in BCS you would configure the endpoint as https://www.foo.com/_vti_bin/client.svc

So…in the web.config for that web application you would add an assembly redirect that looks like this:

<dependentAssembly xmlns=”urn:schemas-microsoft-com:asm.v1″>
 <assemblyIdentity name=”Microsoft.Office.SecureStoreService” publicKeyToken=”71e9bce111e9429c” culture=”neutral” />
 <bindingRedirect oldVersion=”16.0.0.0″ newVersion=”15.0.0.0″ />
</dependentAssembly>

I’ll have more details on this and all the rest at the post-SPC training that Bill Baer and I are doing.

Another Hybrid BCS Configuration Tip When Importing A BDC Model File

 

The hybrid tips are a little hard to come by, so when I find one I try and share.  This tip is for when you are trying to import a BDC model file into your o365 tenant.  When you import the model file, the import process may get stuck around 6% or so for a bit, and then come back with an error message that says something like this:  The following error occurred:  The internet facing URL for the LobSystem (External System) returned an authentication error.  Error was encountered at or just before Line: ‘57’ and Position: ‘20’.  What’s happening is when you import the BDC model, SharePoint makes a call out to the on-premises SharePoint farm via the Internet Facing Url you have defined in your BCS Connection.  When it does that, it uses attributes of the current user and tries to “rehydrate” that user using the user profile on the local farm (see http://blogs.technet.com/b/speschka/archive/2012/08/15/oauth-and-the-rehydrated-user-in-sharepoint-2013-how-d-they-do-that-and-what-do-i-need-to-know.aspx for details on rehydrating users). 

The reason why you would likely see this error is if you are logged into the o365 tenant admin site using an o365 account, like steve@mytenant.onmicrosoft.com.  In that case, when the call goes back into the on premises SharePoint farm, it’s unable to rehydrate the user because that user has no profile and no account in the local farm.  To remedy this error, you need to log into the o365 tenant admin site using an on-premise account.  Normally what you will do is after you have set up dirsync between on premises and o365, you will go into the list of users, select one or more of your on-premises users, and make them a Global Administrator.  Those users can then log into the o365 tenant admin site just like they do any o365 site – using ADFS or potentially just with their corporate credentials if you are syncing passwords to o365.  Again – you just want to make sure that whatever account you use to log into the o365 tenant admin site, that account has a populated user profile in the on premises farm.

Troubleshooting Tips for Hybrid BCS Connections Between Office 365 and SharePoint 2013 On Premises

Let me preface this posting by saying a couple of things:

  1. This is not going to be a “how do I create a BCS hybrid connection to my on-premises farm”; there is a whitepaper coming in the next month or so that will be lengthy and loaded with details on the step by step instructions for doing that.
  2. This posting is meant to be used either a) by people who are incredibly adventurous and want to try and create a BCS hybrid connection without any documentation or b) people who have waited until our documentation describing this process is out, they have followed it, but are still having troubles getting it to work.

What I’ve tried to capture in this posting are a number of hurdles and issues I had to overcome in order to get BCS hybrid working between my o365 tenant and my on-premises SharePoint 2013 farm.  As with my other posting on Troubleshooting Tips for High Trust Apps on SharePoint 2013 (http://blogs.technet.com/b/speschka/archive/2012/11/01/more-troubleshooting-tips-for-high-trust-apps-on-sharepoint-2013.aspx), this posting is a snapshot in time, but as we hear of other useful information I will come back and update this post.  So, that being said, here are some issues that we saw when getting things configured and some ideas of how you might be able to work around them:

 

  1. When you create a Connection Settings object (CSO) in your o365 tenant, you must provide a Url for your on-prem farm (the Internet-facing URL property).  o365 is going to reach out to that endpoint in order to invoke the BCS subsystem and connect to your data source.  Whatever Url you choose to publish and use for this purpose, when you configure it in your CSO you MUST add “/_vti_bin/client.svc” at the end of it in order to work correctly.  If you do not do this then BCS will report an error connecting to the on-premise data source.
  2. If you are using Secure Store Service (SSS) for credentials that will be used to connect to the oData endpoint, you must follow the steps described in this article in order to have it work:  http://social.technet.microsoft.com/wiki/contents/articles/3979.powerpivot-data-refresh-error-credentials-were-not-found-for-the-current-user-within-the-target-application-powerpivotunattended-please-set-the-credentials-for-the-current-user.aspx.   Yes, I know this article pertains to SharePoint 2010 and PowerPivot, but follow these steps or you will have an underlying access denied error when BCS tries to get your SSS application credentials.
  3. Follow the steps I describe here to create the model you will import into BCS in o365:  http://blogs.technet.com/b/speschka/archive/2012/12/06/using-odata-and-ects-in-sharepoint-2013.aspx.
  4. Since your model will be using your Connection Settings object that you create in o365 in order to connect to the on-premise data, there are some changes you need to make to it; if you do not do this then your model will not be able to connect to the on-premise data source. 
    1. To begin with, you should make a copy of the ECT file that you’ll be importing so you don’t break the version you have with your OData project. 
    2. Delete the ODataServiceMetadataUrl and ODataServiceMetadataAuthenticationMode properties from the LobSystem property list in the ECT file.
    3. Delete the ODataServiceUrl and ODataServiceAuthenticationMode properties from the LobSystemInstance property list in the ECT file.
    4. Add this property to the list of properties for both the LobSystem and LobSystemInstance:  <Property Name=”ODataConnectionSettingsId” Type=”System.String”>yourConnectionSettingsObjectName</Property>.  As my sample here implies, the property value must be the name of your Connection Settings object that I described in step 1.
  5. Before you try importing your BCS model into the o365 tenant you need to grant rights to current user to add models first, or you will get an “access denied at 0,0” error when importing the model.
  6. Make sure you grant Everyone at least Execute and Selectable in Client rights to BCS (or whomever you want to be able to connect to these on-premise data sources).  Use the Set Metadata Store Permissions button in the tenant BCS “Manage BCS Models” page.  If you don’t do this, you will get access denied errors for users that have not been granted these rights.
  7. I mentioned the Url you need to configure the CSO in step 1 above.  Any user that is going to be use a BCS hybrid connection must also be granted at least Read rights to the site collection that you use for the Internet-facing URL you configure in your CSO.  Otherwise they will get an access denied error if they try and use the data model; for example, if they try and view an External List based on the External Content Type created when you import the model.

 

That’s what I have for today, as I mentioned above, when/if we find other useful troubleshooting tips I will update this post. 

Resolving the Directory Links Across Partitions are not Allowed Error When Crawling OData BDC Sources

I’ve been seeing this error more and more recently, both inside and outside the walls of my employers.  The Directory Links across partitions are not allowed error seems to routinely crop up when you have created an OData source with Visual Studio and have uploaded the model to the BDC in SharePoint.  What seems to be happening then is you create a new content source in Search for the BDC and configure it to crawl all BDC applications.  Then whenever you do a crawl on that content source you get the error message described above.

I was fortunate enough to have Venkatesh work with me on this a bit and what we found is that if you change the content source to crawl the individual BDC applications instead of the entire catalog, the error went away.  For illustration purposes, here’s what this configuration looks like in the content source UI:

This should get you to the point where your crawls are able to complete successfully and you can find content when executing a query.  After that, things get a little more dicey…in all honesty right now the links for BDC search results don’t really appear to do anything useful, but that will be the next lump of coal I dig into.  Meanwhile, I know lots of people have been hitting this error so I wanted to share this while I have it, and then continue to flesh it out as we make progress.

Using OData and ECTs in SharePoint 2013

One of the nice enhancements in SharePoint 2013 BCS world is that SharePoint can now consume OData in BDC applications.  There are a couple of gaps I ran across recently though when going through this process so I thought I’d cover them here in case anyone else gets similarly stuck.  To start with, I recommend starting with this document to walk you through the basics of creating an application for OData:  http://msdn.microsoft.com/en-us/library/sharepoint/jj163967.aspx.  The main takeaway here is that you can NOT create a BDC application in SharePoint Designer that connects to an OData source – to do that you need to create an External Content Type (ECT) using a tool like Visual Studio.

The document I linked to above walks you through the process of creating the ECT.  It follows that by showing how to use those ECTs in a SharePoint App and deploying it in that manner, but it does NOT show what you do if you want to add it to the BDC catalog so that it can be used many site collections, and that’s where this post comes in.  The first thing to understand is that when you go through the process described in the article above, it will create one ECT for each entity (like a table).  The reason why that’s important to know is because they will use a shared name in the ECT file, which will prevent you from uploading more than one to the BDC catalog.  In order to use each of these entities in SharePoint here’s what you need to do:

  1. Right-click on the ECT file in Visual Studio and select Open With… then select XML (Text) Editor.  At the top of the document in the Model element you will see a Name attribute.  This value has to be unique between all the ECTs that you upload to the BDC, so you should change each one to a descriptive value for that entity, like “Customers Table”.
  2. You can, but don’t have to, change the Namespace of the Entity element, which is about 20 lines down in the document.  I changed mine to be consistent with the model name, but that’s just a style choice, it’s not required.
  3. Once you’ve made the changes and saved the file, you can upload the .ect file directly to the BDC.  Just use the default options – it’s a model – then click the OK button and you’re good to go.
  4. Once you’ve imported the models, don’t forget to grant permissions to folks to use them; kind of pointless without that.

One final thing worth noting here – out of the box you don’t get OData metadata endpoints over things like SQL databases, Azure Table Storage, etc.  Adding it for SQL is fortunately relatively easy.  In a nutshell you:

  1. Create a new Empty ASP.NET web application
  2. Add an ADO.NET Entity Data Model
  3. Add a WCF Data Service
  4. In your WCF Data Service you need to set the Type in the class constructor; this may be a litle confusing at first.  What you want to do is look for a file (that should be in the App_Code folder) that is named something like myDataConnection.Context.tt.  IMPORTANT:  There are usually TWO “.tt” files; find the “blah.Context.tt” file or you will end up using the wrong thing!  If you expand that, underneath you should see a myDataConnection.Context.cs class.  If you open that up you will see the two pieces of information you need for your WCF Data Service:  1) the class name, which you will use as the Type for the WCF Data Service class constructor.  2) The names of the entities it is supporting, implemented like get; set; properties.  You will need the entity names in the WCF Data Service as well, because at a minimum you need to create “SetEntitySetAccessRules” for each entity you want to expose.  This is explained in more detail in the comments when you add a WCF Data Service – I’m just trying to tell you where you go find the entity name to use when you create one of those rules.