Walkthrough: Create a simple BCS connection with SharePoint Designer 2010

First: You need a SharePoint 2010 platform. You can use Foundation because BCS = Business Connectivity Services – former BDC = Business Data Catalog in SharePoint 2007 – included in this “free” edition.

Second: You need a database for the walkthrough. You can use Microsoft’s sample database “AdventureWorks”. You can download them here: http://msftdbprodsamples.codeplex.com – I’ll use the database “AdventureWorksLT2008R2”: This is the lite version of the sample database.

Third: The BCS Application of SharePoint 2010 must be configured. – Create this application in the Central Administration –> Manage Service Applications

Fourth: You need a Site Collection. – In my sample it’s “http://sharepoint.local”

1. Open SharePoint Designer 2010 (“SPD”)

Open the site collection.

image

2. Click “External Content Types” in the Site Objects Navigation.

image

3. Click “External Content Type” in the Group “New” on the Ribbon.

In the “External Content Type Information” group click the text message “New external content type” beside the label “Name”. 

image

Enter “AdventureWorks SalesOrderDetails”.

After that the same text will appear beside “Display Name”.

4. Click on the text message beside “External System”:

image

5. Click “Add Connection”

image

Select “SQL Server” in the drop down “Data Source Type” in the dialog.

image

On the next dialog enter the Database Server Name, Database Name and the Name of the new connection. Select “Connect with User’s Identity”.

image

6. Now the Data Source Explorer view is filled:

image

Scroll down and select the table “SalesOrderDetails” in the tree view.

Right click on “SalesOrderDetails”. Click on “Create All Operations”.

image

7. You get this dialog.

image

Click “Next”.

image

Click “Finish”.

8. Click “Save” or Ctrl+S.

9. After the upload click on “Create Lists & Form”.

image

Now the list will be created:

image

10. Open the browser and navigate to the site collection. You’ll see the previously created list:

image

11. Open the list view.

12. You may get the error “Access denied by Business Data Connectivity”.

image

13. Then open “Central Administration” –> Manage Service Application –> Business Connectivity Service Application

Select the BCS application. Open the context menu. Click “Set Permissions”.

image

On the next dialog add all users that should have access to the BCS app and it’s lists.

image

Click “OK”.

14. Try to open the list on the site collection.

15. You may get the error message “Login failed for user NT AUTHORITYIUSR”.

This means: IUSR account has no rights to access the AdventureWorks database.

Open SQL Server Management Studio. Open “Security” –> “Logins” (at server level).

Add “NT AUTHORITYIUSR” as new login.

image

On the “User Mapping” tab change the security settings:

image

Click “OK”.

 

16. Open the list again.

image

Now it works (for me).

10 thoughts on “Walkthrough: Create a simple BCS connection with SharePoint Designer 2010

  1. hi.. aftr a long search i reach ur page.. its really very useful and solved my prblm.. thank u so much.. 🙂 it works for me also.. 🙂 🙂

  2. Pingback: Integrating a custom processor in FAST pipeline – part 1 of 2 | Johnny's SharePoint & Fast Fun Land

  3. Pingback: BCS Example: Take advantage of external data columns in existing BrightWork lists | Manage Projects on SharePoint

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.