create BCS in sharepoint designer

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”)

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

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”.  

 

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

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

5. Click “Add Connection”

  

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

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

6. Now the Data Source Explorer view is filled:

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

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

7. You get this dialog.

Click “Next”.

 

Click “Finish”

8. Click “Save” or Ctrl+S.

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

 Now the list will be created:

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

11. Open the list view.

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

 

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

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

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

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 can be tweaked here)

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.

 

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

Click “OK”.

16. Open the list again.