Implementing Business Data Connectivity in SharePoint 2013

Business Data Connectivity

Business Connectivity Services 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 accessed 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.

External Content Types in BCS

External Content Types are the core of BCS. They enable you to manage and reuse the metadata and behaviors of a business entity, such as Customer or Order, from a central location. They enable users to interact with that external data and process it in a more meaningful way.
For more information about using External Content Types in BCS, see External Content Types in SharePoint 2013.

How to connect with SQL external data source

Open the SharePoint Designer 2013 and click on the open site icon.

SharePoint Designer

Input the site URL to be opened.

Input the site url

Enter your site credentials here:

site credentials

Now we need to create the new External Content Type and here we have the options for changing the name of the content type and create the connection for the external data source.

connection for external data source

And click on the hyperlink text "Click here to discover the external data source operations". The following window will then open.

external data source operations

Click on the "Add Connection" button, we can create a new connection. Here we have the various options to select .NET Type, SQL Server, WCF Service.

Add Connection

Here we selected SQL Server, now we need to provide the Server credentials.

Server credentials

Now we can see all the tables and views from the database.

tables and views from the database

In this screen, we have the options to create various types of operations against the database.

create different types of operations

Click on the "Next" button.

next button

Parameters Configurations

Parameters Configurations

Options for Filter parameters Configuration

Options for Filter parameters Configuration

Here we need to add a new External List, click on the "External List".

External List

Select the site here and click the "Ok" button.

Select the Site

Enter the list name here and click the "Ok" button.

list name

Then refresh the SharePoint site, we can see the external list here and click on the list.

SharePoint site external list

Here we have the error message "Access denied by Business Connectivity".

Solution for this error

SharePoint Central Administration, click on "Manage service application".

Manage service application

Click on "Business Data Connectivity Service".

Business Data Connectivity Service

Set the permissions for this list.

permission for this list

Click "Ok" after setting the permissions.

after set the permissions

Then refresh the site and hope it works; but again there are problems. An error message, like Login failed for user "NT AUTHORITY\ANONYMOUS LOGON".

Solution for this error

We need to edit the connection properties, the Authentication mode; select the value "BDC Identity".

Authentication mode  select the value BDC Identity

Then use the following procedure:

Open PowerShell and type the following lines:
$bdc = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"}
$bdc.RevertToSelfAllowed = $true
$bdc.Update();

Now its working fine.

PowerShell working fine

One more error

There is a chance for one more error as in the following:

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

If that error exists then it is due to the number of recodes existing in the table.

Solution for this error

Use the following procedure:

Open PowerShell and type the following lines and execute

$bcs = Get-SPServiceApplicationProxy | where{$_.GetType().FullName -eq ('Microsoft.SharePoint.BusinessData.SharedService.' + 'BdcServiceApplicationProxy')}
$BCSThrottle = Get-SPBusinessDataCatalogThrottleConfig -Scope database -ThrottleType items -ServiceApplicationProxy $bcs
Set-SPBusinessDataCatalogThrottleConfig -Identity $BCSThrottle -Maximum 1000000 -Default 20000

Now it will work fine.

Summary

In this article I tried to explain how to implement the Business Data Connectivity in SharePoint 2013.

Up Next
    Ebook Download
    View all
    Learn
    View all