Integration Using SharePoint 2010's BCS Webinar Q & A



This webinar included several live demonstrations and discussed:

  • How to easily access and utilize existing data in other applications from SharePoint
  • BCS versus the Business Data Catalog (BDC)
  • Creating External Content Types and External Lists in SharePoint Designer 2010 & Visual Studio
  • How and when to use the Secure Store Service
  • Best practices for when to leave data in an external application vs. migrating it into SharePoint
  • Avoiding common pitfalls

The webinar demonstrated how to seamlessly connect line of business applications with SharePoint 2010 in accordance with best practices. Learn tips and shortcuts to create a powerful and integrated SharePoint user experience for your organization!


  • Question: Will a recorded version of the webinar be made available?

Answer: Yes, it is available here
Note, if you experience issues with the low-res recording, please try the hi-res version for best viewing.

  • Question: Will the presentation slides and demos be made available?

Answer: The presentation is available here and the link to the recorded demos is provided above. Dave will also make the code from his demos available in downloadable format on his blog

  • Question: How do you make BCS accessible from an Internet facing site (anonymous access)? When I tested this for anonymous access, it always required authentication. Given RevertToSelf, AllowAnonymousExecute and the App pool account have access to the database, what have I done wrong? The same setting works fine in MOSS 2007.

Answer: Assuming that you set up RevertToSelf to control authentication to the data source (i.e. such that it works for authenticated users), the problem is that anonymous users do not have permission to access the BCS service Application itself. This is different than giving them access to the Methods of the BCS entity by using AllowAnonymousExecute. They need access to the BDC model. Normally you would assign these rights in the BCS service application in Central Admin. However, the people picker does not recognize the built-in account ‘NT AUTHORITY\ANONYMOUS LOGON’ so, it cannot be added through the UI.

Here’s the solution; export the BDC model from SPD 2010 and hand edit the XML to add the Anonymous Logon user with Execute rights to the model. The basic process is as follows:

1) Use Central admin to add execute rights to a specific user.

2) Export the BDC default model from SPD 2010 and save it.

3) Edit the BDC model XML and replace the user you added with ‘NT AUTHORITY\ANONYMOUS LOGON’

4) Delete the original External Content Type that you exported

5) Use Central Admin to re-import the BDC model. It will now show in SPD 2010 as the original External Content type. This one can be accessed by anonymous users.

  • Question: When I try to create an external content type in designer 2010, I get an error message "Business Data Connectivity Metadata Store is currently unavailable." Do you know of any way to test the BDC Metadata Store? I've tried deleting it, and recreating, etc... but I still get this message.

Answer: There are several reasons why this can happen. The most common ones are as follows:

1. You haven’t instantiated a Business Data Connectivity Service application in Central Admin yet.

2. You haven’t started the underlying Business Data Connectivity Service on your SharePoint server yet.

3. Your Web Application isn’t subscribed to the BCS service application you instantiated.

4. You are running different builds of SPD and SharePoint, e.g. one is Beta and the other RTM. The build numbers must match or communication/authentication issues can occur.

  • Question: How much functionality will be lost by not upgrading to Office 2010? (i.e. a SharePoint 2010 + Office 2007 environment)

Answer: Microsoft has a knowledgebase article that lists out the BCS features available in SharePoint Foundation, SharePoint Server, and Office 2010. The primary features you will lose if you don’t have Office 2010 are all centered on offline synchronization of BCS data. You won’t be able to take data offline in Outlook or use QuickParts in Word unless you have the 2010 versions. You can find the article here.

  • Question: Can a BDC column value be updated from the DIP in office 2010?

Answer: BDC column values in a Document Information Panel (DIP) in Word 2010 are read only. However you can update the values in a Word document itself by using a QuickPart. Changes made to a QuickPart will be reflected in the DIP immediately and in the document library when the file is saved.

  • Question: How do I create an ECT to an Oracle database source - there's no dropdown for that?

Answer: BCS can connect to a variety of databases including SQL server, OLE DB, Oracle, and ODBC connections. However SharePoint Designer 2010 is only designed to support creation of BDC models connecting to SQL server databases. To connect to the other three you will need to write an XML based BDC model and import it using Central Administration. You can find out more about how to connect to Oracle in this MSDN article.

  • Question: How do you connect to oracle database?

Answer: See answer to the previous question.

  • Question: Can VS .WSPs for BCS be used as a sandbox solution, or does it have to be a farm solution?

Answer: No. BCS uses the admin web service to communicate with the BCS service application so all managed code must be deployed to the GAC. This requires a farm solution. However, you can write a sandboxed solution that USES an external content type built as a farm solution.

  • Question: I can't really tell (since BCS is available in the foundation) whether these types of objects will be available if we're not a MOSS installation. Can you clarify?

Answer: Microsoft has a knowledgebase article that lists out the BCS features available in SharePoint Foundation, SharePoint Server, and Office 2010. The primary features you lose if you only have SharePoint Foundation are BCS Profile pages, the Business Data web parts, and Office Synchronization integration. It should also be noted that the Secure Store service only ships with SharePoint Server and that you won’t be able to build a custom search scope to search BCS data if you only have SharePoint Foundation. You can find the article here.

  • Question: How do you pass the SQL authentication user id password instead of using windows authentication?

Answer: Using SPD 2010 you cannot edit the connection string directly like you can when connecting to data sources in SPD 2007. To use SQL authentication you will need to set up the Secure Store Service and use the Impersonate Custom Identity setting. Then you can store the SQL credentials in the Secure Store Service.

  • Question: How do you use BCS data in custom web parts?

Answer: BCS data in the form of external lists can be accessed through the SharePoint object model programmatically as well as in JavaScript or Silverlight through the Client Object model. External lists are not available through SharePoint 2010’s REST service layer.

  • Question: How do you connect to an XML data source?

Answer: Connecting to an XML data source can be done in 2 ways:

1) WCF Connector – Build a web service that connects to an XML data source.

2) .NET Connector – Build a .NET connector that connects to an XML data source.

  • Question: I created a custom action in a BCS entity in visual studio 2010 however, I cannot see the custom action in external list nor in central Admin-> BCS entity-> Actions.. Could you please advise?

Answer: Without seeing your project directly it is hard to tell which points you have missing to cause your .NET Assembly connector Actions to not show up in the external list or in the Central Admin Manage Service Applications BDC Entity page. For a detailed reference and guidance on building BDC Model Entities in Visual Studio 2010, see the following MSDN reference articles:

1) How To: Create a BDC Model

2) Designing A Business Data Connectivity Model

  • Question: How can I use the WCF service for editing a list and it is possible to specify read only?

Answer: Using a WCF connector, you will need to code web methods for Insert, Update, and Delete functionality, which can be wired up to Create, Update, and Delete Operations in SharePoint Designer.

  • Question: How can I connect to legacy data sources? Is there ODBC support, etc.?

Answer: BCS can connect to a variety of databases including SQL server, OLE DB, Oracle, and ODBC connections. However SharePoint Designer 2010 is only designed to support creation of BDC models connecting to SQL server databases. To connect to the other three you will need to write an XML based BDC model and import it using Central Administration. You might want to look at the third party tool Dave mentioned in the Webinar called BCSMetaMan. It supports creation of BDC models for ODBC data sources. This blog post has a walkthrough using BCSMetaMan to connect to an ODBC based DB2 database.

  • Question: How can I integrate many different mainframe applications into SharePoint if I do not have access to the tables?

Answer: BCS requires access to the data underlying an application in order to do integration. Unless you have access to the underlying tables, views, stored procedures or have a web service that does, there is no way to integrate mainframe applications using BCS.

  • Question: I am using SharePoint Designer to connect to a SQL Data Source, and the SQL Data Source has now changed. Is there a centralized tool to easily change all SQL Data Sources to a new SQL Data Source or do I have to manually do this for each customized list?

Answer: If the name, “location” or schema of the SQL tables has changed, then you will need to update the existing External Content Type. You can do this using the original tools that you used to create the BCS External Content Type.

  • Question: Is it possible to invoke a WCF service (e.g. "insert" or "update" method) from a Submit button, designed using SharePoint Designer?

Answer: SharePoint Designer (SPD) can set up WCF connectors to do inserts and updates. However the standard interfaces to external lists and content types that are set up in SPD are the built-in SharePoint 2010 views and edit pages, which have the built-in buttons. Creating a custom “Submit” button is possible either through InfoPath forms or through a custom Web part, and yes, you can programmatically access the WCF connector insert and update methods from there.

  • Question: Are there any limitations to BCS? Do you recommend a third party solution that will allow me connect to other data sources, protocols (i.e. HL7, MQ Series), SalesForce, etc? We came across a product called Enterprise Enabler and were curious if there are others we should know about to investigate.

Answer: Enterprise Enabler is a set of Pre-built BDC models for connecting SharePoint 2010 to specific external applications likes SalesForce. You could do the same thing using just BCS, but of course it would take more effort on your part. We are not aware of any other specific products like Enterprise Enabler and don’t have enough experience with any specific product to recommend it. You should evaluate it in your context to determine if it meets your specific needs.

  • Question: Does BCS handle simple relational databases like a customer with an associated 1-many address table?

Answer: SharePoint Designer can’t create new joins between tables in a relational database, but it can use Views or Stored Procedures that contain those joins. So yes, it can handle established relationships in databases. Using Visual studio and .Net classes you can of course create new relationships and aggregate data from multiple data sources.