Create an External Content Type in Sharepoint 2010

This is to explain how to display (read/edit/delete) external data from inside Sharepoint 2010 site. For example, to use a custom database in SQL server as the external data source. It involves two steps:
1. Create an external content type – which is to define the external data source (e.g., a SQL server), how to authenticate (the login user, impersonate, or a SQL authentication), what table (or view) to retrieve, what filter to use, and what operations (read/edit/delete) are allowed.

2. Create a list from whis external content type. This list will then be displayed in Sharepoint 2010 site.

(Some security configuration might be needed in order users to see this list)

Here are the details steps:

Note: If the database uses SQL Authentication, there is one extra step – to set up a “Secure Store Application ID” in Sharepoint Central Admin site. The purpose is to set up a “authentication token” so later the ECT can use it to log in to SQL server. This is details in step 0 below.

(So “Secure Store” is sort of like a store that manages all sorts of authentication tokens?)

0. Set up “Secure Store Application ID” – Go to Sharepoint Central Admin web site. Click “Manage Service Application”, “Secure Store Service”. If you haven’t generated any “Key” yet (if it’s a new sharepoint installation, then it’s not generated), simply click the “Generate New Key” button and follow the inrtduction to generate a new key. You need to provide a pass code to ge this done – write down the pass code somewhere in case you need it later.

Once done, click the “New” button on the top left. In the next fowm displayed, enter a value for each textbox. “Target Application ID” is the name of the “token”, so you will want to remember it so you can use it later. Select “Group” in the “Target Application Type”. Click “Next”

In the next screen, update the field name so it’s easier to understand what the parameters (SQL Login ID and password) are:

Also update the field type. Click “Next”.

In the next screen, add local administrator to the “Target Application Administrators” list; and

and add “Domain Users” to the “Members” box so all your domain users can use this token. Click OK to save.

Go back to the “Secure Store Service” home page, right click the saved Application ID, select “Set Credential”.

Enter the SQL login and password. Click OK and the work to create a “Secure Store Application ID” is done.

1. Create an external content type — Go to Designer, connect to the site, click “External Content Types”. Click the “External Content Types” (ECT) button on the top left. You see a screen from which you can create a new ECT.

2. Click the content type name and give it a new name. This seem to be the only chance you can change the name, so do it now. Also update the “Display name” to the same value if you will.

3. Click “Click here to doscover external data sources and define operations”. In the next screen, click “Add Connection” button to add a new connection.

3. Select “SQL Server” if it’s a SQL Server.

4. Enter the database connectiviey information. Select “Cect with Impesonated Custom Identity” and enter the “Secure Store Application ID” that you created in step 0. This “Application ID” will provide the SQL login credential. Click OK.

5. If you are pompted for SQL id/password again, enter the value.

Sharepoint Designer attempts to connect:

6. If connection is successful, you will see the database and all available tables and views.

Pick any table that you would like to be read/edited/deleted from Sharepoint, and right click the table name, and select “Create All Operations”. Click “Next”. In the next screen, select fields that you would like to be made available for Sharepoint. Resolve any “Errors and Warnings” if any.

Click next to set up data filter if applicable. Click “Finish”. The ECT is created.

7. On the same screen, you can create a Sharepoint list from the new ECT just created. Click “Create List & Form” in the ribbon.

If you have not saved the ECT, you will be asked to do so now.

8. Enter list information. Mainly just provide the display name of the list. Click OK.

9. Go to your Sharepoint site, a new list is added to the menu. Click on the link.

10. If you get an “Access denied by Business Data Connectivity”, go to Sharepoint Central Admin à Manage Service Application à Business Data Connectivity Service, right click the content type, and select “Set Permission”.

11. Make sure the users who will access the list from Sharepoint site is added to the list and have proper access.

After that you or anyone should have no problem seeing the list:

Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: