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

  • Article

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 (https://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: https://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: https://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.
  • 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.
  • 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.
  • 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.