Configuring BCS with Secure Store on SharePoint 2013

On this post I will focus on how to test the BCS with Secure Store and the main configurations to get this working well;

Example 1 – AD user asccount:

Create an AD user account. In my example I created one called “RBTBDC”:

image

Add the account as a new login in the SQL Server. On the User Mappings of this user account, set the AdventureWorks2012 database and all the other databases to grant to the user the appropriated access with db_owner:

image

Register the account within the SharePoint Server 2013 as a managed account:

image

Create a AD Security Group and add all users that you want to access the External data. In my example I created one called “SPBCSUsers”:

image

I have populated this AD security group with all the user accounts that I want to have access on the external database:

image

In Central Administration, go to the Manager Service Application and the click on the Secure Store Service application:

image

On the Edit ribbon, click the New button. The Specify Settings page of the Create New Secure Store Target Application wizard is presented.

On this page, configure the fields as desired. For this posting, the following configuration was implemented:

  1. Target Application ID: BCS1.
  2. Display Name: BCS1.
  3. Contact Email: rbtspsetup@mabotega.local
  4. Target Application Type: Group.
  5. Target Application Page URL: None.

image

Click Next. The Specify the Credentials page of the wizard is presented.

Enter the field names as desired. For this posting, the following configuration was implemented:

  1. BCS1 Windows User Name (RBTSPBDC)Windows User NameNot Masked.
  2. BCS1 Windows Password (RBTSPBDC)Windows Password   – Masked.

image

Click Next. The Specify the membership settings page is presented.

Enter the accounts and groups as desired. For this posting, the following configuration was implemented:

    1. Target Application Administrators: MABOTEGA\RBTSPSRVAPPOOL; MABOTEGA\RBTSPSETUP;MABOTEGA\RBTSPADM.
    2. Members: MABOTEGA\SPBCSUsers.

MABOTEGA\RBTSPSRVAPPOOL – The user account that runs the application pool of the

MABOTEGA\RBTSPSETUP – The SharePoint Setup Account – I use this for administration;

MABOTEGA\RBTSPADM – The SharePoint Farm ADM account;

MABOTEGA\SPBCSUsers – AD Security Group for grant access to the users on the external databases;

image

Click OK. The Create New Secure Store Target Application wizard page closes, and the browser is navigated back to the Secure Store Service page, now listing the newly created target application:

image

Select this target application, and, on the Edit ribbon, click the Set Credentials button.

image

The Set Credentials for Secure Store Target Application (Group) dialog appears:.

Enter the information as desired. For this posting, the following configuration was implemented:

  1. Credential Owners: MABOTEGA\SPBCSUsers
  2. BCS1 Windows User Name: MABOTEGA\RBTSPBDC
  3. BCS1 Windows Password: xxxxxxxx
  4. Confirm BCS1 Windows Password: xxxxxxx

image

Click OK. The dialog closes, and the focus returns to the Secure Store Service page.

Create new external content type

  1. Open SharePoint Designer 2013.
  2. Connect to the SharePoint Site. In my example http://portal.mabotega.local
  3. In the Navigation pane, select External Content Types

image

Click on the External Content Types ribbon:

image

On the Extenal Content Type Information, click on the New External Content Type link and give it a name. In my example I type this:

Name: BCS1Sample

Display Name: BCS1Sample

image

On the Extenal Content Type Information, click on the Click here to discover external data sources and define operations link:

image

The Operation Designer page dialog appears:

Click the Add Connection button. A small popup appears from which you can select the connection type.

Select SQL Server,:

image

image

Click OK. The popup closes and the SQL Server Connection dialog appears:

Enter the configuration information. For this posting, the configuration is the following:

  1. Database Server: LABSP2013
  2. Database Name: AdventureWorks2012
  3. Name (optional): BCS1Sample
  4. Select Connect with Impersonated Windows Identity
  5. Secure Store Application ID: BCS1

image

Click OK. The popup BCS Secure Store: BCS1 dialog appears. Type the user name and password for the AD account MABOTEGA\RBTSPBDC:

image

Click OK. The popup BCS Secure Store: BCS1 dialog closes and a progress bar appears momentarily during the validation process. Wait it come back to the Operation Designer page filling with the new connection:

image

Expand the BCS1Sample tree. The various object groups associated with this database are revealed. Expand the Tables object group and then select the Customers table:

image

Right-click on the Customers table item in the tree. A popup menu is displayed.

Select Create All Operatons.

image

The All Operations page wizard dialog is displayed. Click Next:

image

Don´t change anything and click Next:

image

Click Next. The Filter Parameters Configuration page is displayed. Click Add Filter Parameer buttom:

image

Leave the CustomerID data source element configuration settings as default and click (Click to Add) link:

image

The Filter Configuration page is displyed. Select Limit on the Filetr Type and click OK.

image

Fill the Default Value field wit 2000 and click Finish:

image

The dialog closes and the focus changes back to the Operation Designer page:

image

Click the Save button at the top right of the SharePoint Designer UI. This saves the BCS configuration back to the farm.

Again on the External Content Types ribbon, click the Create Lists & Form button. The Create List and Form dialog appears.

For this posting, the settings are configured as follows:

  1. List Name: MyBCS1ListSampleCustmores
  2. Read Item Operations: Read Item
  3. System Instance: BCS1Sample
  4. List Description: AdventureWorks2012 Customer

image

Click OK. The dialog closes, and the focus returns to the Operation Designer page.

On the SharePoint Designer 2013 UI, in the Navigation pane at right, select Lists and Libraries.

Next, in the Lists and Libraries panel, now appearing at right, note that there is a new group added, External Lists, and an item in this group, MyBCS1ListSampleCustmores, and a new View is also listed:

image

Close SharePoint Designer.

Set Object Permissions

Launch Central Administration.

Navigate to the Manage Service Applications page, and then click on the BCS SPS Business Data Connectivity Service application. The Business Data Connectivity Service page is displayed:

image

Select BCS1Sample from the list, and then, on the Edit ribbon, click the Set Object Permissions button.

image

The Set Object Permissions dialog appears. In this dialog, add the domain MABOTEGA\SPBCSUsers security group to the users and groups to be granted permissions.

Then configure each on with all of the permissions listed below:

image

Click Ok. The dialog closes, and the focus returns to the Business Data Connectivity Service page.

Test your SharePoint Site

Open a new browser, and then connect as some domain user added previously in the AD security group MABOTEGA\SPBCSUsers to the site http://portal.mabotega.local.

In this sample I have used MABOTEGA\mabotega

Click the MyBCS1ListSampleCustmores link.

image

SharePoint will work on it to show up the data:

image

The MyBCS1ListSampleCustmores list is shown:

image

This completes our first sample.

 

Example 2 – Using SQL user asccount:

Create a SQL user. In my example I created one called “SharePointBDC”:

image

On the User Mappings of this user account, set the AdventureWorks2008R2 database and all the other databases to grant to the user the appropriated access with db_owner;

image

Create a AD Security Group and add all users that you want to access the External data. In my example I created one called “SPBCSUsers”:

image

On the Manage Service Applications, select the Secure Store and creae a New Secure Store Target Application ID. In my example I created one called “SharePointBCS” that will use a SQL Server login created previously (called “SharePointBDC”. Please follow the screen shots:

image

image

image

image

image

To test the BCS configuration, open the SharePoint Designer 2013 and follow the screen shots below to create an external content type for the SharePoint site.

Please, change the names for your preference.

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

image

If you are trying to access the external list with one user that doesn’t belong to the AD security group called “SPBCSUsers”, please add the user to this group:

image

image

image

This completes the second sample

Article from: https://guidesharepoint.wordpress.com/2014/12/26/configuring-bcs-with-secure-store-on-sharepoint-2013/