Configuring SharePoint 2013 ECT & BCS for SQL Connectivity

  • Article

After having to walk through these setup steps recently for the first time with SQL and the impersonated windows ID I thought I'd post these steps for later reference and hopefully some of you will find them helpful.

There are several authentication options when configuring an External Content Type to point to a SQL server list.

Connect with User's Identity

Connect with Impersonated Windows Identity

Connect with Impersonated Custom Identity

 

The steps for each method above are outlined here: 

Connect with User's Identity

Use this for authenticating with the credentials from SQL itself when a user visits SharePoint to view the SQL list content

 

I.SharePoint Designer

  1. Create new External Content Type
  2. Create new database connection. Use Connect with User's Identity
  3. Create Read Item and Read List operation at the very least
  4. Save External Content Type
  5. Right-click newly created ECT and create External List

Connect with Impersonated Windows Identity

Use this option for SQL authentication when you want your user accounts hidden behind a single Windows alias. The alias account credentials are kept in the Secure Store Service Application.

I. SharePoint Central Admin

  1. Central Admin > Manage Service Application. Create a new Secure Store Service Application and edit it.
  2. Create a new target application using the Group type.
  3. Enter all the accounts that require access on the next screen.
  4. Once the Target Application has been created open the associated drop down list and select Set Credentials and enter the Username/Password of the AD alias account.

II. SharePoint Designer

  1. Create new External Content Type
  2. Create new connection and use the Connect with Impersonated Windows Identity option and enter the Secure Store Application ID you just created in Central Admin above
  3. Create a Read List and Read Item operation at the very least
  4. Save the External Content Type
  5. Open up the options menu of the newly created ECT to create an External List

III. SharePoint Central Admin

  1. Once you are finished with the previous step in SharePoint designer it automatically creates your External Content Type
  2. From the Business Data Connectivity Service Application find the new ECT, select the box next to it, and go to Set Object Permissions located on the command ribbon.
  3. Select a user or group with access to the ECT and grant at least Execute permissions to it.
  4. Select Set Metadata Store Permissions located on the command ribbon

Connect with Impersonated Custom Identity

This option is very much like the Impersonate Windows Identity selection but the configuration uses an account created in SQL as opposed to an AD account as was used In the Windows Identity section above.

 

I. SharePoint Central Admin

  1. Central Admin > Manage Service Application and select or create a new Secure Store Service Application and edit it. Create a new target application using the Group type.
  2. After the Target Application has been created select Set Credentials from the dropdown and enter the Username/Password of a SQL account

II. SharePoint Designer

  1. Create a new External Content Type
  2. Create a NEW connection, or use one that you know was setup w/ a SQL login. Use the Connect with Impersonated Custom Identity option and enter the Secure Store Application ID created in the previous steps.
  3. Create a Read Item and Read List operation at the very least
  4. Save the External Content Type
  5. Open up the options menu of the newly created ECT to create an External List

III. SharePoint Central Admin

  1. Once you are finished with the previous step in SharePoint designer it automatically creates your External Content Type
  2. From the Business Data Connectivity Service Application find the new ECT, select the box next to it, and go to Set Object Permissions located on the command ribbon.
  3. Select a user or group with access to the ECT and grant at least Execute permissions to it.
  4. Select Set Metadata Store Permissions located on the command ribbon

 

Hope you find this useful.