List Template Duplicate Field


Many of us have saved custom lists as templates in SharePoint Online or on-premises, to be re-purposed in the future, as an easy way to re-create the same list, with the same content types, metadata and structure. This is a an easy way for developers to give the end user a prepackaged custom list, rather than developing a provisioning tool to create a unique list. This added benefit to this method is the ability to allow the end-user to quickly create their own list based on a specific template, without the need to ask IT to do it for them.

In some rare cases, if you are trying to created a list from a custom list template, you may end up getting an error of a duplicate field name. The exact error will be "A duplicate field name was found". The list template has been created in the same site collection and you cannot find why and where is the other duplicate field name.
 

Cause

There are 2 possible causes to your issue:

  1. Your list template is referencing the same field twice
  2. Your list template is referencing a field tat already exist in the site collection but with a different GUID

Resolution

In my case, the second cause was my issue. The resolution for both issues is similar. Here are the steps:

Step 1 – Editing your template

  1. Save your template.tsp to your desktop
  2. Rename your template.tsp to template.cab
  3. Extract your template.cab to a specific location
  4. Edit the manifest.xml
  5. Look for the duplicate field name and delete the node.

This will do it for there are 2 nodes for the same field name. If this is not the case, then the GUID is your issue, and you will need to change the GUID in your template with the right GUID from your site collection. Remember that when you create a list from a custom template, if the any column already exist in the site collection, the GUID of the list template must match the GUID of the site column in the site collection.

Proceed with the following steps to resolve your issue:

Step 1.1

  1. Run the following PowerShell command to find the GUID of your site column
    $web = Get-SPWeb http://portal
    $columnName = "Your column name"
    write-host $web.Fields[$columnName].Title -nonewLine
    write-host " " $web.Fields[$columnName].Id
    $web.dispose
  2. Replace the GUID in the list with the GUID from the PowerShell. In most cases the GUID exists in 2 places in the list template
  3. Save the manifest.xml
     

Step 2

Now this is the fun/tricky part. You will need to assemble your manifest as a cab again. Run the following steps:

  1. Run command prompt and browse to the folder where your manifest.xml is
  2. Run the following command: makecab manifest.xml templatename.cab
  3. Rename your templatename.cab to templatename.stp
  4. Upload your new stp file to your list templates library

Problem solved!

 

Search Web Parts – Collab365 Global Conference

 

Have you heard about the virtual Collab365 Global Conference 2017 that’s streaming online November 1st – 2nd?

Join me and 120 other speakers from around the world who will be bringing you the very latest content around SharePoint, Office 365, Flow, PowerApps, Azure, OneDrive for Business and of course the increasingly popular Microsoft Teams. The event is produced by the Collab365 Community and is entirely free to attend.

Places are limited to 5000 so be quick and register now.

During the conference I'd love you to watch my session which is called : 'Search Web Parts'

Content Search Web Part (CSWP) is one of the great web parts in O365 and on-premises. In this session, Mike will demo how to configure a​nd use the CSWP, and build a dynamic O365 branded portal with CSWP only. In this session, we will review: 1.Creating Queries using Keyword Query Language (KQL) 2.Building dynamic queries 3.Creating and customizing HTML Display Templates​​.​

If you join me, you will learn:

  1. Creating Queries using Keyword Query Language (KQL)
  2. Building dynamic queries
  3. Creating and customizing HTML Display Templates
  4. Building Search Driven Portals

Topic(s):

  1. Office365
  2. SharePoint

Audience :

  1. IT Pro

Time (in UTC) :

  1. Thursday, November 2 2017 5:00 PM

How to attend :

  1. Register here.
  2. At the time listed above go here to watch my session. (you can also add me to your own personal planner from the agenda.
  3. Be ready to take notes!

Content Search Web Part Results Issues

 

Content Search Web Part (CSWP) is one of the great web parts introduced in SharePoint 2013 and Office 365. This web part uses the Search API and index to dynamically display results based on specific queries and rules on a content page.

The way it works is pretty simple; when a user navigates to a page, the web part initiates a query based on different inputs such as search box keyword, URL parameter, token, etc. You can also apply filters, refiners, and apply relevancy to the search results to make your SharePoint experience much more enjoyable.

I have spent a lot of time building dynamic portals, and, of course, at the same time troubleshooting issues why results do not show up. Let’s take an example where you want to build an image slider that loads the content from an image library. It’s pretty simple to build this in CSWP, where you pass the URL where you want to display, add some filters and voila, the results should show.

CSWP Query Builder

The Query can look like this:

path:"<URL of the library>“(IsDocument:"True" OR contentclass:"STS_ListItem")

CSWP Refiner

In the above example I am submitting a query to load from a specific URL and return only the items. This will exclude for example, the AllItems.aspx page.

Let’s say you can build a complete portal based on CSWP, however, our topic here is not how to build a CSQP, but to troubleshoot why CSWP does not display results for you.

But wait, what if you are not getting any results back? Here are few tips to help you troubleshoot the issue.

  1. Is the content crawled? Make sure the content you uploaded has been picked up by search. You need to wait before the content is indexed. A good test is to use the general search to check if your content is indexed. If it is, then the issue is something different.
  2. Too many filters in your query refiners. If you have too many refiners, start removing them and check if your query is returning results or not.

In my recent scenario, my content was indexed, showing up in the default search results, and I had no filters. In fact, just adding the CSWP with no extra queries in it did not return anything. Typically, when you add the CSWP, it will automatically show you this:

Default CSWP

After further investigation, looking at the way the portal is configured, I noticed something not adding up in the Alternate Access Mapping (AAM). The default AAM of the portal was not the same as the one it is being crawled. In my case the default AAM was http://portal and in the search content source I had http://portal.contoso.com.

Here’s the AHA moment! AAM did not match the content source; once I made sure that the default AAM and content source are a match, the content started showing up in my CSWP.

Create Publishing Sites with PowerShell

 

Often, we want to provision publishing sites within a site collection without using the interface. Adding publishing sites to a site collection is time consuming, and it gets more complicated if you are using SharePoint Variations. If you are using the Out of the Box Publishing Site template, you have no choice to pick any custom Page Layout you have created, but it uses the default out of the box Page Layout. Of course, you can create your own Publishing Site template and use it, but this takes some time to develop and you will need to deploy it across your WFE servers. Also, creating your own Publishing Site template will not work if you are using Variations. The target site during the Variation site provision will always use the default Publishing Site or Publishing Site with Workflow template.

This becomes very frustrating if you are creating many sites and you have to do it manually. Typically, in a variation site, let’s say we are doing an English and French variations, the site URLs for English and French are different. For example, the English URL of a specific site will be http://contoso/en/EnglishSite and in French, you may want it to be http://contoso/fr/FrenchSite. This different URLs is, most of the times, required in global organization where they need to have localization I their URLs. This process can very time consuming after creating the site because you have to perform many manual steps like:

  1. Changing Page Layout of the welcome page
  2. Changing the Title of the welcome page
  3. Adding any custom list to the sites
  4. Changing the URL of the target site
  5. Changing the Page Layout of the target site welcome page
  6. Changing the Title of the target site
  7. Changing the Title of the target site welcome page
  8. Adding any custom list to the sites

In one of the cases I have encountered, the number of steps were up to 40, which is insane if you have to repeat it 500 times. So, I came up with a script that can eliminate all of these steps with one single PowerShell script that read the info from a CSV file, provisions the site, then configure the sites in English and French. The CSV file has 5 main variables (of course you can/remove as many as you want):

  1. Provide the full path to the site to be provisioned
  2. Provide the title of the site
  3. Provide the Page Layout for the landing page
  4. Provide the unique URL of the French site
  5. Provide the French title

The script will perform the following actions:

  1. Create a log file of every action, in case there is an error
  2. Create the site
  3. Modify the page layout
  4. Change the title of the welcome page
  5. Wait until the Variation French site has been created to resume
  6. Change the French URL
  7. Change the French Title
  8. Change the Title of the welcome page

Here is the script, but you can download the script here along with the CSV file.

#Load sharepoint snapin

$snapin = Get-PSSnapin | Where-Object {$entry.Name -eq 'Microsoft.SharePoint.Powershell'}

if ($snapin -eq $null)

{

    Add-PSSnapin "Microsoft.SharePoint.Powershell"

}

#Define variables

$logMessage =""

$siteURL = "http://contoso"

$site = Get-SPSite $siteURL

$sitesCSV = Import-Csv C:\Publishing-Sites.csv

#sort the object so that top level sites get created before lower level ones in order to avoid errors

$sitesCSV = $sitesCSV | Sort-Object {$_.EN_URL}

#array to contain the sites that are created

$added = @()

#CSV file is now in memory delete the original so that a new one can be added.

Remove-Item D:\Hub\Hub-Sites.csv

$currentDate = get-date

$logFile = "C:\CreationLog.csv"

foreach($entry in $sitesCSV){

                $WebURL = $entry.EN_URL

                $WebURLFR = $entry.FR_URL

                $WebTitle = $entry.EN_Title

                $WebTitleFR = $entry.FR_Title

                $PageLayoutName = $entry.Template + ".aspx"

    #check row values for proper input

    #check if blank fields

    if(($webURL -eq "") -or ($WebTitle -eq "") -or ($PageLayoutName -eq ".aspx") -or ($WebURLFR -eq "") -or ($WebTitleFR -eq ""))

    {

        #blank fields found

        $row = "$currentDate,ERROR,Blank fields found,$entry"

        $row | Out-File -Append $logFile

        continue

    }

    #check if french URL is full URL or more than one level

    if($WebURLFR -contains "/")

    {

        #french URL is bad

        $row = "$currentDate,ERROR,Bad French URL,$entry"

        $row | Out-File -Append $logFile

        continue

    }

    #check for over 256 characters for title and truncate

    if(($WebTitle.length -gt 254) -or ($WebTitleFR.length -gt 254))

    {

                $WebTitle = $WebTitle.substring(0,253);

                $WebTitleFR = $WebTitleFR.substring(0,253);

    }

    #check if english URL is full URL

    if(($WebURL -like "http*") -eq $false)

    {

        #french URL is bad

        $row = "$currentDate,ERROR,Bad english URL,$entry"

        $row | Out-File -Append $logFile

        continue

    }

    #check if URL contains special characters

    if(($WebURL.Replace("http://","") -notmatch "^[a-zA-Z0-9\s-]") -or ($WebURLFR -notmatch "^[a-zA-Z0-9\s-]"))

    {

        $row = "$currentDate,ERROR,URL contains special characters,$entry"

        $row | Out-File -Append $logFile

        continue

    }

    #check if site exists

    $exists = (Get-SPWeb $WebURL -ErrorAction SilentlyContinue) -ne $null

    if($exists -eq $true)

    {

        #site exists. Write to log file and move to next entry

        $row = "$currentDate,WARNING,Duplicate Site,$entry"

        $row | Out-File -Append $logFile

        continue

    }

    try{

                #Create English Site

        $logMessage = "Creating site"

                New-SPWEB -Url $WebUrl -Template CMSPUBLISHING#0 -Name $WebTitle -AddToQuickLaunch -UseParentTopNav -erroraction stop

                $web = Get-SPWeb $WebUrl

                #Enable French alternate language

        $logMessage = "Setting english alternate language"

                $web.IsMultilingual = $true;

                $web.AddSupportedUICulture(1036);

                $web.update();

                #Get Publishing Site and Web

        $logMessage = "Getting english publishing site"

                $PublishingSite = New-Object Microsoft.SharePoint.Publishing.PublishingSite($Web.Site)

                $PublishingWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($web)

                #Get New Page Layout

        $logMessage = "Getting english page layout"

                $SitePageLayouts = $PublishingSite.GetPageLayouts($false)

                $NewPageLayout = $SitePageLayouts | ? {$_.Name -eq $PageLayoutName}

                #update landing page Page Layout and title

        $logMessage = "Setting English layout and title"

                $PublishingPage = $PublishingWeb.GetPublishingPage($WebURL + "/Pages/default.aspx") 

                $PublishingPage.CheckOut()

                $PublishingPage.Title = $WebTitle

        if($NewPageLayout -eq $null)

        {

            $row = "$currentDate,WARNING,Bad Template name,$entry"

            $row | Out-File -Append $logFile

        }

        else

        {

                    $PublishingPage.Layout = $NewPageLayout

        }

                    $PublishingPage.ListItem.Update();

                    $PublishingPage.CheckIn("Page layout Updated via PowerShell")

                $web.Update()

        #add the row to the added array

        $added += $entry

        #log site creation

        $row = "$currentDate,INFO,English Site Created,$entry"

        $row | Out-File -Append $logFile

    }

    catch

    {

        #there was an error.  Log error message to log file and move to next entry

        $row = "$currentDate,ERROR,$logMessage,$entry"

        $row | Out-File -Append $logFile

        continue

    }

    finally

    {

                $web.dispose()

    }   

}

if($added.length -le 0)

{

    return

}

#wait 5 minutes for variations to be created

Start-Sleep -Seconds 300

#start french site creation

$logMessage = "Start french site creation"

$siteURL = "http://contoso"

$RelationShipListName = "Relationships List"

$FieldName = "ObjectID"

$site = Get-SPSite $siteURL

$spWeb = Get-SPWeb $SiteURL

#iterate through @added as it now contains all the sites that have been created

foreach ($frenchRow in $added)

{  

                try{

        $numberOfTries = 0

        $exists = $true

        do{

            $spRelationshipList = $spweb.GetList($RelationShipListName)

            $ENUrl = $frenchRow.EN_URL

                    $FRTitle = $frenchRow.Fr_Title

                    $NewFRUrl = $frenchRow.FR_URL

                    #Get French site URL

                    foreach($EN_Item in $spRelationshipList.Items)

                    {

                                $EN_hyperlink = New-Object Microsoft.SharePoint.SPFieldUrlValue($EN_Item[$FieldName])

                                if ($EN_hyperlink.URL -eq $ENUrl)

                                {

                                                $GroupGUID = $EN_Item["GroupGuid"]

                                                $ID = $EN_Item[“ID”]

                                }

                    }

 

                    foreach($FR_Item in $spRelationshipList.Items)

                    {

                                $FR_hyperlink = New-Object Microsoft.SharePoint.SPFieldUrlValue($FR_Item[$FieldName])

                                if (($GroupGUID -eq $FR_Item["GroupGuid"]) -and ($ID -ne $FR_Item[“ID”]))

                                {

                                                $CurrFRUrl = $FR_hyperlink.URL

                                }

                    }           

            #has variation been created? 

            #if not we wait 5 minutes for up to two tries in order to let the variation timer job do its thing

            $exists = (Get-SPWeb $CurrFRUrl -ErrorAction SilentlyContinue) -ne $null

            if($exists -eq $false)

            {

                Start-Sleep -Seconds 300

            }

            $numberOfTries++

        }while(($numberOfTries -le 6) -and ($exists -eq $false))

        if($exists -eq $false)

        {

            $row = "$currentDate,ERROR,Variation does not exist,$entry.ToString()"

            $row | Out-File -Append $logFile

            continue

        }

                    #Change the site Title

            $logMessage = "Change french title"

                    $web = Get-SPWeb $CurrFRUrl

                    $web.Title = $FRTitle        

                    #Enable English alternate language

            $logMessage = "Enable French site alternate language"

                    $web.IsMultilingual=$true

                    $web.AddSupportedUICulture(1033)

                    $web.Update()

                    #Get Publishing Site and Web

            $logMessage = "Get french puglishing site"

                    $PublishingSite = New-Object Microsoft.SharePoint.Publishing.PublishingSite($Web.Site)

                    $PublishingWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($web)

                    #update landing page  title

            $logMessage = "Update french landing page title"

                    $PublishingPage = $PublishingWeb.GetPublishingPage($CurrFRUrl + "/Pages/default.aspx") 

                    $PublishingPage.CheckOut()

                    $PublishingPage.Title = $FRTitle

                    $PublishingPage.ListItem.Update();

                    $PublishingPage.CheckIn("Page layout Updated via PowerShell")

                    $web.Update()

            $row = "$currentDate,INFO,French Site Customized,$entry"

            $row | Out-File -Append $logFile

    }

    catch

    {

        $row = "$currentDate,ERROR,$logMessage,$entry.ToString()"

        $row | Out-File -Append $logFile

        continue

    }

    finally

    {             

                    $web.dispose()

    }

}

#sort the @added array so that we can change the URL without breaking

$added = $added | Sort-Object {$_.EN_URL} -Descending

$logMessage = "Change French URL"

#iterate through @added a second time in order to change the URLs of the french sites now that they have been customized

foreach ($frenchRow in $added)

{

    $spRelationshipList = $spweb.GetList($RelationShipListName)

                try{

        $ENUrl = $frenchRow.EN_URL

                    $FRTitle = $frenchRow.Fr_Title

                    $NewFRUrl = $frenchRow.FR_URL

                    #Get French site URL

                    foreach($EN_Item in $spRelationshipList.Items)

                    {

                                    $EN_hyperlink = New-Object Microsoft.SharePoint.SPFieldUrlValue($EN_Item[$FieldName])

                                    if ($EN_hyperlink.URL -eq $ENUrl)

                                    {

                                                    $GroupGUID = $EN_Item["GroupGuid"]

                                                    $ID = $EN_Item[“ID”]

                                    }

                    }

                    foreach($FR_Item in $spRelationshipList.Items)

                    {

                                    $FR_hyperlink = New-Object Microsoft.SharePoint.SPFieldUrlValue($FR_Item[$FieldName])

                                    if (($GroupGUID -eq $FR_Item["GroupGuid"]) -and ($ID -ne $FR_Item[“ID”]))

                                    {

                                                    $CurrFRUrl = $FR_hyperlink.URL

                                    }

                    }

                    $web = Get-SPWeb $CurrFRUrl

        #Change the French Site URL

                    Get-SPWeb $CurrFRUrl | Set-SPWeb -RelativeURL $NewFRUrl -ErrorAction Stop

        $web.Update()

        $row = "$currentDate,INFO,French Site URL Changed,$entry"

        $row | Out-File -Append $logFile

    }

    catch

    {

        $row = "$currentDate,ERROR,$logMessage,$entry.ToString()"

        $row | Out-File -Append $logFile

        continue

    }

    finally

    {

                    $web.dispose()

    }

}

Hybrid BCS Part 5 – Validation

Now that you have created an external list or deployed an app for SharePoint in SharePoint Online, you need to test the security you put in place. Every account that will be accessing and manipulating the external data must have three properties:

  1. It must have user or greater permissions to the SharePoint Online site and the external list or app for SharePoint.
  2. It must be a federated account.
  3. It must be a member of the on-premises global security group that you are using to control access to the OData service endpoint. For example, it must be a member of ODataGroup.

In this procedure, you will open the SharePoint Online site and the external list or app for SharePoint with four different accounts.

To validate security on the BCS hybrid

  1. Identify or create one account for each of the account types listed in the following table.

Account

Expected outcome

Troubleshooting step

Account A

  1. Has site/list/app permissions.
  2. Is federated.
  3. Is a member of the on-premises global security group (ODataGroup).

External data displayed and editable.

If the external data does not display or you cannot edit it, check the site permissions, your federation setup, and the membership of your on-premises global security group; for example, the ODataGroup.

Account B

  1. Does not have site/list/app permissions.
  2. Is federated.
  3. Is a member of the on-premises global security group (ODataGroup).

External data does not display.

If the external data does display and you can edit it, check the site/list/app permissions.

Account C

  1. Has site/list/app permissions.
  2. Is not federated (is an Office 365 account only).
  3. Cannot be added to the on-premises global security group (ODataGroup).

External data does not display.

If the external data does display and you can edit it, check your federation setup and membership of your on-premises global security group (Odata Group).

Account D

  1. Has site/list/app permissions.
  2. Is federated.
  3. Is not a member of your on-premises global security group (ODataGroup).

External data does not display.

If the external data does display and you can edit it, check the membership of your on-premises global security group (ODataGroup) and the permissions that you set on the OData service endpoint that you configure in Prepare the SharePoint Online environment for the Business Connectivity Services hybrid scenario

 

  1. Open (by using In-Private browsing if possible) the SharePoint Online site that contains the external list or app for SharePoint by using each of the accounts in turn. Be sure to completely log out and close your browser in between tests.
  2. If you don’t see the expected outcome, refer to the troubleshooting step in the previous table, fix the issue, and repeat all four tests until you achieve the expected outcome.

If you see the error message:

ResourceBudgetExceeded, sending throttled status code. Exception=Microsoft.SharePoint.SPResourceBudgetExceededException: ResourceBudgetExceeded at Microsoft.SharePoint.SPResourceTally.Check(Int32 value) at Microsoft.SharePoint.SPAggregateResourceTally.Check(SPResourceKind kind, Int32 value) at Microsoft.SharePoint.Client.SPClientServiceHost.OnBeginRequest()

 

You can either remove the throttling:

$webapp = Get-SPWebApplication -Identity http://<URL of your on-premises farm>
$rule = $webapp.AppResourceTrackingSettings.Rules.Get([Microsoft.SharePoint.SPResourceKind]::ClientServiceRequestDuration)$rule.Remove()

 

Or change the throttling value:

$webapp = Get-SPWebApplication -Identity http://<URL of your on-premises farm>
$webapp. AppResourceTrackingSettings.Rules.Add([Microsoft.SharePoint.SPResourceKind]::ClientServiceRequestDuration, 150000, 150000)$webapp.AppResourceTrackingSettings.WindowCount = 10$webapp.AppResourceTrackingSettings.WindowSize = [System.TimeSpan]::FromSeconds(30)
$webapp.Update()

Note: 150000 is time in ms (ergo, 150 seconds).

 

Troubleshooting the hybrid configuration

  1. Verify that the external data source is running and accessible
  2. Verify that one-way outbound or two-way authentication is working
  3. Verify configuration steps presented earlier
  4. Check logs written by the Unified Logging Service (ULS logs or trace logs)

<< Previous – Part 4 – Connect to on-premises services

Hybrid BCS Part 4 – Connect to on-premises services

 

Setting a hybrid connection between you SharePoint Online and on-premises required communication trusts between the 2 farms. Refer to this article Configuring Hybrid Infrastructure for more details on how to configure your hybrid infrastructure.

Validate external access to reverse proxy

At this point in deploying the BCS hybrid scenario, you should confirm that you can access your on-premises SharePoint 2013 farm that has been configured to receive hybrid calls from SharePoint Online.

To confirm access to external URL:

  1. Copy the certificate to your extranet computer, and then click the certificate. You will be prompted for the certificate password. This adds the certificate to your personal certificate store.
  2. Open a web browser and browse to the externally published URL of your on-premises farm. You should be prompted for credentials. If not, check your browser settings and make sure that your logged on credentials are not being automatically passed.
  3. Provide the credentials of the federated user. This log on must succeed and you should see the published site. If this does not work, contact the administrators who set up your hybrid infrastructure. Do not proceed any further with the BCS hybrid scenario until this issue is resolved.

Prepare your tenant environment

In order to allow your SharePoint Online tenant to connect to your on-premises tenant, you will need to configure your security to allow accepting connection to your services. The following steps are an example of what you need to establish a trust between your Online and on-premises tenants.

  1. Click a service account that will access the OData service endpoint that you have previously configured; this procedure will be called ODataAccount
  2. Create a global security group for your OData service endpoint; this procedure will be called ODataGroup
  3. Add the service account to the global security group

Configure Secure Store target application

  1. Go to your on-premises tenant SharePoint Central Administration
  2. Navigate to Application Management > Manage service applications
  3. Click the Secure StoreI
  4. if you have never used your Secure Store, you will need to generate a new key first
  5. Click on Generate New Key
  6. Enter your Passphrase then click OK
  7. Click on New under Manage Target Applications
  8. Enter the name of your application, the display name, and your email address
  9. Select Group in the Target Application Type
  10. Click Next
  11. Accept the defaults values in the Create New Secure Store Target Application page
  12. Click Next
  13. Enter the Farm Administrator account in the Target Application Administrators, and enter the group name you created for you OData Service Endpoint in the Members section
  14. Click OK
  15. Click OK

Important:

A pass phrase string must be at least eight characters and must have at least three of the following four elements:

  1. Uppercase characters
  2. Lowercase characters
  3. Numerals
  4. Any of the following special characters
    "! " # $ % & ' ( ) * + , – . / : ; < = > ? @ [ \ ] ^ _ ` { | } ~

Important:

The pass phrase that you enter is not stored. Make sure that you write this down and store it in a safe place. You must have it to refresh the key, such as when you add a new application server to the server farm.

For security precautions or as part of regular maintenance you may decide to generate a new encryption key and force the Secure Store Service to be re-encrypted based on the new key. You can use this same procedure to do this.

Caution:

You should back up the database of the Secure Store Service application before generating a new key.

Set Permissions on your Online BCS

Setting your permissions on your Online BDC Metadata store is different than the on-premises tenant.

    1. Open your SharePoint Online administration page
    2. Click on bcs
    3. Click on Manage BDC Models and External Content Types
    4. Under Permissions, click on Set Metadata Store Permissions
    5. Select All users then set the Execute
    6. Select the checkbox to Propagate permissions to all BDC Model
    7. Click OK

Connect your O365 BCS to your on-premises

Unlike BCS in SharePoint 2013, BCS in SharePoint Online requires that you configure a connection settings object (CSO), which contains additional information to establish the connection to the external system and the OData source you have created.

When you create a CSO in your SharePoint Online tenant, you must provide a URL for your on-premises farm (the external URL you have configured in your reverse proxy to connect to your internal SharePoint services). Your SharePoint Online tenant will try to reach out to that endpoint in order to invoke your on-premises BCS and connect to your data source.

Whatever URL you may choose to publish, your CSO must have /_vti_bin/client.svc at the end of the URL in order to work properly.

Before you begin this procedure, make sure you have the following:

  1. Install configuration tools on an on-premises web server.
  2. The ID of the Secure Store target application that you configured.
  3. The Internet-facing URL that Office 365 uses to connect to the service address and that was published by the reverse proxy
  4. The ID of the Secure Store target application for the Secure Channel certificate in Office 365.

To create a CSO to your on-premises tenant:

  1. Open your SharePoint Online administration page
  2. Click on bcs
  3. Click Manage connections to on-premises services
  4. Click Add
  5. Enter the Title and the OData Service Address URL
  6. Under Authentication, select Use credentials stored in SharePoint on-premises
  7. Enter the Secure Store Target Application ID
  8. Under Authentication Mode, select Impersonate Window’s Identity
  9. Enter the internet facing URL you have configured under the reverse proxy; make sure you include /_vti_bin/client.svc at the end of the URL
  10. Click Create

Since your model will be using your Connection Settings object that you create in your SharePoint Online in order to connect to the on-premises 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-premises data source:

  1. 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>

Import your ECT file to SharePoint Online

Similar to the steps for your on-premises tenant, you need to import your new ECT file you have modified in the previous section to your SharePoint Online BCS tenant.

  1. Open your SharePoint Online administration page
  2. Click on bcs
  3. Click on Manage your BDC Models and External Content Types
  4. Click on Import
  5. Navigate to your ECT file locations and import each ECT file; as an alternative solution, you can develop a PowerShell script to import all the files
  6. Click OK

Once your model is uploaded successfully you can create a new External List in SharePoint Online and use that to work with your on-premises LOB data.

Follow this article to create an External List.

 

<< Previous – Part 3 – External Content Type Configuration 

>> Next – Part 5 – Validation

Configuring Hybrid Infrastructure

 

Setting up SharePoint on-premises requires that you meet basic SSO deployment requirements and then configure SharePoint 2013 services and inbound requests.

When you set up and enable SSO, users in your organization are able to use their corporate credentials to access the Office 365 service offerings. This removes the burden of managing multiple logon identities and passwords. Without SSO, an Office 365 user would have to maintain separate user names and passwords. For an even better end-user experience, you can create and deploy smart links, which can help speed user sign-in requests by reducing the number of redirects necessary for authentication.

In addition to user advantages, administrators and the organization can also benefit from SSO. For example, configuring SSO helps to enforce the organization’s password policies and account restrictions in both the on-premises directory and the Office 365 directory.

To prepare, you must make sure the environment meets the requirements for SSO and verify that the Active Directory and Azure Active Directory tenant is set up in a way that is compatible with single sign-on requirements. Also, Active Directory must be deployed and running in Windows Server 2003 R2, Windows Server 2008, Windows Server 2008 R2, Windows Server 2012, or Windows Server 2012 R2 with a functional level of mixed or native mode. If you plan to use AD FS as your STS, you will need to do one of the following:

  1. Download, install, and deploy AD FS 2.0 on a Windows Server 2008 or Windows Server 2008 R2 server.
  2. Deploy an AD FS 2.0 proxy, if users will be connecting from outside the company network.
  3. Install the AD FS role service on a Windows Server 2012 or Windows Server 2012 R2 server.

In addition, Active Directory must have certain settings configured to work properly with single sign-on. In particular, the UPN, or the user logon name, must be set up in a specific way for each user.

 

Configure SharePoint 2013 services

You need to configure the User Profile Service to synchronize user and group profiles from the on-premises Active Directory domain. When federated users access resources in a hybrid environment, the STS makes calls to the User Profile Service to obtain user account metadata, such as the UPN and email property values. This metadata is used by the STS to construct security tokens during the authentication process.

SharePoint Online presents claims to the on-premises SharePoint farm by using the Simple Mail Transfer Protocol (SMTP). To support this, you need to ensure that the SharePoint user profiles for all federated users are populated with the user’s email address by using the correct UPN.

This means that the work email field in the on-premises SharePoint User Profile Store needs to contain the user’s federated email address. For example, if a federated user logs on to the on-premises domain as contoso\karenb and the public domain for the hybrid environment is contoso.com, her federated email address is [email protected].

You must verify that the App Management and Microsoft SharePoint Foundation Subscription Settings services are started and configured. These services must be enabled to support certain configuration procedures, and to help register SharePoint Online as a high-trust application in SharePoint 2013.

 

Configure SharePoint 2013 for inbound requests

If the hybrid environment is configured for an inbound authentication topology, you must ensure that a single on-premises web application is configured to receive requests from SharePoint Online. This web application is referred to as the primary web application for the hybrid environment, and it accepts requests from the external endpoint URL. No specific web application configuration is required to support a one-way outbound authentication topology.

In a SharePoint Server 2013 hybrid environment, outbound connections can be made from any on-premises web application. A single SharePoint Server 2013 web application must be configured for inbound connections; it is used as the primary web application for accepting inbound connections and configuring services and connection objects for the hybrid features you deploy. You can either create a new web application and site collection or configure an existing web application for this purpose.

 

Configure SharePoint Online

Setting up SharePoint Online requires that you choose an application authentication topology and make additional configuration choices for the service.

 

Choose authentication topology

As shown in Figure 1, your choice of an authentication topology determines how certificates are configured and what capabilities are present in the hybrid solution.

Configuring Hybrid Infrastructure
Configuring Hybrid Infrastructure

Figure 1: Application authentication topologies

One-way outbound topology

One-way outbound topology is not supported with hybrid BCS. Only one-way inbound and two-way (bidirectional) topologies.

One-way inbound topology

A one-way inbound hybrid topology enables SharePoint Online to connect to SharePoint Server 2013 through a reverse proxy device (Figure 2). For example, users of a SharePoint Online Search portal can see both local and remote search results, but only local results are available in the SharePoint Server 2013 Search portal.

SharePoint Hybrid One Way Trust
SharePoint Hybrid One Way Trust

Figure 2: One-way inbound topology

A one-way inbound topology can be configured to let users access on-premises SharePoint search results from the Internet, as long as they have access to the intranet through a virtual private network or DirectAccess.

  1. On-premises SharePoint Server 2013 Enterprise Search portal: Local search results are available
  2. SharePoint Online Search portal: Local and remote search results are available
Two-way (bidirectional) topology

A two-way topology enables bidirectional hybrid service integration between the on-premises SharePoint Server 2013 farm environment and the Office 365 tenant (Figure 3). For example, search can be configured to allow federated users to see both local and remote search results in either SharePoint Server 2013 or SharePoint Online Search portals.

SharePoint Hybrid Two Way Trust
SharePoint Hybrid Two Way Trust

Figure 3: Two-way (bidirectional) topology

A two-way topology can be configured to let users access on-premises SharePoint search results from the Internet, as long as they have access to the intranet through a virtual private network or DirectAccess.

  1. On-premises SharePoint Server 2013 Enterprise Search portal and SharePoint Online Search portal: Local and remote search results are available.
  2. If extranet authentication services are configured, extranet users can log on remotely through an on-premises Active Directory account and use all available hybrid functionality.

 

Refer to these articles to configure your reverse proxy server and how to establish a secure connection between your Online and on-premises tenants.

Link to procedure Description of procedure
Configure a one-way inbound hybrid topology Learn how to configure the infrastructure for SharePoint 2013 hybrid environments that use a one-way inbound authentication topology.
Configure a two-way bidirectional hybrid topology Learn how to configure the infrastructure for SharePoint 2013 hybrid environments using a two-way authentication topology.

 

 

Hybrid BCS Part 3 – External Content Type Configuration

 

Next, you will need to create an External Content Type (ECT) based on the OData source. We need to reiterate here that Hybrid BCS implementation only work with OData source based ECT. The ECT can only be created with Visual Studio and not with SharePoint Designer.

To create an ECT, you need the following requirements:

  1. SharePoint Server 2013
  2. Visual Studio 2013
  3. Office Developer Tool for Visual Studio 2013
  4. OData service that can be accessed from the internet

Create new SharePoint App

Using Visual Studio 2013, create a new SharePoint App:

  1. Under the Office/SharePoint templates, select App for SharePoint to create a new projectNew SharePoint App
  2. Name your project and click OK
  3. Select your local on-premises SharePoint URL to debug your project
  4. Select SharePoint-hosted
    Note:     SharePoint-hosted apps, or apps where all components are hosted on either an on-premises or Office 365 SharePoint farm. SharePoint-hosted apps are installed on a SharePoint 2013 website, called the host web.
  5. Click Finish
  6. In the Solution Explorer, click on Add à Content Types for an External Data Source
  7. Enter the URL of your OData service you have published in the previous Section and choose a Name for it
  8. Click Next
  9. Select the one or more data entity
  10. Click Finish

This process will create the External Content Type for each of the entity you have selected.

Once you have created your External Content Type, you want to add the ECT to your Business Data Catalog (BDC) catalog so you can use it in your site collections.

In the previous section, we explain how you create an ECT, where each entity in the OData source represents a single ECT. However, the entities use a shared name in the ECT file, which will prevent you from uploading more than one entity to the BDC catalog. In order to fix this issue, you need to follow these steps to be able to use the entities in SharePoint:

  1. From Visual Studio, right click on the ECT file, and select Open with. Each entity will have its own .ect file located under “External Content Types\<folder name>”.
  2. Select XML (Text) Editor and click OK
  3. In the top of the document, within the Model element, you will see a Name attribute. This Name attribute is the name you have selected when you connected to the OData source. For example it can look like NorthwindCustomersModel. The value of this Name is the same in all of the ECT files created from the entities, but it has to be unique in order to use it in SharePoint. You will need to change the name based on the ECT you are using. For example, it will be something like Categories Table or Employees Table, etc…
  4. Change the name in each of the ECT file
  5. Change the name of the Namespace in the Entity element (optional)
  6. Save the ECT file

Upload ECT to the BDC Model

Once you have made the changes to all of your ECT files, you can upload all your entities to your BDC model. To do so:

  1. Go to your SharePoint Central Administration
  2. Navigate to Application Management à Manage service applications
  3. Click on your BDC Model
  4. Click on Import
  5. Navigate to your ECT file locations and import each ECT file; as an alternative solution, you can develop a PowerShell script to import all the files
  6. Click OK
  7. Repeat the above steps for all your models
  8. Once you have imported the BDC models, you will need to grant permissions for users to use them
  9. On each of the model, click on the dropdown menu and select Set Permissions
  10. Select the right permission you need to give for each group

<< Previous – Part 2 – Create an OData Source 

>> Next – Part 4 – Connect to on-premises services

Introduction to Hybrid BCS Architecture – Part 1

 

In the past few months, I have been asked many times to architect and design a Hybrid BCS Architecture between SharePoint Online and on-premises, and lately I was asked if I can document it. Given the high demand of such topic, and the fact that the information to implement it is scattered in multiple places, I thought it will be great to share it with the community, and have my blog is a one stop place to follow instructions on how to implement a BCS Hybrid Architecture.

So here it is, this will be a series of blog articles divided by major topic to help you follow and implement Hybrid BCS.

Introduction to hybrid computing

Today’s organizations face significant challenges, including driving IT efficiency and business value in the face of increased pressure to comply with regulations. The goal of any hybridization—or the combining of two related but dissimilar entities—is to gain leverage from the strengths of both parts, while minimizing the components’ weaknesses.

Hybrid computing is based on a computing model that allows organizations to use a combination of traditional and cloud computing environments to achieve a higher degree of flexibility, rather than forcing a choice between either an on-premises or cloud model.

Why hybrid SharePoint?

Organizations can use Microsoft SharePoint Online and SharePoint on-premises to achieve a hybrid computing model. With hybrid SharePoint, these organizations can start to realize the benefits associated with the use of cloud computing—coupled with the flexibility to customize the environment and govern data as tightly as in an on-premises system—while delivering a consistent experience to users. Figure 1 shows some of the most immediate benefits, including:

  1. Maintain consistency across clouds with familiar tools and resources.
  2. Extend your data center with a consistent management toolset and familiar development and identity solutions.
  3. Provide enterprise-grade performance and security in the data center and in the cloud.
  4. Meet changing business needs with greater flexibility.
  5. Deliver capacity on demand.

Benefits of Hybrid Environment
Figure 1: Benefits of a hybrid SharePoint environment

How do SharePoint Online and SharePoint on-premises coexist?

Hybrid environments can be helpful when it is not possible for an organization to migrate to the cloud immediately or in full due to business, technical, or other reasons. Cloud services such as SharePoint Online in Microsoft Office 365 can be an attractive alternative to on-premises SharePoint business solutions, but you might find that you need to deploy only specific solutions in the cloud while still maintaining your on-premises SharePoint farm. New functionality in Microsoft SharePoint Server 2013 and SharePoint Online enables you to integrate services like Search, Business Connectivity Services (BCS), and Duet Enterprise Online across the on-premises/cloud boundary.

Introduction to Business Connectivity Service

The Business Connectivity Service (BCS) is a centralized infrastructure in SharePoint 2013 and Office 2013 that supports integrated data solutions. With Business Connectivity Services, you can use SharePoint 2013 and Office 2013 clients as interfaces into data that doesn’t live in SharePoint 2013 itself. For example, this external data may be in a database and it is accessed by using the out-of-the-box Business Connectivity Services connector for that database. Business Connectivity Services can also connect to data that is available through a web service, or data that is published as an OData source or many other types of external data. Business Connectivity Services does this through out-of-the box or custom connectors. The connectors, as the name implies, are the communication bridge between SharePoint 2013 and the external system that hosts the external data.

At the most fundamental level, every Business Connectivity Services configuration is driven by the location of the Business Connectivity Services infrastructure and the location of the external system that hosts the external data. There are only three ways that the Business Connectivity Services infrastructure and the external system can relate to one another. The Business Connectivity Services infrastructure and the external system can be on your corporate network (all-on-premises), or both the Business Connectivity Services infrastructure and the external system are in the cloud (cloud only).

An on-premises solution is one where the SharePoint 2013 farm and the external system are both behind a company’s firewall and live in company-controlled data centers and users will have to be on the company’s network to access it.

With a cloud-only configuration, Business Connectivity Services in a SharePoint Online tenancy can access data from various cloud services. For example, SharePoint Online can access data from a third-party stock quotes service or from the Windows Azure Marketplace Data Market by using the Business Connectivity Services web service connector. Because this type of solution doesn’t include any customer-maintained SharePoint 2013 farms and hardware and consists of only cloud-based services, it is called a cloud-only solution.

A BCS hybrid solution makes use of SharePoint Online and SharePoint 2013 on-premises. It integrates data from an on-premises OData service endpoint into a SharePoint Online tenancy.

The BCS hybrid solution looks as shown in the following diagram.

Hybrid BCS

>> Next – Part 2 – Create an OData Source