SharePoint 2010: Connecting to SQL Server Using the External Content Type Feature

Posted by: Mahesh Sabnis , on 3/29/2012, in Category SharePoint
Views: 155676
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:

customer-table

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.

sharepoint-designer

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

external-content-types

This step will retrieve the External Content type.

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

new-external-content-type

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:

1. Name of the External Content Type Information

  • Display Name.
  • Office Item Type, this is a List type e.g. Contact, Task, Post, Appointment and Generic List.
  • 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.

external-content-operation

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

set-external-system

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:

add-connection

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

external-data-source-type

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

sql-server-connection

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

data-source-explorer

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:

create-all-operations

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

all-operations

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

parameters-configuration

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:

external-content-type-operation

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

create-list

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’

customer-info-list

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

quick-launch

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

access-denied

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:

service-application-info

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:

object-permission

Click ‘OK’

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

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.

Give a +1 to this article if you think it was well written. Thanks!
Recommended Articles


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Anthony Grace on Thursday, March 29, 2012 10:16 AM
Hi Mahesh,

Great article! Two questions:

1) Is the enterprise license necessary for this approach?

2) Is it possible to source-control this, or how easily can this be backed up for a disaster recovery scenario?
Comment posted by Mahesh Sabnis on Monday, April 2, 2012 7:20 AM
Hi Anthony Grace,
  Thanks for the feedback. I am exploring your queries once it is done I will post the update.
Thanks
Regards
MAhesh Sabnjs
Comment posted by venkat on Friday, May 11, 2012 1:59 AM
Hi Mahesh,
good article
Comment posted by faizan ahmed on Wednesday, June 20, 2012 7:13 AM
i face an crucial error invalid credential  please  contact your system admininstrator colleration ID :7d99991a9 -518a-4c2b-9773-d98ccfb5cbd7
login failled to user
Comment posted by Mahesh Sabnis on Saturday, June 23, 2012 6:13 AM
Hi Faizan,

  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.

Correlation ID:f731a7ea-e46b-4b95-8071-efddeac10208

Thanks in advance
Regards
Comment posted by Mudassar on Saturday, July 7, 2012 2:04 PM
Hi again !
The error is changed to :

Access denied by Business Data Connectivity.
Correlation ID:f26b042a-2ce3-4616-8196-4b8c6bc5172c

Thanks

Comment posted by Mudassar on Saturday, July 7, 2012 2:29 PM
Hi again !
The error is changed to :

Access denied by Business Data Connectivity.
Correlation ID:f26b042a-2ce3-4616-8196-4b8c6bc5172c

Thanks

Comment posted by Mudassar on Saturday, July 7, 2012 3:22 PM
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.
Comment posted by Mahesh Sabnis on Wednesday, September 26, 2012 11:31 AM
Hi Balwant,
Thanks a lot.
Regards
MAhesh Sabnis
Comment posted by Mahesh Sabnis on Wednesday, September 26, 2012 11:33 AM
Hi Shri,

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)

Comment posted by Pratik on Thursday, March 14, 2013 9:16 AM
Hi Mahesh,
Great Article ! Me too getting the same Error as Jeff
Cannot connect to the LobSystem (External System).
Thank !
Comment posted by sangamesh on Thursday, May 30, 2013 2:41 AM
Good Post nice very helpfull.............

Comment posted by sangamesh on Thursday, May 30, 2013 2:53 AM
Good Post nice very helpfull.............

Comment posted by Smruti Nayak on Monday, June 24, 2013 9:22 AM
Good post..
Comment posted by Mohan Dhokare on Monday, August 12, 2013 2:47 AM
Good post helpfull for me.....
Comment posted by Khushi on Wednesday, September 11, 2013 3:27 AM
God arrticle. Unfortunately this doesn't complex fields viz multi-select field, people picker, rich text, etc. :-(

Any thoughts Mahesh? Any workaround?
Comment posted by Desktop Anywhere on Monday, September 16, 2013 2:40 PM
Very nicely done. Extremely helpful.
Comment posted by Al Coston on Tuesday, October 1, 2013 1:40 PM
I also get

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

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel