Goal

The goal of this article is to explain how you can expose external data through the Business Data Connectivity Service into search in SharePoint 2013. This blog post will take you onto a tour from creating data, exposing it through BCS as an external content type, viewing it as an external list and finally getting that data back into the search results.

We need external data

So the first step is to have some external data. We will use the Northwind database which can be found here;

http://northwinddatabase.codeplex.com/

Download the file and unzip the SQL Backup file into a local folder on your computer. Go into Microsoft SQL Server Management Studio and create a new database.

Right mouse click on the root item “Databases” and choose from the menu “Restore Database”. Select the earlier downloaded database and restore it.

When SQL has finished restoring the database it will appear as follow:

The next step is to expose it through Business Connectivity Services (BCS) into SharePoint 2013. At first we will need a SharePoint site which makes use of a Business Connectivity Service Application. So there are some steps to be taken to achieve this.

A new SharePoint Web Application

For our example we will use a new SharePoint Web Application. But you can use an existing one if you like.

1. Go to Central Administration and choose Application Management à Manage web applications
2. Create a new web application. (E.g. a BCS Test site on http://sp-dev-2012:101/)
3. Generate a new site based on for example the team site template.

Create a Business Data Connectivity Service Application

If you did not yet have a Business Connectivity Service Application execute the following steps;

• Go to Central Administration and choose Application Management à System settings à Manage Services on server
• Check if the Business Data Connectivity Service is started. If not click on “Start” to start the service
• Go to Central Administration and choose Application Management à Manage service applications
• Choose “New” from the ribbon and select Business Data Connectivity Service.
• Create the service application.

We have to configure the service and make sure that a Host SharePoint site URL is given for enabling Profile Page Creation. This is for hosting the External Content Types. It is recommended to use a dedicated SharePoint site to host them. In our case we generated a subsite under our web application (E.g. http://localhost:101/BCSProfilePages). This configuration is done though the management page of the service. Click on the just created service application and choose the option “Configure” in the ribbon. This will open a popup dialog which allows you to enter the URL.

Connect the service to the SharePoint Web Application

We have to make sure that the Business Connectivity Service Application is connected to the web application. This is checked by clicking next to the name of the just created web application and choose the option “Service connections” from the ribbon. Make sure that there is a checkbox marked in front of the Business Connectivity Service Application.

Connect the data source and create an External Content Type

Connecting the data source (E.g. one of the tables of the NORTHWND database) we use SharePoint Designer 2013. I’m not that fond with the designer but for creating External Content Types, Data Connections and Workflow it is a great product. So open the site http://localhost:101/ and connect to it. The first thing we are going to do is create an External Content Type. An External Content Type makes it possible to expose external data as a type within SharePoint just like other Content Types. There is a difference, because the data is and will not be in SharePoint and the available functionality around the Content Type and External List are limited in comparison with a normal Content Type and List. The cool thing is that you can enrich your External Content Type in SharePoint with additional metadata. Execute the following:

• Choose from the navigation panel the option “External Content Types”
• Now select the option “External Content Type” in the ribbon to create a new one.
• Click on the link “Click here to discover external data sources and define operations”. This will change the screen and allows you to add a new data connection, select the data and to generate operations needed to retrieve, add, delete and edit the data.
• Click on button “Add Connection”. A small popup dialog requests to specify what type of data source. Choose “SQL Server” and click at the “OK” button. A new small popup dialog appears which allows you to specify the data connection information. Enter the Database Server, Database name and choose the connect option “Connect with User’s Identity”. It is also possible to use an account from the Secure Store Service if you want. Click at the “OK” button to continue.

In the Data Source Explorer you will see the “NORTHWND” database added. Click on the “Tables” leaf and select right-mouse click on the “Customers” table. You will see a menu allowing you to create operations. Operations are used to retrieve and manipulate the data. For viewing data in search you only need “Read Item” and “Read List” operations. But to make it more interesting we will create all operations at once. This will allow a user to change the data from inside SharePoint.

The “All operations” popup dialog appears. Click once on the “Next >” button to move to the next screen. This screen shows you how the data source elements are mapped into the External Content Type. Select the element “ContactName” and check the box for “Show in Picker”. You now will have only one warning with regards to the “CustomerID” element. Just leave that as it is. Click on the “Finish” button to generate the operations.

Now click on back in the breadcrumb to get back to the External Content Type screen. Change the name and display name of the External Content Type to “NorthWindCustomer”.

The last thing we need to do is to set the correct field to the title field of the External Content Type. Select the “CustomerID” under “Fields”. Choose the option “Set as Title” from the ribbon. The field name of the “CustomerID” field will change to “CustomerID(Title)”. Make sure you use a non-numeric field as title otherwise you will get issues when trying to index the content in the Search Service.

Click on the save icon in the left top corner of SharePoint Designer to save all your changes to SharePoint. The changes are now saved to the metadata store of the Business Data Connectivity Service.

Create a profile page

Before we start the actual crawl by the Search Service Application, we need to have a Profile Page available for the External Content Type. This profile page is used for generating a correct URL in the search results. This means that the search results will return a URL to that Profile Page with a parameter CustomerID=[some value] to view the details of the data. Execute the following steps.

• Go to Central Administration and choose Application Management à Manage service applications
• Click on the Business Data Connectivity Service Application to manage it.
• Select the view “External Content Types” and select the “NorthwindCustomer” Content Type.
• Click on the option “Create/Upgrade” in the ribbon. This will open a pop dialog which allows you to create a default profile page for the External Content Type. The profile page is created inside the dedicated profile page site which we created earlier.

If everything goes according plan we should have a search results URL per item like:

Create an External List

To actually view the external data you will need an External List. This list is based on the selected External Content Type and will show you the data from the external data source. This is accomplished in a very simple way.

• Open the web application (E.g. http://localhost:101/)
• Click on the site actions icon and choose the option “Site contents”
• Select the “Add an app” option.
• Choose the app type “External List”. This will show a popup dialog allowing you to choose from the available External Content Types. The External Content Types are exposed through the Business Data Connectivity Service.

• Click on the “Create” button to create the new External List.

Viewing the external data in SharePoint

If you click on the “Customers” list in the site contents of the SharePoint site you will receive an error.

The problem lays in the fact that the BCS connects to the database using the “NT AUTHORITYIUSR”. So execute the following steps.

• Open SQL Server Management Studio
• Select the database “NORTHWND” and go into Security à Users
• Right-mouse click to add a new user
• Enter under the page “General” the user name and login name “NT AUTHORITYIUSR”.

• Check in the page “Membership” the checkboxes db_datareader and db_datawriter

• Select the “OK” button to save your changes.
• Now refresh the page and you will see a list of customers from the external data source.
• This part is only done to make sure that all settings are correct and that the data can be reached. The actual crawling of the data takes place directly in the database.

Configure a content source to crawl external data

To crawl the data we need to specify a content source for indexing the external data via the Search Service Application. This allows us to crawl the customers from the NORTHWND database. Execute the following steps.

• Go to Central Administration and choose Application Management à Manage service applications
• Click on the Search Service Application
• Click on the left side navigation the option “Content Sources”
• Choose “New Content Source” to create a new content source
• Enter the name “Northwind”
• Choose as Content Source Type “Line of Business Data”. You are now able to select the Business Data Connectivity Service Application and specify which external data sources need to be crawled. Select the second radio button and check the checkbox of the NORTHWND database.

• Click on the “OK” button to add the new content source.

Give the appropriate rights for the search service to crawl

You will need to give the default content access account appropriate rights on the NORTHWND database and the Business Data Connectivity Service Application to allow it to crawl the external data. A lot of different errors can happen when crawling external data. The most common error you will receive is “Error while crawling LOB Contents”. Most of these errors have something to do with security. So execute the following steps.

If you would crawl immediately you would receive the following error:

This error is caused by the fact that the default content access account does not have any rights to access the metadata store in the Business Data Connectivity Service Application. Take the following steps to prevent this error.

• Go to Central Administration and choose Application Management à Manage service applications
• Select the Business Data Connectivity Service Application
• Choose the option “Set Metadata Store permissions” in the ribbon. This will allow us to specify an account and give it rights to all BDC models, External Systems and External Content Types at once.

That last option will make sure that the account has rights to all metadata store objects defined in the Business Data Connectivity Service Application.

If you would crawl now you will notice that you still get an error. The error would be something like;

As you see it has something to do with the fact that the default content access account has no rights on the NORTHWND database. We need to give the account (E.g. CONTOSOsp-search) db_owner rights at the NORTHWND database. This is done exactly when we added the IUSR account. So follow those steps again but then for the default content access account.

Crawl the external data

Select the menu on the right of the content source Northwind to choose the option “Start Full Crawl”. Check the Crawl logs by selecting the menu on the right of the content source Northwind again and choose the option “View Crawl Log”. If everything went according plan you will see that there are records crawled and no errors has occurred.

Show external data through search on a page

The next and final step is to show the external data through a Search Results Web Part on a page. We start with executing the following steps.

• Open the web application (E.g. http://localhost:101/)
• Click on the “Edit” icon to edit the landings page of the SharePoint site
• Click inside one of the areas to add the Web Part. This will show an extra tab in the ribbon called “Insert”. Click on the tab and choose the option “Web Part”. Select the “Search Results” Web Part in the category “Search” and click the “Add” button to add the Web Part to the area.
• Click in the right part of the header of the Web Part to get the menu and choose the option “Edit Web Part”.
• Click on the “Change query” button
• Replace the “Query text” field with the following value

Test the query by clicking on the “Test query” button. You will see some results at the right side. But you notice that we use –Path:bdc3 in our filter. This prevents two additional results created due to that the table is crawled. In our result set we do not want to have those results present. We only want to see the customers returned. So finally close the popup dialog, the editor part and save the page.

Wrap up

So finally the external data is shown in the search results. As you can see we will need at least two service applications (BCS and Search) to achieve our goal. An important part is security and is the main reason for a lot of strange error messages. So check that first if you encounter any errors. I always generate an External List based on the External Content Type to check if I can reach the data from within the permissions of the current user. The next step is of course to enhance the display template and the profile page for nicer results.