Skip Ribbon Commands
Skip to main content

Blog

:

SharePoint Designer > Blog
Penelope Coventry, independent consultant and trainer, based in the UK. She has authored/co-authored over 10 SharePoint related books. When not writing, Penny works on large SharePoint deployments. Penny has spoken at a number of conferences including TechEd North America, International SharePoint Conference and SharePoint Best Practices Conferences, as well as user group meetings and SharePoint Saturdays.
October 17
SharePoint 2016 IT Preview Resources

I recently did a presentation, “What’s new in SP2016 for the End User”, at SharePoint Saturday Oslo, 2015 #SPSOslo, and plan to present the same session at SharePoint Days 2015 and Exchange conference in Slovenia. This blog post contains the list of resources for those presentations.

Announcements, Forums, User Voice:

ISOs:

Overviews:

Detailed Information:

September 10
Automating business processes with SharePoint, Office 365 and Azure

This blog post details the one-day presentation at SQL Server and SharePoint Pre-conference Training Days, prior to SQL Saturday Cambridge 2015.

Many people are forced to use Microsoft® SharePoint® because it is already there or part of the Office 365™ ecosystem; therefore many users see it just as another task to add to their burgeoning workload of tasks they have to complete in their busy working day. When any technology is introduced into a business it should help not hinder users. It should enable users to do more, not less. Technology should meet business needs and not be focused around a single product.

With this in mind, during the session I look at automating business processes using out-of-the-box functionality of SharePoint and with the use of Azure™ Logic Apps outside SharePoint, beyond firewalls and beyond even your own datacentre. The aim of the session was to cover tools and technology that allowed you to create workflows using no-code visual designers, SharePoint Designer 2013, Access 2013 and the Azure Portal.

SharePoint topics covered included:

Azure related topics covered included:

May 26
Business Connectivity Services: End User Implications – Part Three: External Content Type Authorisation Errors

Introduction

In the first part of this article we covered implications of external system throttling thresholds for users of Business Connectivity Services (BCS). That blog post displayed the error messages a user may see when the items returned from the external system exceed the throttling thresholds. In the second part of this article the default threshold settings were described and how to change them when using on-premises installations of either Microsoft SharePoint® Foundation 2013 or Microsoft SharePoint® Server 2013.

In this third part we will cover the implications, for users, of the permission settings on the External Content Type (ECT). The other parts of this series are:

  1. Why this blog series
  2. Threshold Limit Errors
  3. Changing External System Throttling
  4. External Content Type Authorization Errors – this blog post
  5. External System Authorization Errors (TBW)
  6. Summary (TBW)

Business Data Connectivity Authorisation Background

Each object stored in the Business Data Connectivity (BDC) service application metadata store has an access control list (ACL) that specifies which users and groups have permissions on the objects. Out of the thirteen BDC metadata objects, only permissions on the external system, external content type (ECT), and the BDC model can be set using the SharePoint 2013 Central Administration website.

Note: A professional developer can set the permissions using code for ECT operations (methods) and instances of those operations that specify how to use the operation by using a specific set of default values.

BDC metadata object permissions

The five BDC metadata objects that can have their own controllable ACLs:

  • External system*
  • BDC model*
  • ECTs*
  • Operations (also known as methods)
  • Operation instances

* You can configure the permissions for the BDC objects identified by the * using the SharePoint 2013 Central Administration website, Windows PowerShell® or programmatically.

These objects are referred to as individually securable metadata objects. All other objects obtain their permissions from their parent object. For example, associations, actions, and identifiers cannot be assigned permissions directly but take their permissions from their parent ECT. However, by default, permissions do not propagate from one individually securable metadata object to another, so an ECT operation does not inherit its permissions from its ECT, unless the propagate check box is selected.

Set Object Permissions

When permissions are propagated from an ECT, all operations and operation instances for that ECT receive the new permissions, replacing the permissions originally set on those individually securable metadata objects.

Note: Do not select the Propagate Permissions To All check box on the BDC metadata store as every external system, BDC model, or ECT will inherit this configuration when added to the metadata store. This also prevents users from unnecessary access to any external system, BDC model, or ECT that they should not have.

You can set the following four permissions on the BDC metadata store and BDC metadata objects:

  • Edit
  • Execute
  • Selectable In Clients
  • Set Permissions

Not all permissions are applicable to all objects. For example, enabling the Execute and Selectable In Clients with the BDC metadata store or the external system has no effect. However, setting these permissions at the BDC metadata store or external system level and selecting the propagation check box can be useful when you do not want to individually configure ECTs or operations for these two permissions. There isn’t an Edit permission on the ECT, however selecting the Edit permission on the ECT is useful as objects below the ECT, which do have an Edit permission inherits this permission settings, so users of a BCS tool, such as, SharePoint Designer, can create and modify objects that the ECT defines, such as, operations, actions, and associations.

What this blog post details

This blog post details the implications to the end user of the four permission on external content types. The scenarios detailed propagated the permission from the external content type to all the child individually securable metadata objects. At the end of this post, there are details on how to find ECT permission settings.

This blog post does not detail how to complete the security tasks of applying permissions to the external content type and assumes users are mapped to at least the Contribute permission level on the lists used in these scenarios, and have the permission to add Web Parts to pages.

Note: Information on the BCS related security tasks that can be completed by IT Professionals, SharePoint Online administrators, site collection administrators, site owners, external content type creators and developers can be found in the TechNet article; “Overview of Business Connectivity Services security tasks in SharePoint 2013”, which can be found at: http://technet.microsoft.com/en-us/library/jj683116.aspx.

The rest of this post describes errors an end-user may see in the browser, such as when using:

This post also details error messages a user may see in Office applications when accessing external data using BCS, for example, when using Quick Parts in a Microsoft Word document.

Edit permission on BDC Metadata Store

Before you can modify an external content type (ECT), it has to be created. To create an external content type (ECT), you must first have Edit permissions to the BDC metadata store. The BDC metadata store is the SQL Server database associated with the BDC service application that stores the BDC Models, external content types and external system information. This is true whether you are creating an ECT by uploading a .bdcm file using the SharePoint 2013 Central Administration web site, or deploying the BDC model or external content type, using a tool, such as SharePoint Designer or LightningToolsBCS Meta Man.

SharePoint farm administrators, SharePoint Windows PowerShell®, and application pool accounts have full permissions to a BDC service application and Edit permissions on the Metadata store. Farm administrators can then maintain or repair the BDC service application if necessary and deploy solutions packages that use BCS. However, these accounts do not have Execute permissions on any metadata store objects. Therefore, such accounts can upload or create a BDC model with its associated external system definition and ECT, and can even create an external list from those ECTs. However, these accounts would not be able to execute any of the operations on the external content, and therefore, when the external list is displayed in the browser, an authentication error would be displayed.

Also, when you configured a user as an administrator of a BDC service application, using the Administrators button on the Service Applications Ribbon, this does not give the user any permissions to manage the BDC metadata store.

BDC Metadata Store Permissions

As an administrator of the BDC service application you will be able to display the page that allows you to view External Systems, External Content Types and BDC models, however when you click the any of the commands on the Edit Ribbon, for example, Import to upload a BDC model, the error message: “Sorry, this site hasn’t been shared with you.” is displayed.

As a BDC service application administrator, in SharePoint Designer, no external content types will be displayed in the External Content Types gallery, and when you attempt to add a connection to an external system, such as an SQL Server database, then the message “Access denied by Business Data Connectivity” is displayed, as shown in the following screenshot.

SPD Access denied

Edit permission on External Content Type

To modify an existing ECT, you need to have Edit permissions on the ECT. Without Edit permissions the ECT will not be visible in the External Content Types gallery in SharePoint Designer.

No Execute permission on the External Content Type

Once you have created an ECT, next you need to test it - the easiest method is to create an external list based on the ECT. Just because you have the Edit permissions on the ECT, it does not follow that you have Execute Permissions on the ECT. With only Edit permission on the ECT you will be able to create an external list assuming you have the Manage Lists and the Add and Customize Pages rights and you are using SharePoint Designer, however when you click on the external list, you may see no data. To display data in the external list you need permission to execute the Read List operation (Finder method) which is associated with the view.

The following sections details the error messages you received with eternal lists, external columns, Business Data Web Parts and Quick Parts, when you do not have the Execute permission on the ECT.

External List

When you do not have the Execute permissions for the Read List operation, then no data is displayed in the external list and the following error message is displayed:

“Access denied by Business Data Connectivity.”

As shown in the following screenshot:

External List access denied

External Column

When a user displays in the browser a view of a list or a library that contains an external column, the data in the external, as well as data in any associated columns can be seen. This user may be mapped to any permission level that allows them to see the items in the list, such as View Only, Edit or Contribute. This user may not have the Execute permission on the ECT. This happens, as when the user who created or modified the items, selected the values in the external columns, then the data from the external system is copied to the list or library at that time, and is stored within a SharePoint content database. This is unlike external lists, where external data is not copied from the external system.

However, if a user is allowed to modify an item in the list or library, but does not have the Execute permission on the ECT, then when they try to edit the properties of a list item or file, the Edit properties page tries to resolve the previously selected value in the external column and as the user does not have the rights to execute the Read List operation (Finder method) the following message is displayed:

“No exact match was found. Click the item(s) that did not resolve for more options. You can also use Select button to choose External Data.”

As shown in the following screenshot:

External Column

If the user then displays the External Item Picker for the external column, the following error message is displayed:

“Access denied. You do not have permission to access this content.”

As shown in the following screenshot:

External Item Picker access denied

This user who has no Execute permission on the ECT associated with the external column will be able to:

  • Create new items, upload and create new documents providing that they do not attempt to add any content into the external column.
  • Delete items or documents in the list or library.

When such a user wants to modify the properties of an existing list item or document, then they must delete the contents in the external column to save their modifications. If the external column is a required column, then the user will be unable to make any modifications to items in the list or create new items.

Word Quick Parts

When a user has inserted Quick Parts into a document to display or modify values in an external column, then when the document first opens then any embedded external data is displayed for exactly the same reasons why a user can see data in the external column when viewing the library in the browser. However, if the user tries to amend the values in the Quick Part control, then when the External Item Picker is displayed, the following error message is displayed:

“An error occurred while obtaining business data by using the Picker web service. Contact your system administrator.”

As shown in the following screenshot:

Work Quick Parts

Business Data Web Parts

When a user displays a page where a Business Data Web Part is displayed, and does not have Execute permissions to the ECT that the Business Data Web Part uses, then no external data is displayed and the following error message is displayed:

“Access denied. You do not have permission to access this content.
Correlation ID:1d90a99c-28d3-0034-3fb4-8a8221d868dc”

As shown in the following screenshot.

Business Data Web Pats access denied

User does not have Selectable in Client Permissions on the External Content Type

The external content type (ECT) is not displayed in the External Content Type Picker when you try to create an External List or use Business Data Web Parts. However all ECTs are displayed when you create an external column, even if you have no BDC object permission to any ECTs.

Tip: To create an external list you must have both the SharePoint Manage Lists and the Add and Customize Pages rights, as well as the Selectable in Client object permission to the ECT. When you have the Manage Lists rights and not the Add and Customize Pages rights, then you are only allowed to add SharePoint internal list apps, such as, SharePoint libraries – Document, Form, Wiki Page, Picture and SharePoint lists – Links, Announcements, Contacts, and so on; however to display the option to create an external list under Apps you can add on the Your Apps page you must have the Add and Customize Pages rights. By default this right is included in the Full Control permission level and the Design permission level, and therefore, usually only Site Owners can create external lists.

Methods for finding the permissions for an external content type

You can use the SharePoint Central Administration web site, SharePoint Designer or Windows PowerShell to find the permissions settings for an ECT, as described below:

  • SharePoint Central Administration web site:
    1. Open the SharePoint Central Administration website in the browser.
    2. Under Application Management, click Manage service applications.
    3. On the Service Applications page, click the name of the BDC service for which you want to manage permissions.
    4. Select the check box to the left of the ECT and then click Set Object Permissions on the Edit Ribbon tab.

Find ECT permission using CA

  • SharePoint Designer:
    1. Using SharePoint Designer, open any site where you have permissions to use SharePoint Designer.
    2. In the Navigation pane, click External Content Types.
      The External Content Types gallery page is displayed in the workspace. The workspace might be empty if no ECTs have been created or you have do not have Edit permissions on any ECTs.
    3. Click the ECT to display the Summary View.
      You can view, but not modify the permissions for the ECT which are displayed in the Permissions area.

Find ECT permissions using SPD

  • Windows PowerShell:

To display the permissions for one ECT, type commands similar to the following:

$ect = Get-SPBusinessDataCatalogMetadataObject –BdcObjectType Entity `

   -Name Suppliers `

   -Namespace Northwind_MetaMan `

   -ServiceContext http://intranet

$ect.GetAccessControlList()

Where Suppliers is the name of your ECT, Northwind_MetaMan is the namespace, and http://intranet is the URL of a web application that is associated with your BDC service application.

To export the output to a file that you can then open in Microsoft Excel use the following command:

$ect.GetAccessControlList() | Export-Csv c:\Tools\ECT.csv

To display all active ECTs and their permissions, type commands similar to the following:

$metadatastore = Get-SPBusinessDataCatalogMetadataObject `

   –BdcObjectType Catalog –ServiceContext http://intranet

$ects = $metadatastore.GetEntities("*","*",$true)

$ects | foreach {

   $_.Namespace, $_.Name,

   $metadatastore.GetEntity($_.Namespace,$_.Name).GetAccessControlList()

}

The ECT output can be formatted so that it is easier to read by using a command similar to the following:

$ects | foreach {

   ("`r`n ECT: " + $_.Name + "`t" + $_.Namespace),

   $metadatastore.GetEntity($_.Namespace,$_.Name).GetAccessControlList()

}

 

Warning: Windows PowerShell scripts can affect the performance of SharePoint and therefore can impact your users. You should consider running scripts outside of business hours.

Summary

In this third part of this blog series, we detailed errors that are displayed when users do not have the necessary BDC object permissions on external content types. Briefly:

  • When you do not have Edit permissions on the BDC Metadata Store, then you cannot create an ECT.
  • When you do not have Edit permissions on the ECT, you cannot modify or delete the ECT.To create external lists, you must have the SharePoint Manage Lists and the Add and Customize Pages rights, as well as the Selectable in Client object permission to the ECT.
  • To create, read (view), update or delete external data using an external list you must have the Execute permission on the relevant operation.
  • To select an ECT in a Business Data Web Part you must have the Selectable in Client object permission to the ECT. For users to see the external data in the Web Part they must have the Execute permission.
  • To select external data stored in external columns, including selecting external data using Quick Part controls in Word, you must have Execute permissions on the Read List operation.

Reference

April 06
Finding External Lists using Windows PowerShell®

This blog post provides sample Windows PowerShell® scripts that displays information on external lists in your Microsoft SharePoint® on-premises environments and the external content types (ECTs) used by those external lists. Only those sites that you have access to, will appear in the output. This blog post details:

Tip: For information on the permissions needed to run SharePoint Windows PowerShell cmdlets in an on-premises installation, see the blog post: When is a SharePoint Farm Administrator not a SharePoint Farm Administrator?

Find all external lists in one site

To find all external lists in a site is as easy as typing either of the following commands, where http://intranet/sites/lt/bcsdemo is the URL of the site:

(Get-SPWeb http://intranet/sites/lt/bcsdemo).Lists |

  where {$_.HasExternalDataSource -eq $true} |

  Select Title

Or

Get-SPWeb http://intranet/sites/lt/bcsdemo |

Select -ExpandProperty Lists |

  where {$_.HasExternalDataSource -eq $true} |

  Select Title

To quickly display a count of the number of external lists in a site, type:

((Get-SPWeb http://intranet/sites/lt/bcsdemo).Lists |

  where {$_.HasExternalDataSource -eq $true}).Count

However if you want to find the external content type (ECT) associate with the external list, type commands similar to the following:

# Use your site in place of http://intranet/lt/bcsdemo

$web = Get-SPWeb http://intranet/sites/lt/bcsdemo

# Find all the lists in the site and save them in a variable $lists

$lists = $web.lists

# For each external list output the list name, ECT name and namespace

$lists | where {$_.HasExternalDataSource -eq $true } |

foreach {

    $_.Title,

    $_.DataSource.GetEntity().Name,

    $_.DataSource.GetEntity().Namespace

    $_.DataSource.GenEntity().Version

}

Note: An ECT is uniquely identified by its name, namespace and version number.

The output from the above script is not easy to read. Using the technique described in the TechNet article: Windows PowerShell Tip of the Week the output can be displayed in a table, using the following script:

# Create custom table formatting

$a = @{Expression={$_.Title};Label="List";width=20},

@{Expression={$_.DataSource.GetEntity().Name};Label="ECT";width=20},

@{Expression={$_.DataSource.GetEntity().Namespace};Label="Namespace";width=25},

@{Expression={$_.DataSource.GetEntity().Version};Label=”Version”;width=15}

# Display list name, ECT name and namespace as a table

# This line of code assume you have initiated the $lists variable as

# detailed in the first snippet of code

$Lists | where {$_.HasExternalDataSource -eq $true } | Format-Table $a

The output from the command will look similar to:

List       ECT       Namespace                           Version
----       ---       ---------                           -------
BigExtList BigECT    http://intranet.dp.loc...           1.5.0.0
matttest   Products  Northwind_MetaMan                   1.0.0.2
Northwind  Customers Customers http://intranet.dp.loc... 1.14.0.0
Northwind  Employees Northwind_Employees Northwind       1.0.0.0
Orders     Orders    http://intranet.dp.loc...           1.3.0.0
Products   Products  http://intranet.dp.loc...           1.0.0.0
Suppliers  Suppliers Northwind_MetaMan                   1.0.0.1

Tip: If you do not know the width for the table columns, then use the –AutoSize parameter on the Format-Table command.

The GetEntity method on the DataSource field is of type, Microsoft.SharePoint.BusinessData.MetadataModel.Static.Entity, and makes a call to SharePoint to obtain the ECT information from the Business Data Connectivity (BDC) Metadata store. When an ECT is deleted and external lists based on that ECT are not, then a call to the GetEntity method will result in an error message.

You can use the GetEntity method to obtain other useful information, such as, GetActions() and GetAccessControlList(). However, some of this information is saved as a property of the list object, therefore, if you have saved the reference to the list object in a variable, it will be more efficient to display the information using the variable.

Warning: As the code in this post uses a variable to store SPWeb you should dispose of the memory assigned to the variable. This is true for SPSite and SPSiteAdministration objects; therefore when using code that use these objects you should look to use the two SharePoint cmdlets Start-SPAssignment and Stop-SPAssignment.

Go to top of post.

Find all external lists in a site collection or a web application

To find all external lists, in a site collection type a command similar to the following:

Get-SPSite http://intranet/sites/lt -Limit All |

Select -ExpandProperty AllWebs | Select -ExpandProperty Lists |

  where {$_.hasexternaldatasource -eq $true} | select ParentWebUrl, Title

 

Which has output similar to:

ParentWebUrl               Title
------------               -----
/sites/LT Access           Assets Ext List
/sites/LT Access           Retail Sales Ext List
/sites/LT/bcs2013demo      BigExtList
/sites/LT/bcs2013demo      mattes
/sites/LT/bcs2013demo      Northwind Customers
/sites/LT/bcs2013demo      Northwind Employees
/sites/LT/bcs2013demo      Orders
/sites/LT/bcs2013demo      Products
/sites/LT/bcs2013demo      Suppliers
/sites/LT/bcshostsite      Customers
/sites/LT/bcshostsite      Orders
/sites/LT/dvwp2013/pacebus PB Customers

Similarly, to find all external lists in all web application type the following command:

Get-SPWebApplication | Select –ExpandProperty Sites |

Select -ExpandProperty AllWebs | Select -ExpandProperty Lists |

  where {$_.hasexternaldatasource -eq $true} | select ParentWebUrl, Title

 

For a summary report that totals the number of ECT’s per site, type:

Get-SPWebApplication | Select -ExpandProperty Sites |

select -ExpandProperty AllWebs | select -ExpandProperty Lists |

  where {$_.hasexternaldatasource -eq $true} | group parentweburl 

 

Which has output similar to:

Count Name                      Group
----- ----                      -----
2     /sites/LT                 {Access Assets Ext List, Access Retail...
7     /sites/LT/bcs2013demo     {BigExtList, matttest, Northwind Custome...
2     /sites/LT/bcshostsite     {Customers, Orders}
1     /sites/LT/dvwp2013/pac... {PB Customers}

Warning: Windows PowerShell scripts can affect the performance of SharePoint and therefore can impact your users. You should consider running scripts outside of business hours.

Go to top of post.

List External Lists by External Content Types

When you use many external content types (ECTs) to find the total number of external lists that uses external content types, then you can type a command similar to:

# Save in a variable the external lists you wish to report on

# in this example, the variable contains all external lists

# in a specific web application

$Lists = Get-SPWebApplication http://intranet.dp.local |

Select -ExpandProperty Sites | Select -ExpandProperty AllWebs |

select -ExpandProperty Lists | where {$_.hasexternaldatasource -eq $true}

# Save the expression to obtain the ECT name in a variable

$e=@{Expression={$_.DataSource.GetEntity().Name}}

# Output external lists grouped by ECT

$Lists | Sort $e | Format-Table Title, ParentWebUrl –Groupby $e

Which has output similar to:

$_.DataSource.GetEntity().Name: BigECT
Title               ParentWebUrl
-----               ------------
BigExtList          /sites/LT/bcs2013demo


$_.DataSource.GetEntity().Name: Customers
Title               ParentWebUrl
-----               ------------
Customers           /sites/LT/bcshostsite
Northwind Customers /sites/LT/bcs2013demo

Note: When no $_.DataSource.GetEntity().Name: <external content type name> is displayed prior to displaying the external list details, then the ECT associated with the external list has been deleted.

To output a table that counts the number of external lists created for each ECT then type the following command:

$lists | sort $e | group $e

Which has output similar to the following, where the Name column displays the ECT name, and the Group column contains the name of each external list created from the ECT.

Count Name                Group
----- ----                -----
1     BigECT              {BigExtList}
2     Customers           {Customers, Northwind Customers}
1     Northwind_Employees {Northwind Employees}
2     Products            {Products, matttest}
1     Suppliers           {Suppliers}

Tip: If you are a new SharePoint Farm Administrator and new to Windows PowerShell then you may want to read the Chapter written by Penelope Coventry from the Microsoft SharePoint 2010 Administrator's Companion, Microsoft Press, August 2010, which is still relevant for SharePoint 2013, and can be found at: http://technet.microsoft.com/en-us/library/gg550867.aspx.

Go to top of post.

March 01
Business Connectivity Services: End User Implications – Part two: Changing External System Throttling

Introduction

In the first part of this article we covered implications of external system throttling thresholds for users of Business Connectivity Services (BCS), whether using Office 365® or on-premises installations of Microsoft SharePoint®. 

This is the second part of the article and describes the default threshold settings and how to change them when using on-premises installations of either Microsoft SharePoint® Foundation 2013 or Microsoft SharePoint® Server 2013. If you are using Office 365® you will not be able to change these thresholds.

External System Throttling

Each Business Data Connectivity (BDC) service application has throttling thresholds defined by throttling rules which consist of a throttle type and scope.

The throttle types are:

  • Items. The number of items returned from the external systems, such as the number of authors.
  • Size. The amount of data retrieved by the BCS Runtime from the external system in bytes.
  • Connections. The number of open connections to the external system.
  • Timeout. The time until an open connection is terminated, in milliseconds.
  • Metadata Size. Used to restrict the size of the metadata returned by the external system, specified in bytes.
  • Model Size. Used to restrict the size of the BDC model that can be imported into the BDC metadata store, specified in bytes.
  • Max Number Of Models. Used to restrict the number of BDC models that can be imported into a BDC metadata store per BDC service application. For a partitioned BDC service application then this is per tenant.
  • None. No throttle type specified.

Throttle scopes refer to type of connection that is used to contact the external system. Scopes includes Database, Web Service, WCF, OData and Custom. There is also a Global scope that includes all connector types, except for custom connectors. When a BDC service application is first created, the throttle rules do not include all combinations of throttle types and scopes. A throttle rule for the Global scope is used, when a throttle rule is not enabled for a throttle type for a specific scope.

For each throttling rule, not only do you have to specify the throttle type and scope, there are three properties defined, which are:

  • Enforced. When this property is set to True, the throttling rule is enabled.
  • Default. This property affects external lists and custom Web Parts. The first part of this article, provided examples of the error messages presented to the user based on this property. Custom Web Parts can override this value and therefore can present more data than external lists.
  • Max. The limit used when custom Web Parts override the value in the Default property

The throttling rules that exist when a BDC service application is first created, whether in partition mode or non-partitioned mode, are displayed in the following table.

Scope

Throttle Type

Enforced

Default

Max

Global

Connections

True

200

500

Global

ModelSize

False

10,000,000

50,000,000

Global

MaxNumberOfModels

False

1000

2000

Database

Items

True

2000

1,000,000

Database

Timeout

True

180,000

600,000

WebService

Size

True

3,000,000

150,000,000

WCF

Size

True

3,000,000

150,000,000

WCF

Timeout

True

180,000

600,000

WCF

MetadataSize

False

3,000,000

150,000,000

OData

Size

True

3,000,000

150,000,000

OData

Timeout

True

180,000

600,000

OData

MetadataSize

False

3,000,000

150,000,000

Modifying External System Throttling Thresholds

You can retrieve and amend the throttling rules by using the Business Data Catalog Windows PowerShell® cmdlets, as shown in the following examples. First you need the BDC service application proxy. Assuming there is only one BDC service application in your farm, which is true for most on-premises installations, then you can use the following Windows PowerShell command:

$bdcproxy = Get-SPServiceApplicationProxy | where {$_.TypeName –like “Business*”};

 

To display the throttling configuration for a BDC service application, type a command similar to the following one, on one line:

Get-SPBusinessDataCatalogThrottleConfig –ServiceApplication $bdcproxy `

  –Scope Global –ThrottleType Connections;

 

The output from the command is as follows:

    Scope : Global
    ThrottleType : Connections
    Enforced : True
    Default : 200
    Max : 500

To disable a throttling rule, type the following command:

Get-SPBusinessDataCatalogThrottleConfig –ServiceApplication $bdcproxy `

  –Scope Global –ThrottleType Connections | Set-SPBusinessDataCatalogThrottleConfig `

  -Enforced:$False;

To modify a throttle rule, type the following command:

$dbrule = Get-SPBusinessDataCatalogThrottleConfig –ServiceApplication $bdcproxy `

    –Scope Database –ThrottleType Items;

$dbrule | Set-SPBusinessDataCatalogThrottleConfig –Maximum 2000000 –Default 5000;

Summary

This second part of the four and discussed the default external system throttling settings and how to modify them. By default the following throttling rules are defined.

  • Global Scope. Throttle Types: Connections, ModelSize, and MaxNumberOfModels.
  • Database Scope. Throttle Types: Items, and Timeout.
  • WebService Scope. Throttle Type: Size.
  • WCF Scope. Throttle Types: Size, Timeout, and MetadataSize.
  • OData Scope. Throttle Types: Size, Timeout, and MetadataSize.
Back to Part Zero: End User Implications​.​​​
February 02
Business Connectivity Services: End User Implications – Part One: Threshold limit Errors

Introduction

Once you have created an external content type, content owners, business analysts and site owners will create Microsoft SharePoint® solutions to expose the data from external systems using External Lists, external data columns and Business Data Web Parts. However, using data from external systems, is not the same as using data from internal lists, such as document libraries, tasks, announcements and contacts. In this series of blog posts I will look at the errors users may see when a page is displaying data from an external system.

 

Note: If you are new to using external data in your SharePoint sites, you might like to read: “Introduction to external data”, which can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/introduction-to-external-data-HA102891586.aspx, and “Differences between native and external lists”, which can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/differences-between-native-and-external-lists-HA102771000.aspx.

This article is divided into six parts:

  1. Why this blog series
  2. Threshold Limit Errors (this blog post)
  3. Changing External System Throttling​
  4. External Content Type Authorization Errors
  5. External System Authorization Errors
  6. Summary

In this first blog post, I will describe errors your may see as the result of external system throttling threshold limits.

First, as a reminder of what happens with internal lists, Office 365® and on-premises installation of SharePoint Foundation 2013 and SharePoint Server 2013 provide the following list throttling options.

  • List view threshold. A maximum of 5000 items are returned from a content database operation. When using a SharePoint on-premises installation this value can be amended using either the Central Administration web site or Windows PowerShell® at the Web Application level.
  • List view threshold for auditors and SharePoint server farm administrators. A maximum of 20,000 items can be returned from a content database operations.

There are many other thresholds, as well, for example, the list view lookup threshold, where a maximum of 8 (12 for post-June 2013 CU) join operations, such as those based on the column types, lookup, person or group and workflow status. These and many other limits can be found on the Software boundaries and limits for SharePoint 2013 page at: http://technet.microsoft.com/en-us/library/cc262787.aspx.

When using external data these threshold DO NOT apply.

External System Throttling

Internal lists throttling and external system throttling is enabled by default. The external system throttling limits can be changed for on-premises installations of SharePoint by using Windows PowerShell® commands. With Office 365® the limits cannot be changed.

For example, by default, the number of external data items returned from a database query is limited to 2,000, with a database connection timeout of 60 seconds. A Windows Communication Foundation (WCF) connection is not limited by the number of items, but by the size of the data returned, which is 3 MB and a connection timeout of 60 seconds. Custom Web Parts can exceed the default throttling limits, but more on this and how to change the default throttling thresholds in the second part of this series.

However, no matter the value of the throttling limits, you may come across scenarios where the amount of data that is returned from the external system exceeds the limits. The method of overcoming the limits is to define filters on your external content types.

The rest of this blog investigates the error messages users see, when no filters have been created on the external content types or when the retrieval of data from the external system is taking too long.

The SharePoint components that are related to external content types and therefore where users will see the error messages, are:

Before looking at errors related to external content type related, let’s look at the threshold error messages you may see on internal lists and libraries.

List View Thresholds

When you create a view on an internal list or library that exceeds the list view threshold, the following message is displayed,

“This view cannot be displayed because it exceeds the list view threshold (5000 items) enforced by the administrator”

As shown in the following screenshot.

Screenshot: View cannot be displayed message. 

Now let’s explore the error messages you may see, related to external system threshold limits.

External content type Read List operation thresholds

The Read List operation (Finder method) threshold is defined by scope and throttle type. This threshold affects External Lists and Business Data Web Parts. The External List Item Picker is also affected by a threshold limit but this is not the same limit that is used for External Lists and Business Data Web Parts.

External Lists

When an external list is created from an external content type based, for example, on a database (Scope) and the number of items (Throttle Type) to be return exceeds the threshold limit, the following message is displayed:

“Database Connector has throttled the response. The response from database contains more than '2000' rows. The maximum number of rows that can be read through Database Connector is '2000'. The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet.”

As shown in the following screenshot:

Screenshot: Threshold exceeded warning message. 

More information on External Lists can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/create-an-external-list-HA102771010.aspx.

Business Data List Web Part

The Business Data List Web Part uses the same Scope and Threshold Type as the External List. The following error message is displayed within the Business Data Web Part when the items return from a database external system, exceeded the threshold limit.

“An error occurred while retrieving data from Adventure Works. Administrators, see the server log for more information.

Correlation ID:6259959c-f8b8-0034-3fb4-825fe72a8d0b

Database Connector has throttled the response. The response from database contains more than '2000' rows. The maximum number of rows that can be read through Database Connector is '2000'. The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet.”

As shown in the following screenshot:

Screenshot displaying threshold limit warning message 

More information on Business Data Web Parts can be found at: http://office.microsoft.com/en-gb/sharepoint-server-help/use-the-business-data-web-parts-HA102770986.aspx.

External Item Picker Threshold

The External Item Picker is a dialog box that is displayed when you are using:

  • External columns
  • Business Data Item Web Part
  • Business Data Actions
  • Word when using Quick Parts

Although the External Item Picker uses the same default Read List operations (Finder method) which can be used with External Lists and Business Data Web Parts, the External Item Picker uses a different limit to control the number of items displayed in the item picker. This limit cannot be changed and is set at 201.

For example, once you have created an external column on a list or library, then users can use the external item picker to select an item (row) from the external system and store that value in the external column. When the number of items returned by the external content type exceed this limit then in the External Item picker, the following error message is displayed:

“Warning: Too many results have been returned. This result set may be incomplete or truncated.”

As shown in the following screenshot:

Screenshot: Choose dialog box displaying warning message 

Note: If the value from the external system is not displayed in the first 201 items returned, then the end user will not be able to select an external item to store as a value in the external column in their list item. The Search box in the External Item Picker, can only be used to return a subset of external data when a filter is defined.

You can also embedded external data in a Microsoft Word document using Quick Parts. You can then use the External Item Picker similar to how you used the External Item Picker in the browser to select the external data to include in your document. Similarly, a message is displayed if the results have been truncated,

“Warning: Too many results have been returned. This result set may be incomplete or truncated.”

As shown in the following screenshot:

Choose Screenshot: External Data dialog box displaying warning message 

Summary

When you use Business Connectivity Services whether in Office 365® or on-premises installations of Microsoft SharePoint®, then throttling thresholds are enabled by default to prevent denial of service (DoS) attacks. You are most likely to see the effect of these thresholds when you create external content types with no limit filters or the retrieval of data from the external system is taking too long. A SharePoint server administrator can alter the thresholds, however, it is best to always configure filters on external content types. The next part of this article will describe the default threshold settings and how to change them.

Appendix: Filters

When creating your external content types (BDC models), use the following guidelines:

  • Define filters for the Read Item (SpecificFinder) and Read List (Finder) operations (methods) to limit the items that are returned from the external system.
  • When defining one or more filters, select a filter to be the default filter, especially if you will be using Quick Parts in Microsoft Word to embedded external data. In Word the External Item Picker does not display a drop down list to select a filter. When a filter is selected as the default filter, then that is the filter that will be used, although this is not obvious to the user as the name of the filter is not displayed.
  • Define default values for the filters. When developers use external content types in their solutions, then no items will be returned unless a default value for a filter is defined. However, when you first create an external list using the browser or SharePoint Designer, then no items will be returned from the external system, even when a default value is specified. You will need to train users to alter the default view and enter values for the filters for items from the external system to be displayed.
  • Define either a Limit filter or a Page filter on operations (methods) that return multiple items.
  • On input parameters, define system filters. On return parameters, define user filters.

References

Back to Part Zero: End User Implications​.​​

January 22
Project Siena Resources

​I recently did a presentation at the inaugural Derbyshire Dot Net user group. Part of my presentation was on Project Siena, which is a Windows Store application that allows you to create a Windows 8.1 app very easily and quickly, connects to multiple data sources (including Microsoft SharePoint) and uses an Excel-expression-like syntax.

September 24
Business Connectivity Services: End User Implications – Part Zero: Why this blog series

Once an external content type is created then they can be used in Microsoft SharePoint® solutions to expose the data from external systems using External Lists, external data columns and Business Data Web Parts. However, unlike solutions that are based on data stored internally in lists and libraries, when exposing external data using Business Connectivity Services (BCS), there are at least three levels of authorization plus BCS external system throttling thresholds in use:

  • SharePoint permissions. Allows a user access to the site, external list, the list that contains the external column and pages where Business Data Web Parts are used.
  • Business Data Connectivity (BDC) objects. External content types could be used extensively in your SharePoint installation, and solutions built using them could be a critical to your business. A modification to an external content type could stop those solutions working as designed; therefore, external content types can be guarded against accidental or malicious modifications. For example, even if a user has SharePoint permissions to view an external list, if they do not have permissions on the external content type, no data in that external list will be displayed.
  • External System authorization. Most external systems have their own authorization settings determining who can see the data as well as who can view, create, manipulate, or delete the data.
  • BCS threshold limits. These limits are enabled by default to prevent denial of service (DoS) attacks. A user may have the correct SharePoint, BCS object and external system permissions, however, if the number of items returned from the external system exceeds the threshold limits, data will either not be displayed or only a subset of the data will be displayed.

Authentication and threshold limits that can affect the display of external data 

Using the browser or Microsoft Office applications, it is not always obvious as to which authentication settings or throttling threshold is preventing the user from seeing the data. This series of blog posts looks at the errors users may see when a page should be displaying data from an external system.

The other parts of this blog post are:

  1. Threshold Limit Errors (external system throttling)
  2. Changing External System Throttling
  3. External Content Type Authorization Errors
  4. External System Authorization Errors (to be written)
  5. Summary (to be written)

Penny's Blog Home page

​​
August 01
When is a SharePoint Farm Administrator not a SharePoint Farm Administrator?

Have you seen in documentation any of the following statements?

  • You’ll need to work closely with your SharePoint farm administrator.
  • To complete the following tasks you must be a SharePoint farm administrator.

The first statement may be more about a person’s role and responsibility, than the tasks they can physically complete on a Microsoft SharePoint® installation. The second statement could be found when a new SharePoint-related product needs to be installed in your on-premises installation of SharePoint.

This blog post concerns the set of activities needed to make a user a “SharePoint farm administrator”, so that they can complete all SharePoint farm administrative tasks, whether those tasks can be completed using the SharePoint Central Administration web site or Windows PowerShell®.

So how does a user become a SharePoint Farm Administrator? Surely all that is needed, is an existing SharePoint Farm Administrator completes the following steps:

  1. Open the SharePoint Central Administration web site in the browser.
  2. On the Quick Launch, under Central Administration, click Security.

    Click Manage the farm administrators group
  3. On the Security page, under Users, click Manage the farm administrators group.
  4. On the Farm Administrators page, click New.

    Click New
  5. On the Share ‘Central Administrator’ dialog, under Add people to the Farm Administrators group, type a userid and then click Share.

    Type a userid and then click Share.

Note: The above steps are for Microsoft SharePoint 2013, however similar steps can be used in a Microsoft SharePoint 2010 farm.

Unfortunately, as you may have guessed, completing the above steps is not sufficient. These steps adds the user to the Farm Administrators SharePoint group and to the WSS_ADMIN_WPG local security group on each server in the SharePoint farm. This only allows the user to complete tasks using the SharePoint Central Administration web site and access SharePoint resources, such as, Logs and Web services. The user will also need to be a member of the local Administrator’s security group as well, otherwise they will be promoted to provide an administrator’s userid and password when they try to start the SharePoint Central Administration web site. However, the user is still not a fully-fledged SharePoint farm administrator.

If the user tries to run the SharePoint Management Shell, then they will receive the error message:

“The local farm is not accessible. Cmdlets with FeatureDependencyId are not registered.”

For a user to execute all SharePoint-related Windows PowerShell cmdlets to complete tasks on any Web Application in the SharePoint farm, they need to be a member of the Microsoft SQL Server® SharePoint_Shell_Access database role on the SharePoint configuration database and all content databases that contains resources you want the user to manage. This can be accomplished by using the Add-SPShellAdmin Windows PowerShell® cmdlets. SharePoint.

Tip: You can identify users who have SharePoint Window PowerShell capability by using the Get-SPShellAdmin cmdlet.

The Add-SPShellAdmin cmdlet can only be executed by an existing SharePoint farm administrator, or a user that is associated with the SQL Server securityadmin server role and db_owner database role on all affected databases, and local administrative permission on the local computer.

Note: The user that is the first SharePoint farm administrator is the Active Directory security account that was used to run the SharePoint Products Configuration Wizard to install the first server in a Microsoft SharePoint farm.

Therefore to make a user SharePoint Farm Administrator who can complete all SharePoint server related tasks no matter whether they need to use the SharePoint Central Administration web site or Windows PowerShell, they need to be a member of the:

  • Local Administrator security group for each server in the farm
  • Farm Administration SharePoint Group
  • SQL Server SharePoint_Shell_Access database role for the SharePoint configuration database and all content databases.

Note: If additional servers are added to the farm or additional content databases are created, then the existing SharePoint Farm administrators will need to be added to the appropriate groups/roles at that time.

The best way to ensure that no settings are missed, is to complete all tasks in a Windows PowerShell script. Here is an example of what such a script may look like:

<# ----------------------------------------

SharePoint 2013 Sample script to create a new SharePoint Farm Administrator

Provided by LightningTools as is

where $userid is of the format domain\username

and the function is called:

   New-LTSPFarmAdmin –userid "DP\chuck"

Please test in your environment before use

------------------------------------------#>

Function New-LTSPFarmAdmin ([string]$userid)

{

   # Get Central Administration Web Application

   $caWebApp = Get-SPWebApplication –IncludeCentralAdministration | `

      where {$_.DisplayName –like "SharePoint Central Administration*"};

   # Add user to the Farm Administrators SharePoint group

   New-SPUser –UserAlias $userid –Web $caWebApp.URL –Group "Farm Administrators";

   # Get content database for Central Administration web site

   $caContentDB = Get-SPContentDatabase –WebApplication $caWebApp;

   # Allow the user to use SharePoint Windows PowerShell cmdlets

   Add-SPShellAdmin -Database $caContentDB -Username $userid;

 

   # Complete for each content database in farm

   $contentDBs = Get-SPContentDatabase;

   foreach ($contentDB in $contentDBs) {

      Add-SPShellAdmin -Database $contentDB -Username $userid;

   }

} # End of Function New-LTSPFarmAdmin

# ####################### End of Script ############################################ #

 

Tip: If you are a new SharePoint Farm Administrator and new to Windows PowerShell then you may want to read the Chapter written by Penelope Coventry from the Microsoft SharePoint 2010 Administrator's Companion, Microsoft Press, August 2010, which is still relevant for SharePoint 2013, and can be found at: http://technet.microsoft.com/en-us/library/gg550867.aspx.

This blog post was originally published on the LightningTools blog.

June 11
SharePoint Evolution Conference Road show

​I am proud to announce that I will be speaking at all 12 cites on the SharePoint Evolution Conference Road show that is being held from Monday 9th June 2014 to Wednesday 25th June 2014. This blog post will provide links to material that I mentioned during my sessions. The three sessions I'll be presenting on during the road show are:

​Building an App Forms Business Solution

In this session, after a brief introduction on how to use Microsoft Access® 2013 to build Microsoft® SharePoint® apps with a full SQL Server® database, Penny will demonstrate how such technology was used in an actual business scenario. Most of the session will be ‘no-code’, and Penny will discuss hints and tips that she found useful. This session is applicable to information users, using either Office 365™ or an on-premises installation of Microsoft SharePoint® 2013. This session also briefly summarise other existing and planned SharePoint-related Microsoft Forms solutions, such as Excel Surveys (aka “FoSS”), and Forms on SharePoint Lists (aka “FoSL”).

Learn More

Books:

SharePoint Conference 2014 Recorded Sessions:

Articles and Blog Posts:

Call for Action

New & Future

Enhancing an App Forms Business Solution

In this session, Penny I'll enhance an Access app business scenario, by directly connecting to the app’s SQL Server® database. The session starts with a brief introduction of how the Microsoft® SharePoint® app was built using Microsoft Access® 2013. She I'll then connect to the app database using Business Connectivity Services (BCS), Microsoft Visio® 2013 and several Business Intelligence technologies. Although this is a ‘no-code’ solution, it is a technical session and will cover the Secure Store, and creating external content types. Penny will highlight any limitations between using Office 365™ or an on-premises installation of Microsoft SharePoint® 2013.

Learn More

SharePoint Conference 2014 Recorded Sessions:

Articles and Blog Posts

Book

Getting help

User Interface Tips and Tricks

Often power users are asked to make their SharePoint site look pretty. This session will look at what a power user should and should not do to enhance the User Interface of sites. It will include explanations of page types, master pages and Page Layouts as well as what is responsive web design. Then using no-code, Penny will amend the look and feel of page, including using composed looks, and adding buttons to the Ribbon and the List Item Menu.

Although this is not a developer session, Penny will talk about how you if you have from time to time added code to a page using the browser, how you should best do it. The session covers both SharePoint in Office 365, and on-premises installations of SharePoint 2013.

Learn More

Books:

SharePoint Conference 2014 Recorded Sessions:

SP24 Sessions:

Other recorded sessions:

Blogs:

Pod shows:

Other people to follow:

Files

1 - 10Next
MVP SharePoint Server
SharePoint Days and Exchange 2015