Abstract: With the External Content Types feature provided in SharePoint 2010, communication between two systems can be achieved easily. By using SQL Server as Data Source, you can minimize writing explicit code for database connectivity and perform CRUD operations too.

As a SharePoint 2010 developer, I have come across various requirements from clients on connecting to external data sources e.g. Direct Connectivity to Database, Windows Communication Service (WCF) and External .NET type from a SharePoint Web Site. By considering its need in portal development, I decided to pen down my thoughts on this topic. This article is a result of the same.

Practically there are many scenarios when a SharePoint site needs to connect to an external system which stores data inside it. E.g. A Payroll system which calculates salary of the Employee in the organization wants to communicate with a Leave Management and Processing portal for calculating total leaves of Employee. To do this in SharePoint 2010, we have been provided with a facility of integrating external data provided by Database, WCF and Web Services and .NET assemblies.

In this article, I have explained the mechanism of integrating with external data provided by Sql Server Database. For this article I have used a sample ‘Customer’ Table in the ‘Company’ Database:

For this article, I already have a Team Site on address ‘http://MyServer/sites/ss’. (Note: You can follow all these steps on the site created by you.) To follow my steps, I recommend you to create a Company Database in SQL Server and create the Customer table shown above in this database.

Prerequisites:

Sql Server 2008 R2.

SharePoint 2010 Server.

SharePoint 2010 Designer.

Step 1: Browse the site created by you > Select ‘Site Actions’ and select ‘Edit in SharePoint Designer’. This will start the SharePoint Designer.

Step 2: SharePoint 2010 Designer now shows the Site Information. From the left side pane, select ‘External Content Types’ as below:

This step will retrieve the External Content type.

Step 3: To createa new ‘External Content Type’, click on ‘External Content Type’ in the Top left corner of the Ribbon as below:

This will show the window from where you can set the External Content Type Information. Here in this window, you can set the following Information:

External System, using which the Data Source can be discovered, in this case our Data Source will be Sql Server.

2. External Content Operations: This is used to define Operations which can be performed on the External Data Source e.g. Create, ReadList, ReadItem, Update and Delete etc.

Step 4: Set the External Content Type information as below:

Now Click on the ‘Click Here to discover external Data sources and define operations ’ as shown above, you will get the following window using which you can add connections to external content type:

Click on ‘Add Connection’. You will see a window using which you can select ‘External Data Source type’ > Select Sql Server as below:

After clicking OK, you will be asked to enter Database information as shown below:

Click on ‘OK’ and you will get the Database Connection in Data Source Explorer. Expand it and locate the ‘Customer’ table as shown below:

In this step, you have completed the Connection part.

Step 5: Now after establishing the connection withan External Data Source, it’s time for us to define possible operations on the Data Source. So right click on ‘Customer’ and select all Operations from the Context menu as shown below:

The above image shows self-describing operation types. After selecting ‘Create All Operations’, the wizard will start as shown below. Please read the Instructions.

Click on ‘Next’. The next window will show the Parameter Configuration as shown below. Note: Here you can ignore the Warnings.

Click on ‘Next’. Here it is optional for you to set filter parameters which allows you to set the size of the result set. Click on ‘Finish’. The following operations will get generated as shown below:

Step 6: Once it is done, click on the ‘Save’ button on the top-lfet.

Step 7: Now to create the List, click on ‘Create List & Form’ button on the Ribbon

After clicking on ‘Create List & Form’, you will see a form where you can enter List name and other information as shown below. If you want to create an InfoPath form, you can check the CheckBox ‘Create InfoPath Form’

Step 8: Now go back to the Web Site and Refresh. You will see the ‘CustomerInfoList’ in the Quick Launch window as shown below:

Step 9: Click on the ‘CustomerInfoList’ and you will get the following result:

You must be wondering why did this happen? What does the error ‘Access denied by Business Data Connectivity’ mean? The reason for this error is that, BCS is an external web service which is hosted on IIS and to establish connectivity between the Service Hosted on IIS and a database, an explicit authorization is required. So now we need to do some configuration here.

Step 10: Open SharePoint 2010 Central Administration and Select Application Management. From ‘Application Management’ > select ‘Service Applications’ and click on ‘Manage Service Applications’. You will get a list all the services which are running. From this page, click on ‘Business Data Connectivity Service’ > you will get ‘Service Application Information’ as shown below:

Step 11: Select the ‘CustomerListInfo’ and click on ‘Set Object Permissions’ from the Ribbon. You will get a window for Setting Object Permissions. Add the user info as shown below:

Click ‘OK’

Step 12: Now go back to your site and click on the CustomerInfoList from Quick Launch. You will get the following result:

From here, you can perform ‘Create’, ‘Update’, ’Delete’ and ‘Read’ operations.

Conclusion: With the External Content Types feature provided in SharePoint 2010, communication between two systems can be achieved easily. By using SQL Server as Data Source, you can minimize writing explicit code for database connectivity. In cases where you have a local database available with you, you can use the SharePoint 2010 portal directly to perform CRUD operations with your data source.

Mahesh Sabnis is a Microsoft MVP having 14 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet

Please check access rights of your current user w.r.t SPS web site. Second if the error persist then make sure that your login user is havind admin rights in AD.
Regards
Mahesh

Comment posted by
Mudassar
on Saturday, July 7, 2012 1:58 PM

Hi Mahesh!
First of all great article but i am having problem !The error is

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Hi again !
Solved by my self actually. There was a problem with authentication .
I was accessing the table which was dependent on some other tables !

Thanks

Comment posted by
Jerkski
on Wednesday, July 25, 2012 10:45 AM

Hello! Exciting write -up! Just what I needed. Have just one error, I added my own permissions in step 11, but now get this error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Correlation ID:2f27b578-4549-4e2f-8450-e8870a55e865

I am logging in as a authenticated user...why would it try to run it as Anonymous Logon?

Comment posted by
Jerkski
on Wednesday, July 25, 2012 11:35 AM

Found this link which solved the issue: http://blog.ozzie.eu/2012/04/sharepoint-2010-bcs-login-failed-for.html

Added proper credentials to SQL for authentication. Now, I get this error:

The query against the database caused an error.

Correlation ID:3227e106-60b9-479d-ba44-55b90668f817

Any ideas?

Comment posted by
Jerkski
on Wednesday, July 25, 2012 12:31 PM

Found this link which solved the issue: http://blog.ozzie.eu/2012/04/sharepoint-2010-bcs-login-failed-for.html

Added proper credentials to SQL for authentication. Now, I get this error:

The query against the database caused an error.

Correlation ID:3227e106-60b9-479d-ba44-55b90668f817

Any ideas?

Comment posted by
Jerkski
on Wednesday, July 25, 2012 12:46 PM

Found this link which solved the issue: http://blog.ozzie.eu/2012/04/sharepoint-2010-bcs-login-failed-for.html

Added proper credentials to SQL for authentication. Now, I get this error:

The query against the database caused an error.

Correlation ID:3227e106-60b9-479d-ba44-55b90668f817

Any ideas?

Comment posted by
Jerkski
on Wednesday, July 25, 2012 1:40 PM

Found this link which solved the issue: http://blog.ozzie.eu/2012/04/sharepoint-2010-bcs-login-failed-for.html

Added proper credentials to SQL for authentication. Now, I get this error:

The query against the database caused an error.

Correlation ID:3227e106-60b9-479d-ba44-55b90668f817

Any ideas?

Comment posted by
Jerkski
on Wednesday, July 25, 2012 2:36 PM

Whoa, sorry for the reposts...not sure why that happened. Anyways, I made it past the previous error, but now have this error come up when trying to add a record using my new sharepoint list:

Failed to create a list item for this external list based on the Entity (External Content Type) 'PurchaseOrders' in EntityNamespace 'http://companyweb'. Details: The query against the database caused an error.

Troubleshoot issues with Microsoft SharePoint Foundation.

Correlation ID: 0ad3ec6b-29e2-4421-9d7d-05243b847c6a

Comment posted by
Rikab Kothari
on Wednesday, August 29, 2012 2:50 AM

I want to fetch the data from sharepoint in to excel. I am finding it difficult as it needs authentication. Hope you could help me on this...

Comment posted by
Holly
on Tuesday, September 11, 2012 8:36 AM

Amazing article. I can't wait to do some testing!

Comment posted by
Holly
on Tuesday, September 11, 2012 8:36 AM

Amazing article. I can't wait to do some testing!

Comment posted by
Sri
on Tuesday, September 18, 2012 11:52 AM

Hi Mahesh,

I am working on SharePoint 2010: Programming with Business Data Connectivity Model Using Visual Studio 2010 but the datasource is from AS400.

Please let me know some guidence.

Thank you

Sri

Comment posted by
Balwant
on Tuesday, September 25, 2012 8:10 AM

Dear Mahesh,

First of all thanks for posting this tutorial, because it helped to me configure more in BCS, but in the above permissions steps, we should also set permission with meta deta permissions. I think it solved my issue.

As per my knowledge if you are using DB2 database, then you need to make use of either ODBC or UDL (I am not sure) for the connectivity.
Thank
Regards
MAhesh Sabnis

Comment posted by
Bob
on Saturday, November 3, 2012 5:16 PM

Hi Mahesh,
Really good article, while following these steps, on Step 11 you'd added the user "Administrator", I'm logged on as an Administrator still I couldn't add the user 'Administrator' on that list, I Even couldn't found any of my users while browsing for user using that context menu, do you have any idea??
Regards
Bob

Comment posted by
rajendar
on Wednesday, November 7, 2012 5:47 AM

Excellent article! it saved lot of my time

Comment posted by
Rajat Banerjee
on Friday, January 4, 2013 3:06 AM

Good one!

Comment posted by
Jeff
on Tuesday, March 12, 2013 11:41 AM

Mahesh, followed the steps above and still get Cannot connect to the LobSystem (External System)

Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator.

Comment posted by
Aishwarya
on Wednesday, February 5, 2014 3:06 AM

Hi mahesh

first of all it is Great article

my question is

is there any chance to edit list template from the SQLserver table "Customer".

let me explain clearly, i want to know if we are changing like add column in Customer table(ContectNo int)it will be reflected SharePoint site or not.if you know how to edit list template from SQL server can u tel me the process.

Thanks,
Best,
Aishwarya.

Comment posted by
Bonnie
on Monday, March 31, 2014 5:38 PM

Have you ever had a problem with form loading very slowly when connected to a sharepoint list on a drop-down control. SharePoint list is populated from ECT.

Comment posted by
PWB
on Thursday, April 3, 2014 10:01 AM

Once again, Mahesh, outstanding walk-through!

Comment posted by
Louis
on Wednesday, April 16, 2014 4:29 PM

I am getting this error
Cannot connect to the LobSystem (External System). Reason: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'

Any suggestions on how to fix this will be greatly Appreciated.

Comment posted by
Louis
on Wednesday, April 16, 2014 4:31 PM

I am getting this error
Cannot connect to the LobSystem (External System). Reason: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)'

Any suggestions on how to fix this will be greatly Appreciated.

Comment posted by
nurjalih
on Thursday, May 8, 2014 4:07 AM

great article, thank's

Comment posted by
laxmi
on Tuesday, June 17, 2014 5:47 AM

Nice article!

Comment posted by
laxmi
on Tuesday, June 17, 2014 5:48 AM

Getting error " Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

"Please let me know why i am getting the above error?

Comment posted by
Josh
on Thursday, June 26, 2014 4:49 PM

This may only work if you are running SharePoint and SQL on the same server. This method does not work if you are trying to connect to a remote SQL server as this functionality is broken

Comment posted by
Mahesh Sabnis
on Sunday, July 13, 2014 11:49 PM

Hi Josh,

I that case, create a WCF Service to connect to remote Sql Server and use WCF as a ECT in the SharePoint. Please visit the link below http://www.dotnetcurry.com/showarticle.aspx?ID=799
Regards
MAhesh Sabnis

Comment posted by
Manny
on Thursday, September 18, 2014 2:12 PM

any solution for:

"Unable to display this Web Part. To troubleshoot the problem, open this Web page in a Microsoft SharePoint Foundation-compatible HTML editor such as Microsoft SharePoint Designer. If the problem persists, contact your Web server administrator. "