Microsoft Business Connectivity Services Team Blog

BCS Artifact Generator

bcsbauth — Fri, 04 Jun 2010 00:23:02 GMT

Chances are you’ve already read Brad Stevenson’s post Types of Solutions and you’re interested in building a BCS solution.

If you’ve read my post on the BCS Solution Packaging Tool, perhaps you’ve already tried to build and package a solution yourself.

To help out developers and power users we’ve created another new tool called the BCS Artifact Generator which will greatly help you get started building working solutions, which you can then customize however you’d like.

To use the tool simply select your BDC Model (see Lionel Robinson’s post Creating an External Content Type in SharePoint Designer 2010 for help creating a BDC Model), give your solution a name, then follow the wizard which guides you through picking and choosing how your solution will look in Outlook. When you click “Generate” the tool will produce a folder containing all of the files necessary to run a BCS solution.

You can then point the Solution Packaging Tool at this folder to package and deploy your solution to Outlook, all in a matter of minutes.

You can download the free Artifact Generator tool from MSDN Code Gallery.

Also, be sure to download the free Solution Packaging Tool, also from MSDN Code Gallery.

Creating a Declarative Solution for Outlook

To create a declarative BCS solution in Outlook, you’ll do the following:

Create the necessary solution artifacts (see below for details)
Put the artifacts in a folder
Create a BCS Package (a special type of VSTO ClickOnce package) using that folder - Optional: put the BCS Package somewhere public (i.e. SharePoint) where your users can then go to deploy the solution
Deploy the solution package to Outlook
You’re done!

Creating a BCS Package is done using the Solution Packaging Tool, which we’ve seen already. This post will examine the first step – creating the solution artifacts. We’ll show you how quickly and easily you can do this using the Artifact Generator tool.

What are the required artifacts?

To have a solution you’ll need the following three files at the very least:

Having these three artifacts will allow you to create and deploy a solution in Outlook. In addition, your solution can contain the following three customizations:

A) A custom item Taskpane (item C in the screenshot below)

B) A custom item Form Region (item D in the screenshot below)

C) A custom ribbon containing item action buttons (item B in the screenshot below)

The following screenshot shows a declarative solution and each of the three aforementioned customizations:

To add a custom Taskpane to your solution you’ll need each of the following:

a) InfoPath Form [.xsn]

b) Layout file [.xml]

c) A few lines of XML added to the Solution Manifest

To add a custom Form Region to your solution you’ll need each of the following:

a) Office Form Storage File [.ofs]

b) Form Region Manifest [.xml]

c) A few lines of XML added to the Solution Manifest

To add a custom ribbon tab with action buttons to your solution you’ll need each of the following:

a) Ribbon definition file [.xml]

b) A few lines of XML added to the Solution Manifest

Using the Artifact Generator

You can now use the Artifact Generator to create most of these files automatically:

Solution Manifest (including the additional lines needed for each customization)
Subscription(s)
Layout file
Form Region Manifest
Ribbon definition file

Before using the tool, be sure to create the following files if you’d like to include them for use:

BDC Model
[Optional] InfoPath form (to show item details in the taskpane)
[Optional] Office Form Storage file (to customize the added form region)

Steps to use the Artifact Generator

Give your solution a name
Import the BDC Model
Click “Next”
You can customize each of the External Content Types (ECT) that are mapped to Outlook item types by clicking “Customize” (see below for customization options):

First, you can change the name that is displayed for that item.
You can also change the icon that is displayed for that item.
Click “Next”.
For the ECT fields (i.e. “Product Serial Number”) that aren’t mapped to regular Outlook fields (like “First Name”, “Phone Number”, etc.), a form region will be automatically generated for your solution. You can also create and customize this region yourself by creating an Office Form Storage file and selecting to use that file for this item.
Click “Next”.
If the External Content Type has an association (in the BDC Model) you can show those associated items in a list on a taskpane (i.e. when you open a customer contact, it will show the orders that the customer has placed).
You can also add an InfoPath form which will show the field details for each item you select in that list. First create and customize the InfoPath form and select it for use in the wizard for this item.
Click “Next”.
To add a ribbon for the item which can have action buttons that perform custom actions for the item, click “Add a custom action” and then choose a name for the ribbon tab and for the ribbon group label where the button appears.
By default the Artifact Generator will help you create one action button to start with. You can easily edit the Ribbon definition file later to add more buttons. This default button is a “URL Action” type of button, which opens up a URL in the browser based on a parameter value for that item. Enter a URL (be sure to add “{0}” in the URL where the value should go) and select a field to use as that parameter.
Click “Finish”.

Click “Generate artifacts”.
A folder containing these newly generated artifacts will be created at the same location of the tool itself. The folder will also open up, displaying the artifacts.

Then, use the BCS Solution Packaging Tool to select this folder and create a new solution package to be used for installation and deployment of the solution. Be sure to select “Outlook Intermediate Declarative Solution” as the solution type.

You should now have created an Intermediate Declarative Solution in Outlook!

Data Solution

If you’ve read my post Overview of BCS Advanced Code-Based Solutions or the MSDN article Creating Advanced Code-Based Solutions Using Business Connectivity Services you’re aware that you can create Office Add-Ins using Visual Studio which can integrate with BCS and external data.

These solutions need metadata and subscriptions on the machine running the solution, which dictate synchronization of the external data between the client and the external system. This metadata can be set up on that machine by having a user deploy a Simple Solution to Outlook or SharePoint Workspace, but this requires additional steps that each user of your solution would have to perform initially.

The ideal and recommended approach is to use a complementary Data Solution to go with your Office Add-In, which will set up the metadata and subscriptions on the user’s machine automatically.

A Data Solution requires the following files:

BDC Model
Subscription(s)

Again, you can either create the subscriptions by hand or use the Artifact Generator to create them for you. Simply import your BDC Model to the Artifact Generator and click “Generate artifacts”. Then, use the Solution Packaging Tool to point to that folder and create a BCS Package of type “Data Solution for Office Add-In”.

If the user deploys this solution and then deploys your Office Add-In, your add-in will be able to display and manipulate external BCS data.

To try this out for yourself, check out the following: Business Connectivity Services Excel 2010 Add-In.

Thanks for reading, and enjoy the tool!

Russell Palmer

Program Manager

BCS Solutions in Outlook

bcsbauth — Fri, 04 Jun 2010 00:04:44 GMT

If you’ve read Brad Stevenson’s post Types of Solutions then you’re already familiar with the three types of BCS solutions:

Simple Out-Of-Box Solutions
Intermediate Declarative Solutions
Advanced Code-Based Solutions

This post will discuss the different types of BCS solutions possible in Outlook 2010.

Simple solutions can be created using out-of-box capabilities, by simply clicking the “Connect to Outlook” button in the ribbon of a SharePoint External List.

At that point, a new folder will be displayed in Outlook. Clicking on the item type in that folder will display the external data items, previously seen in the External List, inside of Outlook either as a Contact, Post, Task or Appointment. See External Lists in Outlook for an example.

The fields that are unique to this External Content Type (i.e. “Customer ID Number”) that were not mapped to Outlook fields (such as First Name, Phone Number, Email, etc.) will be displayed in a standard auto-generated form region when opening up an item. All of the names and icons for the solution will be automatically selected.

For developers that want to create a similar type of solution in Outlook, yet customize it to extend its capabilities and look more uniquely professional, an intermediate declarative solution can be created for these needs. This will allow you to customize the:

Number of External Content Types that appear in the solution folder in Outlook
Solution names and icons used
Taskpane, which displays related items for External Content Types with associations
Ribbon, which contains custom action ribbon buttons
Form region, used to show the unique fields

In addition to these Simple and Intermediate solutions, developers can also create Advanced BCS solutions in Outlook with code, by creating an Outlook Office Add-In in Visual Studio 2010 and packaging it together with a BDC Model, subscriptions and a Solution Manifest in a BCS Solution Package.

Let’s look a little closer at both the Intermediate Declarative Solution and the Advanced Code-Based Solution.

Intermediate Declarative Solution

To create an intermediate declarative solution:

Create the required artifacts (see table below for details)
Put the artifacts in a folder
Create a BCS Package (a unique kind of VSTO ClickOnce package) from that folder
Deploy the solution to Outlook
You’re done!

To create the required artifacts, you can either:

a) Create them automatically using the BCS Artifact Generator tool, or

b) Create them by hand, following the guide Creating Intermediate Declarative Outlook Solutions Using Business Connectivity Services

Be sure to check out the following resources as well:

To package the required artifacts, you can either:

a) Use the BCS Solution Packaging Tool with type “Outlook Intermediate Declarative Solution”, or

b) Use the server APIs to package manually

Here’s an example of what a custom intermediate declarative solution can look like in Outlook:

The following table lists the required and optional artifacts:

Component

Artifact

Details

BDC Model

Metadata.bdcm

Required; must have .bdcm file extension or be named exactly “metadata.xml”

Subscription file(s)

*Subscription.xml

Required, one for each External Content Type; must be named as *Subscription.xml (i.e. “myCustomerSubscription.xml”)

Solution Manifest

OIR.config

Required; must be named OIR.config

OIR or layout resource file(s)

*.resx

Optional

Outlook ribbon file(s)

*.xml

Optional

Layout file(s)

*.xml

Optional

InfoPath form(s)

*.xsn

Optional

Outlook form region(s)

*.ofs

Optional

Form region manifest file(s)

*.xml

Optional

Custom Ribbon Manger, Custom Action and Custom OBPart assembly

*.dll

Optional

Outlook view definition(s)

*.ovd

Optional

Icon(s)

*.ico, *.jpg, *.png

Optional

Advanced Code-Based Solution

To create an advanced code-based solution:

Create the required artifacts (see table below for details)
Create an Outlook Office 2010 Add-In using Visual Studio 2010
Put the artifacts and Add-In DLL in a folder
Create a BCS Package (a unique kind of VSTO ClickOnce package) from that folder
Deploy the solution to Outlook
You’re done!

To create the required artifacts, you can either:

a) Create them automatically using the BCS Artifact Generator tool, or

b) Create them by hand

To package the required artifacts, you can either:

a) Use the BCS Solution Packaging Tool with type “Outlook Advanced Code-Based Solution (OIR)”, or

b) Use the server APIs to package manually

The following table lists the required and optional artifacts:

Component

Artifact

Details

BDC Model

Metadata.bdcm

Required; must have .bdcm file extension or be named exactly “metadata.xml”

Subscription file(s)

*Subscription.xml

Required, one for each External Content Type; must be named as *Subscription.xml (i.e. “myCustomerSubscription.xml”)

Solution Manifest

OIR.config

Required; must be named OIR.config

VSTO Add-In assembly

*.dll

Required

OIR or layout resource file(s)

*.resx

Optional

Icon(s)

*.ico, *.jpg, *.png

Optional

BCS Solution Tools

For more information on the BCS Solution Packaging Tool read the blog post BCS Solution Packaging Tool.

For more information on the BCS Artifact Generator see the blog post BCS Artifact Generator.

How do I optimize backend synchronization?

lionelro — Thu, 13 May 2010 15:55:00 GMT

Business Connectivity Services (BCS) has a synchronization framework that uses a client cache to enable you to work with external data even when disconnected.

In this post, we’ll discuss ways to optimize solutions to best use the synchronization framework client cache. We’ll be using the Contoso Sales Manager sample solution presented in the previous post, BCS Solution Packaging Tool. Please check that post for instructions on how to package and deploy the solution.

How does the client cache work?

When a solution is deployed to an Office client, a process called BCSSync.exe contacts the external system directly and downloads the data into a per-user SQL CE database managed by the synchronization framework.

Every client will be contacting the external system directly, so it’s really important to optimize your solution for offline use to minimize the load on the external system.

Working with Subscriptions

To determine what data will be brought to the client cache, solutions need to define subscription xml files. Each subscription file is responsible for defining how to request data from the external system.

Subscriptions can have 3 types of items: Queries, Explicit Identities and Associations. Queries point to finders that will be executed against the external system to populate the cache, Explicit Identities are additional instances that are added individually to the subscription, and Associations are used to bring related instances.

Here is the subscription file for Contoso Sales Manager solution defining how to request data for the “Customers” external content type.

<?xml version="1.0" encoding="utf-8"?>
<Subscription LobSystemInstanceName="AdventureWorksContosoLOBInstance"
    EntityNamespace="AdventureWorksContoso" EntityName="Customer"
    Name="AdventureWorksContosoCustomerSubscription" View="GetCustomerById"
    IsCached="true" RefreshIntervalInMinutes="360"
    xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Queries>
    <Query Name="AdventureWorksContosoCustomerQuery"
        MethodInstanceName="GetCustomers"
        DefaultDisplayName="Customer Read List"
        RefreshIntervalInMinutes="180" IsCached="true" Enabled="true" />
</Queries>
<Associations>
    <Association Name="GetOrdersForCustomer"
        MethodInstanceName="GetOrdersForCustomer"
        TargetSubscriptionName="AdventureWorksContosoOrderHeaderSubscription"
        TargetView="GetSalesOrderHeaderById"
        LobSystemInstanceName="AdventureWorksContosoLOBInstance"
        RefreshIntervalInMinutes="60"/>
</Associations>
</Subscription>

The subscription above has one query, which runs the “GetCustomers” Finder on the external content type. All Customer instances returned by the finder call will be cached locally on every client that has the solution installed. The refresh interval for the query is set to 180 minutes, so every 3 hours the finder will be executed and the local data will be refreshed on each client.

NOTE: When working with the client cache, it’s important to know that it performs best when the Finder view matches the SpecificFinder view. If the Finder returns a subset of the fields that the SpecificFinder returns, the synchronization framework is forced to call the SpecificFinder on every instance returned by the finder to populate all fields of the instance. If it’s not possible for the Finder and the SpecificFinder views to match, bulk stereotypes can be used to improve performance. Bulk stereotypes will be covered on a later post.

Optimizing Subscriptions

Explicit Identities

If you know exactly which items to synchronize, you can just add their identities to the subscription individually. This is a lot easier to do programmatically since you have to put the serialized identity on the subscription XML file.

To get the serialized Identity, you need to instantiate an Identity object with the appropriate identifier values, and call its Serialize method. More information can be found at the Identity.Serialize method page on MSDN. Once you have the Identity’s serialized string, you just need to add it in an “<Identity>” tag within the “<Identities>” tag, as will be shown later in the example.

Filtering data

The obvious way to optimize synchronization is to reduce the amount of data that is downloaded by the client. The easiest way to do this is by using filters. The filters supported by the synchronization framework that can be used to reduce the number of items downloaded are Wildcard, Comparison and Limit filters.

For example, you can use a Wildcard filter to download all employees whose names start with the letter “M” using “M*” as the filter value. Or you can select all customers that have their zip code equal to “98052” using a comparison filter. A limit filter can be used to limit the number of products to 100.

Example

Here is an example of the subscription above with 11003 and 11020 as explicit identities and a filter to return only customer with CustumerIDs greater than 11050.

<?xml version="1.0" encoding="utf-8"?>
<Subscription LobSystemInstanceName="AdventureWorksContosoLOBInstance"
    EntityNamespace="AdventureWorksContoso" EntityName="Customer"
    Name="AdventureWorksContosoCustomerSubscription" View="GetCustomerById"
    IsCached="true" RefreshIntervalInMinutes="360"
    xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<Identities>
    <Identity>i+yoAAA==</Identity>
    <Identity>iDCsAAA==</Identity>
</Identities>
<Queries>
    <Query Name="AdventureWorksContosoCustomerQuery"
        MethodInstanceName="GetCustomers"
        DefaultDisplayName="Customer Read List"
        RefreshIntervalInMinutes="180" IsCached="true" Enabled="true">
      <FilterValues>
        <FilterValue FilterDescriptorName="MinCustomerId" FilterIndex="0"
           Type="System.Int32">11050</FilterValue>
      </FilterValues>
    </Query>
</Queries>
<Associations>
    <Association Name="GetOrdersForCustomer"
        MethodInstanceName="GetOrdersForCustomer"
        TargetSubscriptionName="AdventureWorksContosoOrderHeaderSubscription"
        TargetView="GetSalesOrderHeaderById"
       LobSystemInstanceName="AdventureWorksContosoLOBInstance"
      RefreshIntervalInMinutes="60"/>
</Associations>
</Subscription>

This subscription will only download the specified customers, as you can see in the image below.

The filters used by the subscription file have to be defined on the model, and the external system needs to support them, too. The model for the Contoso Sales Manager solution and the AdventureWorks web service used by the solution were updated to support a comparison filter on the CustomerID field. The updated solution can be found in the package attached to this post.

NOTE: The version number of the Customer external content type was increased on the updated solution. When editing the model manually, this is an important step to make sure our changes are not ignored during deployment.

Is that all?

Limiting the amount of data downloaded by the client is not always an option. For those cases, version field, Bulk Stereotypes and Last Id Seen filter are a great option. They will be covered in an upcoming post.

- Rodrigo Silveira.

How do I leverage Secure Store from within my .Net Connectivity Assembly?

lionelro — Tue, 11 May 2010 15:00:00 GMT

With Database, Web Service and WCF connectors, there is one and only one external system. BCS understands how to connect to these external systems, and it supports Secure Store integration out-of-box for these connectors. Within a .NET assembly connector, the .NET assembly code can use zero, one or many external systems. Since BCS does not know what the .NET assembly code does, it does not support Secure Store integration out of box for the .NET assembly connector.

Even though BCS does not have OOB support for Secure Store in .NET assembly connector, it provides two different mechanisms to leverage Secure Store within a .NET connectivity assembly. In the first mechanism (no-code approach) BCS does the heavy lifting of reading the credentials from secure store whereas in the second mechanism (code approach) .NET connectivity assembly needs to do the heavy lifting.

This blog explains how both approaches are used.

No-code approach

As the name suggests, your .NET connectivity assembly does not directly interact with Secure Store. Using filters, it can utilize BCS to read the credentials from Secure Store and get the credentials as input parameters to the method. This approach is not unique to .NET assembly connector and can be used with Database, Web Service and WCF connectors.

When writing the method in .NET connectivity assembly, you will need to have input parameters for credentials. The following example shows “GetEntity” method with input parameters for username and password along with other parameters.

public SampleEntity GetEntity(string username, string password, int id){
// parameters username and password
// contains the credentials
}

To get the credentials in the username and password parameters, you will need to use filters UsernameFilter and PasswordFilter. In the metadata model, you will need to define the properties “SecondarySsoApplicationId” and “SsoProviderImplementation” for LobSystemInstance and associate the filters to appropriate input parameter of the method. When BCS encounters filters, it uses the properties “SecondarySsoApplicationId” and “SsoProviderImplementation” for secure store target application id and secure store provider.

<LobSystemInstance ...>
<Properties>
    <!-- other properties omitted –>
    <Property Name="SecondarySsoApplicationId"
        Type="System.String">MyLobAppId</Property>
    <Property Name="SsoProviderImplementation" Type="System.String">
        Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>

Now let’s define the two filters for the method.

With the filters defined for the method, the following snippet shows how to associate the two filters to the input parameters of the method.

That’s all you will need to leverage secure store in .NET connectivity assembly. When BCS executes the method, it will read the credentials from the secure store and set the credentials in the input parameters of the method.

Code-approach

Before we start writing code for leveraging Secure Store, let’s take a look at how BCS leverages Secure Store in other connectors. Other connectors in BCS rely on the metadata model to provide them information about Secure Store and the target application id where the credentials can be retrieved from for authenticating with the external system. For example, in the case of the Database connector, the properties for LobSystemInstance in the metadata model look like this:

<LobSystemInstance ...>
<Properties>
    <!-- other properties omitted –>
    <Property Name="SsoApplicationId"
        Type="System.String">MyLobAppId</Property>
    <Property Name="SsoProviderImplementation" Type="System.String"> Microsoft.Office.SecureStoreService.Server.SecureStoreProvider, Microsoft.Office.SecureStoreService, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>

Reading the metadata model, the Database connector understands that it needs credentials to connect to the database and the credentials can be obtained from the specified secure store provider. Once the database connector has the necessary information for the Secure Store provider, it reads the credentials from the provider and impersonates the user for database connection.

The following sections explain how to read the metadata model properties within your .NET assembly code and use secure store APIs to read user credentials.

Reading metadata model properties

To read metadata model properties in the .NET connectivity assembly, code in .NET assembly must implement IContextProperty interface. The following code shows a sample implementation of IContextProperty interface.

Sample implementation of IContextProperty

using IContextProperty = Microsoft.BusinessData.SystemSpecific.IContextProperty;

public class SampleConnector : IContextProperty
{
    private IMethodInstance methodInstance;
    private ILobSystemInstance lobSystemInstance;
    private IExecutionContext executionContext;

#region IContextProperty implementation

    public IMethodInstance MethodInstance
    {
        get { return this.methodInstance; }
        set { this.methodInstance = value; }
    }

    public ILobSystemInstance LobSystemInstance
    {
        get { return this.lobSystemInstance; }
        set { this.lobSystemInstance = value; }
    }

    public IExecutionContext ExecutionContext
    {
        get { return this.executionContext; }
        set { this.executionContext = value; }
    }

#endregion

// unrelated code removed for brevity
}

When the .NET connectivity assembly class implements the IContextProperty interface, the BCS runtime automatically initializes IContextProperty properties.

At runtime, the code has the context (method instance and LOB system instance) in which it executes; a method implemented in the code can read the properties from the metadata model. The following code shows how LobSystemInstance properties are read:

public SampleEntity GetEntity(int id)
{
    // read the LobSystemInstance property defined in metadata model
    string provider =
        LobSystemInstance.GetProperties()[“ssoProviderImplementation”]
        as string;

// unrelated code removed for brevity
}

Interacting with Secure Store

When the .NET connectivity assembly requires reading one credential from Secure Store, we recommend using the following property names.

ssoProviderImplementation – Secure Store provider
SecondarySsoApplicationId – Target Application for Secure Store

ssoProviderImplementation and SecondarySsoApplicationId properties are supported for modeling .NET assembly connector models in SPD.

If the .NET connectivity assembly requires reading more than one credential from Secure Store, choose a property name that makes the most sense for your scenario.

Sample implementation for reading credentials

The first step is to instantiate the Secure Store provider from the provider implementation. In your code, make sure that the provider implements ISecureStoreProvider interface.

A simple implementation for instantiating the provider may look like

private ISecureStoreProvider GetSecureStoreProvider()
{

// error checking removed for brevity

string provider = this.LobSystemInstance.GetProperties()
[“ssoProviderImplementation”] as string;

Type providerType = Type.GetType(provider);

return Activator.CreateInstance(providerType)
as ISecureStoreProvider;

}

Once the Secure Store provider has been instantiated, it’s time to get the credentials from Secure Store. Secure Store returns a SecureStoreCredentialCollection for the user and target id, which contains a collection of credentials. ISecureStoreSecureCredentials interface defines the credentials type and its value.

private void ReadCredentialsFromSecureStore()
{

// error checking removed for brevity

    string targetId =
        LobSystemInstance.GetProperties()[“SecondarySsoApplicationId”]
        as string;
    ISecureStoreProvider provider = GetSecureStoreProvider();

    // get the credentials for the user on whose behalf the code
    // is executing
    using(SecureStoreCredentialCollection credentials =
        provider.GetRestrictedCredentials(targetId))
    {
        SecureString secureUsername;
        SecureString securePassword;

         // look for username and password in credentials
        foreach (ISecureStoreCredential credential in credentials)
        {
            switch (credential.CredentialType)
            {
                case SecureStoreCredentialType.UserName:
                case SecureStoreCredentialType.WindowsUserName:
                    secureUsername = credential.Credential;
                    break;
                case SecureStoreCredentialType.Password:
                case SecureStoreCredentialType.WindowsPassword:
                    securePassword = credential.Credential;
                    break;
                default:
                    break;
            }
        }
        // username and password have been read
        // use them as necessary.
    }

     //NOTE: Since we are getting the credentials in the using block,
    //all the credentials that we get will be disposed after the
    //using block. If you need to cache the credentials don't use
    //using block but dispose the credentials when you are done
    //with it.

}

The above code shows how the credentials are read from secure store. These credentials can be used for authentication to an external system.

Summary

Depending upon your scenario, you may choose no-code approach or code approach to leverage secure store in your .NET connectivity assembly. The following table lists the differences between these two approaches.

	No-code approach	Code approach
Interaction with secure store API required?	No	Yes
Exclusive to .NET assembly connector	No	Yes
SPD support	Yes	Partially
Impacts method signature	Yes	No
Read from multiple Secure Store	No	Yes
Can credentials be set at LobSystemInstance level?	No	Yes

-Kaushik Raj

BCS Logging

lionelro — Fri, 07 May 2010 09:36:00 GMT

BCS is a cool framework to bring external data to Office Applications on the client. But due to various reasons like misconfigured BDC models/ Advanced Code-Based Solutions/ Intermediate Declarative Solutions or External System’s business logic, the expected data might not show up on the client. Most of the troubleshooting can be done by looking at the event logs. If the information in event logs is not enough to troubleshoot the issue, then the next best thing to do is start BCS logging on the client. Client logs provide rich and descriptive information, which is really helpful in debugging complex issues. Check out “Diagnostic logging in Business Connectivity Services overview (SharePoint Server 2010)” for information on how to enable BCS logging on the client.

There are 2 levels of logging from BCS on the client – Events logs and Trace logs. All the logging from BCS can be found in Trace logs. Whereas only a subset of the Trace logs are logged under Event Logs and they comprise of the important BCS actions which will help the user in figuring out the state of the BCS Solutions on the client i.e. information regarding the Solution download, Install & Uninstall, solution activation, Services startup & shutdown, Solution Registration and Errors. Trace Logs are more descriptive than Event logs and contains the exception stack trace of errors if any.

Here are the different Categories under which most of the BCS actions are logged:

Deployment - The logs under this category help the user to figure out the important deployment actions that happen when offlining an External list from SharePoint or while doing a standalone install of Declarative/Code-Based solutions. All the actions involved with Solution download, Activation, package verification and Uninstallation get logged under this category.
Runtime – BCS on the client has both managed and unmanaged assemblies. All the offlining calls from the managed components of BCS like CRUD operations, Subscription Refresh, Picker resolution, Rich List part actions from office applications, etc…fall under this category.
Wrapped PST – The logs under this category are from the unmanaged BCS Add-in running in outlook.
Business Data – All the BDC Runtime calls involved in executing against the External System are found under this category. These calls include preparation of the request, invoking of BDC stereotype methods against the External System and runtime processing of the results.

By default the values of Input and Return Parameters are not dumped in the logs due to privacy issues. For debugging purpose, you can add the “LogParameterValues” property at the Method Parameter level in metadata to log the parameter values sent and received from the External System for every call.

<Property Name="LogParameterValues" Type="System.Boolean">true</Property>
Cross Sync – Actions involving data Sync between Offline Cache (i.e. SQL CE database) and Outlook Wrapped PST are logged under this category.

Here are some sample logs which outline the important actions logged as part of the E2E life cycle of a WCF based External Content Type (ECT) off-lined to outlook:

Solution Install/ List Offlining logs:
- Runtime - BCSSync started with PID: 4020, integrity level: 8192 and command line: ""C:\Program Files (x86)\Microsoft Office\Office14\BCSSync.exe" -Embedding".
- Runtime - Cache database file not found, creating new database file.
- Runtime - BCS directory encrypted successfully.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.DiskIOThrottler was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.ProxyRegistrationService was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.Deployment.ActivatorService was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.Deployment.VerifierService was started.
- Runtime - Service Microsoft.Office.BusinessData.Offlining.SynchronizationManager was started.
- Runtime - Service Microsoft.Office.BusinessApplications.Runtime.Deployment.UpdateService was started.
- Deployment- Url:file:///C:/Users/<UserName>/Desktop/Output/ContosoSalesManager.vsto Solution Name:ContosoSalesManager Solution Id:ContosoSalesManager Solution Version:1.0.0.0 Message:Solution download completed successfully.
- Runtime - Starting verification process for model C:\Users\<UserName>\AppData\Local\Microsoft\BCS\cont..ager_ae3bf1f59b9d1e0f_0001.0000_0c401f2e52834d92\metadata.xml.
- Business Data - Generating WCF Proxy Assembly in a parallel App Domain
- Business Data - Trying to discover service document at 'http://<Server>:8181/webservice.asmx?wsdl'
- Business Data - Compiling proxy was success, will retrieve the assembly.
- Runtime - Set to Verification Complete for the solution ContosoSalesManager.
- Runtime - BCSSync with PID: 4020 was requested to spawn a new instance and shutdown.
- Runtime - BCSSync started with PID: 4068, integrity level: 8192 and command line: ""C:\Program Files (x86)\Microsoft Office\Office14\BCSSync.exe" /Restart /Activation /RestartApps".
- Runtime - Starting deployment of subscriptions for solution ContosoSalesManager.
- Runtime - Verifying subscriptions.
- Runtime - Generating subscriptions in cache

Wrapped PST Data Sync logs:
- Runtime - Notification is being sent
- Cross Sync - Receiving Cache Changed (Direct) Event
- Runtime - 17 direct change notifications processed at 10:42:11.0782575
- Cross Sync - BCS/PST synchronization: Changing 1 Outlook items with information from item 'e83807ab-4748-4a36-ba32-ab62a9aafd78' IsDeleted=False .
- Cross Sync - BCS/PST synchronization: Folder 'ContosoCustomerFolderID' is scheduled to be synchronized.
- Cross Sync - BCS/PST synchronization: Folder state changed to 'vfId=ContosoCustomerFolderID, status=resync, token=8973, exception=null'.
- Cross Sync - Copied Syncstamp 2010-04-28 17:45:50Z for folder ContosoCustomerFolderID ito XS_FOLDER record
- Cross Sync - BCS/PST synchronization: Completed synchronization of folders: ContosoCustomerFolderID,
- Cross Sync - Syncstamp for folder ContosoCustomerFolderID is set to 2010-04-28 17:45:51Z

LOB communication logs:
- Runtime - Data change detected; restarting synchronization loop.
- Runtime - Refreshing subscription AWWSExampleCustomerSubscription with results of query AWWSExampleCustomerQuery
- Business Data - Opening connection to SystemInstance: http://<Server>:8080/webservice.asmx?wsdl
- Business Data - Creating ClientBase object with type : BCSServiceProxy.WebServiceSoapClient
- Business Data - Using WCF authentication mode : PassThrough
- Business Data - Executing MethodInstance '2147484216' representing Web Method 'GetCustomers'
- Business Data - Closing connection to SystemInstance: http://<Server>:8080/webservice.asmx?wsdl
- Business Data - Exiting out of method to enumerator executor

Most of the BCS logs are pretty descriptive and intuitive. Hopefully this blog post can act as a starting point for debugging issues while developing BCS solutions.

- Sreekanth Lingannapeta

BCS Team Channel: Secure Store Service

lionelro — Thu, 06 May 2010 22:55:31 GMT

We just uploaded the next installment on the BCS Team Channel. In this video Sanjay Rama takes you through a quick introduction and demo of the Secure Store Service.

Secure Store Service is the new single sign on service in SharePoint 2010. It provides the capability of storing secure data (i.e. A set of credentials) and the ability to map this data to a specific identity or a group of identities.The most common scenario for Secure Store Service is an application trying to authenticate against a system in which the current logged in user is known differently or has a different account for authentication. It’s a shared service in SharePoint 2010 and can be used to store credentials of multiple external systems.

Why it’s important to define filters for a good picking experience

lionelro — Wed, 05 May 2010 22:29:35 GMT

Hi this is Juan Balmori with another post for the BCS blog. This is a follow up to my previous post, about the external item picker. There is a problematic scenario that you may face: You are searching for an external item on the picker dialog that you are 100% sure it exists on the external system but the dialog does not seem to work properly and is not finding it. Worse it’s not even filtering by the specified search criteria.

For example in the following image I am searching for an ‘Artist’ whose name is ‘Kim’ and evidently exists as you can see on the search results but the picker is:

Effectively showing Kim
But is also showing a bunch of additional results that don’t even meet the search criteria
On top of that, I get a strange error message telling me that ‘Search criteria cannot be met as Filters are not defined for the External Content Type. Contact your system administrator’

What’s going on?

Well, as the error message is suggesting, the problem is that in this example the “Artist” External Content Type (ECT) does not have a filter defined and when an ECT lacks a Filter, the default behavior is to show the first 200 items and we cannot filter/narrow the search results. And you are going to get that warning.

Moreover, imagine a scenario in which you have thousands of items to pick from; you will need to have a good set of filters defined on your ECT so that your users can find the items they are searching for, and/or quickly narrow their search to the item they are looking for.

So as a general rule of thumb, I strongly recommend you to always define at least one filter on your ECTs.

Fortunately, filter definitions can be done in SharePoint Designer (SPD) and it’s really easy to do it, I will describe how. Filters need to be defined on the read list operations (aka finders, as named in the BDC metadata model). There is a specific dialog to configure a filter for the read list operation as you can see on this image. (Double-click on the read list operation and then hit ‘next’ to get here)

Then follow these instructions:

You need to click on the ‘Add Filter Parameter’ button
Click on the ‘(Click to Add)’ link on the Properties panel to configure the picker. You’ll find the following dialog:
Give the filter a name on the New Filter text box
Select a filter field to be used. In this example I want to filter by ‘Artist Name’ so I select that field.
Define the type of filter to use. For a complete list of filter types and how they work please refer to this article. It’s worth mentioning two frequently used filters types: Comparison and wildcard. For this example, the artist name can have multiple words. (i.e. ‘Franz Kohl’), let’s see what happens when I use each filter type:
- Comparison: If you use the ‘equal’ operator we’ll expect the user to type the full artist name to find items. Hence it will only filter if the user types ‘Franz Kohl’, but will not work with either just ‘Franz’ or with just ‘Kohl’
- Wildcard: If you use the ‘wildcard’ type then the filter will be send to the external system with wildcards. In this case it will filter if the user types ‘Franz’ or ‘Kohl’ (we’ll return all the artists that have the string ‘Franz’ as part of the name or vice-versa).
Finally, identify what to do if the filter value is null. You need to select this option if you have an external list associated with this ECT. Otherwise you’ll see no items when browsing the list (because the filter value will be null).
You can add more filter parameters as needed and add a AND/OR behavior to it. For instance, we are interested to filter by last name and zone.

One very important note regarding the design experience: when integrating specifically with databases, our tools generate the necessary queries to enable the designed filters. If you are using other types of BCS data sources such as web services, WCF or .NET assemblies you need to make sure that your filters are part of their API, before you can model them in SPD.

Here is an example of how the picker dialog is going to look like when we select a ‘wildcard’ type of filter on the artist name and we search for ‘Karen’

Once multiple filters are defined on the ECT, the external item picker algorithm to apply those filters is the following: it appends the resulting items of each filter of each read list operation of the ECT. So to be more efficient we recommend not having a high number of filters/finders on each ECT as this can potentially result in calling a high number of operations on the ECT.

In summary, the most important piece of information for this post is: Always define filters on your ECT, especially when you are going to use it for picking purposes.

Thanks for reading!

- Juan Balmori, Program Manager

Searching External Data in SharePoint 2010 Using Business Connectivity Services

lionelro — Fri, 30 Apr 2010 22:10:50 GMT

Eric White posted great content on how to Search External Data in SharePoint 2010 using BCS. Here is the overview of the post from his blog:

Overview

This article will examine how to use SharePoint Designer to define an external content type (ECT) and how to set up a search crawl that includes that external data. You will then see how to reduce crawl times for large amounts of data by using incremental crawls. All of this will be done using SharePoint Designer, SharePoint Central Administration and some editing of XML.

Before beginning, you should have the following:

Access to SharePoint 2010 Central Administration, including the search service and the business data connectivity service.
Access to SharePoint Designer.
A SharePoint 2010 web application with a site collection. For more information, see Create a site collection (Office SharePoint Server). You can probably use Sharepoint - 80 for these examples.
Access to Microsoft SQL Server. You can use the same instance of SQL Server that SharePoint uses.
A sample database for Microsoft SQL Server (AdventureWorks will be used in these examples. See Sample Databases for Microsoft SQL Server 2008R2 to install.)

These examples will be more meaningful if you have some general understanding of SQL queries and views. Previous experience with XML will also be helpful. Even without that experience, you should be able to successfully complete all the examples.

- Lionel Robinson, Program Manager

Introduction to System Filters

lionelro — Fri, 30 Apr 2010 00:14:33 GMT

Filters are used by Microsoft Business Connectivity Service (BCS) to give values to input parameters while invoking a Method on the External System (LobSystem). But there could be some parameters whose values should be provided by the system (example: username, password) and not from the user. Filters that provide values to these input parameters are called System Filters.

Here is an example Scenario of when to use System Filter: Jim has a web service operation that creates a file. The method takes filename, user name, and password. File name will be passed as user input, but username and password will have to be passed from the system. Jim needs a solution to pass these values without requesting them from the user.

Solution: Jim will have to configure username and password filters for the parameters that match username and password. Jim will also have to specify the “SecondarySSOApplicationID” and “SSOProviderImplementation” on the External System Instance, which will be used by the filters to fetch username and password values. This will make the system provide username and password as values to input parameters.

The different types of System Filters that are supported are:

ActivityID (Activity Identifier Filter)
Activity Identifier Filter is used while calling operations on an external system, and the value to be passed as input parameter should represent a context identity of the invoking operation.
BCS sets a GUID that represents the current operation context as the value on the input parameter which is associated with the ActivityID filter.
Password (Password Credential Filter)
Password credential filter is used while calling operations on external system and the value to be passed as input parameter should represent a password stored in Secure Store.
BCS gets the credentials that map to the External System Instance (LobSystemInstance) properties “SecondarySsoApplicationId” and “SsoProviderImplementation”. It uses the password obtained from credentials as the value for the input parameter which is associated with the Password filter. Note: SecondarySSOApplicationId is different from SSOApplicationId, SecondarySSOApplicationId is used by UserName filter and Password filter. For more information read SecondarySSOApplicationID property on LobSystemInstance.
SSOTicket (Single sign on Ticket Filter)
Single sign on ticket filter should be used while calling an operation on the External System and the value to be passed as an input parameter should represent a sign on ticket for the user from the Secure Store. The target service or application can then redeem this ticket to get the user credentials.
BCS uses the “SSOProviderImplementation” property specified in the External System Instance and requests this provider to issue an SSOTicket. This SSOTicket is passed as the input parameter which is associated with the SSOTicket filter.
UserContext (User Context Filter)
User Context Filter should be used while calling an operation on the External System.The value to be passed as an input parameter should represent the user context.
- In this case, BCS uses the “UserContextType” property on the Filter (FilterDescriptor) to find a value to set on the input parameter. If the “UserContextType” specified is “MachineId”, the value passed to the input parameter will be the unique id for machine’s operating system installation.
- If the “UserContextType” specified is “QualifiedUserName”, the value passed to the input parameter will depend:
  - If the login mode for user is Windows classic, the value will be the windows logon name for the user. This will contain the domain name and the user name (example: domainname /username).
  - If the login mode for user is Claims based, then value passed will be the unique value that identifies the user (example: membership:username, username@provider.com).
UserCulture (User Culture Filter)
User Culture Filter should be used while calling an operation on the External System and the value to be passed as an input parameter should represent the culture of the user environment invoking the operation.
BCS uses the “Format” property on the Filter to find a value to set on the input parameter.
- If the value of the “Format” property is “RFC1766”, the value passed to the input parameter will be the name of the culture. Read about name of a culture.
- If the value of the “Format” property is “TwoLetterISO”, the value passed to the input parameter will be two letter ISO language name of the culture. Read about two letter ISO language name of a culture.
- If the value of the “Format” property is “ThreeLetterISO”, the value passed to the input parameter will be the three letter ISO language name of the culture. Read about three letter ISO language name of a culture.
- If the value of the “Format” property is “ThreeLetterWindows”, the value passed to the input parameter will be the three letter windows language name of the culture. Read about three letter windows language name of a culture.
- If the value of the “Format” property is “LCID”, the value passed to the input parameter will be the LCID of the culture. Read about LCID of a culture.
UserName (User Name Credential Filter)
User Name Credential Filter should be used while calling an operation on the External System and the value to be passed as an input parameter should represent the User Name stored in Secure Store.
BCS gets the credentials that map to the External System Instance properties “SecondarySsoApplicationId” and “SsoProviderImplementation”. It uses the user name obtained from credentials as the value for the input parameter which is associated with the UserName system filter. Note: SecondarySSOApplicationId is different from SSOApplicationId, it’s used by UserName filter and Password filter. For more information read SecondarySSOApplicationID property on LobSystemInstance.
UserProfile (User Profile Filter)
User Profile Filter should be used while calling an operation on the External System and the value to be passed as an input parameter should be a property on the current user’s profile.
BCS uses the “UserProfileProvider” and the “UserProfilePropertyName” properties specified on the Filter to find a value to set on the input parameter. “UserProfileProvider” is used to fetch the current user’s profile and “UserProfilePropertyName” contains the property name whose value on the user profile should be passed to the input parameter.

Configuring System Filters

SPD approach:

System Filters can be configured for input parameters via SPD. The steps to configure System Filters via SPD are:

Open SPD
Click on “OpenSite” and open a site.
Click on “External Content Types” on “Site Objects” pane on the left of SPD.
Click on “New External Content Type” ribbon button to create new external content type.
Click on “Operations Design View” ribbon button to create new methods for external content type.
Click on “Add Connection” button and discover a data source connection.
Enter the method configuration wizard used to design the operations, by right clicking a data source node and selecting the operation that is required.
Move to the Input Parameter Configuration to define filters for input parameters.
Click on the “Click to Add” next to filters to configure filters. The screen shot below shows the link that is used to create filters.
Select New Filter and give it a name.
Use the Filter Type combo box to choose a System Filter as shown in the screen shot below:

Hand Written Approach:

System Filters can be configured by making changes to the model file by hand. The steps to follow to configure System Filters are:

Open the Model file.
Find the method whose input parameters should be configured with system filters.
Add FilterDescriptors section or modify if one already exist, to add filter descriptors that point to system filters. An example to configure SsoTicket System filter is shown below.

<FilterDescriptors>
<FilterDescriptor Type="SsoTicket" Name="NameofSsoTicketFilter">

</FilterDescriptor>
</FilterDescriptors>
Configure your desired input parameter’s type descriptor to point to this FilterDescriptor created in the previous step. An example to configure the above created FilterDescirptor is shown below:
<Parameter Direction="In" ...>
<!-- properties omitted –>
<TypeDescriptor FilterName="NameofSsoTicketFilter" ...>
<!-- properties omitted –>
</TypeDescriptor>
</Parameter>

- Tittu Jose, Software Development Engineer

Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

lionelro — Wed, 28 Apr 2010 00:20:06 GMT

Tajeshwar Singh wrote a white paper which shows you how to use Microsoft Business Connectivity Services (BCS) in Microsoft SharePoint Server 2010 to access and surface BLOB data in the SharePoint user interface and search. Check out the overview below taken from the paper.

Link to document: Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

Overview of the white paper

Microsoft Business Connectivity Services (BCS) is the new version of Microsoft Office SharePoint Server 2007 Business Data Catalog functionality. New features are added that help retrieve binary large object data (referred to as BLOB data) from external systems and make it available in Microsoft SharePoint Server 2010. This article describes the following:

The functionality that is provided by the StreamAccessor stereotype that is introduced in Business Connectivity Services.
How to use StreamAccessor to retrieve file attachments from external systems for viewing and indexing.
How to write the BDC model that is required to consume BLOB data.
The built-in Web Parts behavior for BLOB data, and how BLOB fields can be indexed by SharePoint Server search.

In this article's scenario, the AdventureWorks database that is hosted in Microsoft SQL Server 2008 is used as an external system that contains the binary data. The BDC metadata model is created with a StreamAccessorMethodInstance to retrieve the BLOB field of type varbinary from SQL Server as an external content type. The BLOB fields are modeled as types that can be read in chunks to help Business Connectivity Services read the stream in chunks, and not load the complete content in memory. This can help prevent out-of-memory conditions. An example of such a type is System.IO.Stream in the Microsoft .NET Framework. An External Data Grid Web Part is configured to show the external items with links to download the BLOB. Finally, Search is configured to crawl the BLOBs and show the results in the SharePoint Server search user interface (UI).

- Lionel Robinson, Program Manager

BCS Solution Packaging Tool

lionelro — Mon, 26 Apr 2010 06:02:31 GMT

If you’ve read Brad Stevenson’s post Types of Solutions, you’re already familiar with the range of BCS Solutions across three levels:

Simple out-of-box solutions
Intermediate declarative solutions
Advanced code-based solutions

Simple solutions are easily created through the click of a button, and the user doesn’t have to worry about packaging or deployment (see Andrew Harris’ post External Lists in SharePoint Workspace and Juan Balmori Labra’s post External Lists in Outlook).

Intermediate declarative solutions (as the name describes) are a way to step up from what is provided out-of-the-box with simple solutions, to customize and enhance solutions declaratively – without code – to fit your particular needs and requirements. These types of solutions can include re-usable components created and delivered by other developers, or can include components like InfoPath forms, workflows and Web part pages.

One interesting type of solution can be created by combining several of these pieces, and other pieces specific to regular Outlook customizations, to create a full Business Connectivity Services solution in Outlook: an Outlook Intermediate Declarative Solution.

When all of the components and pieces of this solution are assembled, they need to be packaged together as a Visual Studio Tools for Office (VSTO) ClickOnce package, in the same way the simple “out-of-box” Outlook solution is automatically packaged. Similarly, this solution can then be deployed to user’s computers with the click of a button.

As you’ve probably already guessed, the Solution Packaging Tool will help you to package your Outlook Intermediate Declarative solution. This post will show you where to get the tool, outline how to use it, define the types of solutions it can package, introduce the command line interface for the tool and finally, provide a link to download a sample solution that you can use to test things out for yourself.

Solution Packaging Tool

To begin, download the tool from MSDN Code Gallery:

http://code.msdn.microsoft.com/odcsps14bcspkgtool

To use the tool, you’ll be following these general steps:

Create a folder on your local computer and put all of your solution files in this folder
Create another new folder (which will eventually hold the generated package)
Give the solution a name and version (i.e. “Customer Tracker” and “1.0.0.1”)
Browse to and select the folder from step 1 as the “Source Folder”
Browse to and select the folder from step 2 as the “Output Folder”
Pick a solution type (read on for details about solution types) from the following:

Outlook Intermediate Declarative Solution
Outlook Advanced Code-Based Solution (OIR)
Data Solution for Office Add-In

Input a certificate file (when ready with a production solution) or, for now, just use the default certificate that is provided for use during development and testing
For advanced solution types, add an assembly (read on for details about this solution type)
Click “Package”
Click “Deploy”, or open the Output Folder and double-click the .vsto file

The solution will then be installed in Outlook on that machine.

For more information on Intermediate Declarative Solutions, see the content on MSDN and in the SharePoint 2010 SDK. This tool also contains a help guide (click the “Help” button in the bottom left corner of the tool window) which describes in detail:

Each of the three solution types, and how to package them
The types of files/artifacts that comprise each type
How and where to host solutions after packaging (so users can deploy them)
Using certificate files
Using the command prompt support

For now, however, let’s examine the solution types in a bit more detail.

Types of Solutions

There are three types of solutions that you can package with the Solution Packaging Tool:

Outlook Intermediate Declarative Solutions
Outlook Advanced Code-Based Solutions (leveraging the Solution Manifest)
Data Solutions for Office Add-Ins (which don’t leverage the Solution Manifest)

When we refer to an “Intermediate Declarative Solution” rather than just individual declarative customization components, we’re generally referring to the solution created for Outlook.

If you add custom code assemblies to further enhance this type of Outlook solution, you’re moving up the scale to an Advanced Code-Based solution, but you can still leverage some of the capabilities of Declarative solutions (Solution Manifest, form regions, InfoPath forms) without fully diving into an all-code solution. Specifically, this type of solution would still leverage the Solution Manifest (OIR.config) file to dictate how the External Data for the solution should appear and behave in Outlook. You can still use the Solution Packaging Tool for this type of solution.

Lastly, if you’re interested in developing Office Add-Ins using Visual Studio (for Excel, PowerPoint, etc.) you’ll be fully creating Advanced Code-Based Solutions which control all the functionality of the solution. We do still provide one level of assistance here, however. You can create something called a Data Solution, which is comprised of a BDC Model (containing External Content Type definitions with security and connection information) and External Content Type Subscription files (which define how/when to retrieve data to the Rich Client Cache).

This will allow you to focus on developing your Office Add-In user-experience and functionality, and let the Data Solution take care of the lifecycle of the external items of data to and from the external system. This Data Solution package can also be created using the Solution Packaging Tool. However, the Office Add-In itself will need to be packaged separately using Visual Studio.

So to reiterate, the three types of solutions that can be packaged using the Solution Packaging Tool are:

Outlook Intermediate Declarative Solutions
Outlook Advanced Code-Based Solutions (with a Solution Manifest)
Data Solutions for Office Add-Ins

You’ll notice these three types in the tool’s “Solution Type” dropdown selection menu.

Command Line Interface

The tool also supports a command prompt interface, which some users may find faster and easier to use than the graphical user interface. The commands can also be useful in script files.

Sample Solution to Get Started

If you want to try out the tool, or simply want to check out a sample Intermediate Declarative solution, you can download our Contoso Sales Manager sample solution from the tool site. On the download page, at the right side in the “Releases” section click “Sample Solution”.

A ReadMe file is provided to walk you through setting it up.

This solution allows you to track contacts from an External System database (a sample database provided free by Microsoft named “Adventure Works”) in Outlook as native Outlook contacts, with the ability to create, edit and delete customers to the backend External System.

It also provides a taskpane, visible in the contact view which shows a list of all orders placed by that customer in the database. When an order is selected from the list, the order details show in the taskpane as well.

Finally, there is a URL-based action in the custom ribbon for each customer item, which searches the internet at MSN white pages to look up that user’s telephone number as a parameter and provide contextual information.

Sample Solution pre-requisites

Client

Office Professional Plus 2010

Server

Windows Server 2008
SharePoint Server 2010 with Enterprise Client Access License
SQL Server 2008 (and SQL Server Management Studio)
SharePoint Server 2010 Software Development Kit (and Samples)

Again, for more information on creating Intermediate Declarative Solutions or developing Advanced Code-Based Solutions, see the content in the SharePoint 2010 SDK or on MSDN.

Enjoy!

- Russell Palmer, Program Manager

Authenticating to Your External System

lionelro — Sat, 13 Mar 2010 01:13:15 GMT

When getting acquainted with BCS, most new users work against an unsecured backend. This means they can ignore the issue of authentication and focus on the fun stuff, like making external lists and searching external content. But real world external systems have all kinds of sensitive data: payrolls, inventories, credit card numbers, and more. The permissions built into BCS can help out, but since users could simply connect directly to the external system without using BCS, the external system needs to have its own authentication turned on too.

I’m Adam Outcalt, a Software Development Engineer for BCS, and in this article we’ll look at the authentication modes supported by BCS and how you can use them to make sure you stay in control of your data.

Authentication Modes

Now that we know why backend authentication is important, let’s look at the modes available in BCS. The names are different depending on where you see them, and are summarized in the table below.

Name	SPD	Model
Pass Through	User’s Identity	PassThrough
SSO (Windows)	Impersonated Custom/Windows Identity	RdbCredentials / WindowsCredentials
Revert To Self	BDC Identity[1]	RevertToSelf

Pass Through

Pass Through means to authenticate to the external system with the same credentials that were used to authenticate to SharePoint. Pass Through will not function by default in NTLM, because NTLM does not allow for delegation of identity; if your backend does not allow anonymous logon, you’ll get an error like this one in your external list:

The query against the database caused an error.

And ones like these in the server logs:

The query against the database for LobSystem 'AdventureWorks', LobSystemInstance ' AdventureWorks', MethodInstance 'Read List', caused an error : Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. A severe error occurred on the current command. The results, if any, should be discarded.

The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'Negotiate,NTLM'

This is because your identity made two hops to get to the external system: one from the client machine to the server, and the other from the server to the external system. Since NTLM did not allow for delegating identity, the request to the backend was anonymous. You can get around this problem by using a system like Kerberos that allows for constrained delegation of credentials. (This article can help you learn more about configuring Kerberos with SharePoint.)

Even without Kerberos, Pass Through is still useful for simple testing during development; if you are working from a development server, in some configurations (like using a browser on the server to connect to a database, or using a browser off-box to connect to a database on the same machine as SharePoint), the whole trip will only be a single hop – just be sure to test it with a proper configuration before deploying!

Pass Through can also work with claims; more info on this soon!

Secure Store

The basic idea of using Secure Store for BCS authentication is to avoid the double hop problem of Pass Through by storing credentials in SharePoint. Since we can use the credentials in Secure Store to sign you in again on the server, the double hop becomes two single hops.

The other great thing about Secure Store is that you don’t have to sign in users as themselves; you can map them to a service account instead, preferably one that has rights to absolutely nothing except the external system you want to interact with. This means that you don’t have to give the actual users permissions to the backend at all! As long as the users don’t know the password for the service account, they will only be able to access the external system through external lists and other web parts, and you can use this to restrict the sort of operations they can perform.

Mapping to a service account also leads to large performance improvements, since all the connections are pooled for the single logon. The disadvantage is that the external system won’t be able to record who executed a particular transaction – you can solve this problem with secondary authentication filters, which we’ll discuss later in this article.

Revert To Self

Revert To Self is a popular authentication mechanism carried over from the last release of SharePoint. Using Revert To Self means to connect to the external system as the IIS application pool that is servicing the request. In external lists, this means the application pool for the content page; for timed workflows it means the process that runs the workflow; and so on. Because the application pool of a SharePoint farm is a very highly privileged account, it is not a recommended security practice to use Revert To Self in deployment environments.

Even if you don’t use the application pool for anything besides SharePoint, the account still has enough privileges to subvert SharePoint itself (for example, being an application pool, it has privileges to write configuration information about the farm). This means that anybody able to create or edit Revert To Self systems in BDC has at least enough power to make themselves administrators of SharePoint. This was fine in the last release of SharePoint, because the only people who could create or edit BDC LOB Systems were administrators anyways. This release, though, there are lower ranks of users like tenant administrators and SharePoint Designers (SPD) who have this power.

To keep SPD users from being able to elevate themselves to administrators, Revert To Self is disabled by default, and if you try to import a model that uses Revert To Self, you’ll get an error message like this one:

Application definition import failed. The following error occurred: The metadata object that has Name 'MyWebService' has a Property with name 'WebServiceAuthenticationMode' and value 'RevertToSelf'. This value indicates that the runtime should revert to the identity of the application pool, but reverting to the application pool must be explicitly enabled for the service application by a farm administrator.

You should think very carefully about the structure of your organization before enabling Revert To Self on a production server. It is almost always preferable to set up Secure Store instead; having a legacy model that uses Revert To Self is in general not a good enough justification to turn Revert To Self on. You should only turn it on if ALL of these are true:

You are on a SKU like SharePoint 2010 Foundation that does not have SecureStore
You do not have the resources to create a custom SecureStore
You completely trust all of your site designers, as though they were SharePoint administrators
You have locked down the use of the application pool account account, so that if it were subverted by a malicious site designer, your losses would be limited to the SharePoint farm and the external systems.

Don’t do it just because it’s convenient! Of course, this is about production servers. If you are working on a throwaway development farm, you can still turn it on for testing purposes, but be sure to test it in a real configuration before deploying.

In any case, if you have a good reason to use Revert To Self, here’s how to enable it from SharePoint 2010 Management Shell (what’s that?). You must be on the farm that provides the BDC service application to enable Revert To Self; if you consume it from a federated farm, the administrators of the federated farm would need to enable it for you.

$apps = Get-SPServiceApplication
$bcs = #Do something appropriate here to get the app that is
#BCS. If you’re doing this by hand, just type $apps and look
#for the Business Data one, then index into it like $apps[i].
#If you’re doing it for automation, filter by
#$_.GetType.FullName (not $_.TypeName, which is localized).
$bcs.RevertToSelfAllowed = $true

This is an on/off switch that controls whether new models can be created/imported that utilize Revert To Self. If you later choose to turn it back off, that only prevents the creation of new models, but does not prevent you from executing methods in existing models that use Revert To Self; to solve this problem, you should delete all models in the catalog when you turn it back off. Don’t be tempted to turn Revert To Self on, import a model, and turn it back off, since somebody could edit the model to do malicious things once it exists.

As a final note, if you’re playing with a test copy of SharePoint and set RevertToSelfAllowed to true for the sake of science, there are some scenarios (like tenant sites) where we will detect that there is no legitimate reason for using it, and we’ll ignore the property anyways. Don’t rely on this as a safety net for when it’s okay to enable Revert To Self.

Filters for Secondary Authentication

Like we dicussed earlier, you can improve performance of BCS calls by using Secure Store with a service account to have all of the connections run as the same user. This is great, but now the external system doesn’t know who the caller is! You can solve this problem by using filters, which will insert the relevant data for you. The best filter for this scenario is the UserContextFilter which will insert a unique, human-readable identifier for the current user.

Some companies set up their SharePoint and external systems inside a tightly controlled subdomain that is fully secure. In these cases, filters can be used as a primary authentication mechanism, turning off connection level security for a performance improvement. In this configuration, authentication can be handled by using the Username and Password filters, which will use Secure Store to fill in the correct data before making the method call. SsoTicketFilters can also be used to insert a Secure Store ticket, which is similar to a claim.

Keep in mind that filters are only as secure as the connection to the external system. If a user can connect directly to the external system (such as by offlining a virtual list), he can change the values of the filters to make it appear he is a different user, with or without Secure Store. To use filters as a security measure, only rely on them when they come from a trusted source like the SharePoint server.

Anonymous Authentication

It is possible to configure a SharePoint site to be accessed anonymously. The most common scenario for this is a read-only external list on a public-facing site, so that users can see the list without needing to sign in. In this case, Pass Through will go to the external system as anonymous, and Secure Store will not be usable (you cannot map “anonymous” to a service account in Secure Store). In this situation, you must allow anonymous access to the backend, so be sure to give only read permissions.

Other Security Considerations

But what about those rights you can configure in BCS? The BCS execute right is used to filter who is able to make an external system call through BCS. In some systems, this is meaningful security; eg., if you have Secure Store set up to map users to a service account, and only the service account is able to modify the backend. If you configure it so that normal SharePoint users don’t have execute permissions and don’t know the password of the service account, the external system will be secure.

Notice, though, how easily this degenerates into an insecure setup. If instead the backend allowed anybody to log in, all the carefully-crafted BCS permissions in the world won’t matter, since the users could directly connect to the external system. (Incidentally, don’t rely on security through obscurity, expecting BCS to keep the address of your external system a secret. For example, anybody who can access an external list that has been offlined, or edit a model in SPD, can learn the URL directly; others can learn it by monitoring network traffic, etc.)

Another easy way end up insecure is to expect that just having Secure Store makes you safe, since ordinary users can’t directly invoke it. Anybody who makes web parts with the SecureStorePermission on your SharePoint server can make one that invokes Secure Store and connects to your external system, or makes connections as the application pool.

Recap

As we’ve seen, correctly setting up authentication to your external system is crucial to keeping your data secure. Secure Store is the most desirable authentication mode, and Revert To Self should only be used in very specialized corner cases. Pass Through strikes a balance somewhere in the middle, depending on your configuration. Secondary authentication filters can be used in conjunction with Secure Store to improve performance.

I hope this article has helped you pick the configuration that’s right for you and your company’s security policies.

-Adam Outcalt, Software Development Engineer

[1] You can’t discover an LOB with this setting (it wouldn’t make sense), but you can change a LOB’s properties to this.

Working with BCS in Visual Studio 2010 series

lionelro — Thu, 11 Mar 2010 01:25:56 GMT

The SharePoint Team Blog has a great series that walks you through the creation of an external list using Visual Studio 2010 and the new Business Data Connectivity Designer! The first two posts of the series are already live with more to come.

- Lionel Robinson, Program Manager

Introducing the SharePoint SPIdentity Team Blog

lionelro — Wed, 10 Mar 2010 00:30:00 GMT

Check out the SharePoint SPIdentity Team Blog to read up on how Identity was implemented in SharePoint 2010. There, aside from an introductory post, you’ll find the first to parts to a series of blog articles that talk about configuring claims-based authentication:

Claims-based authentication "Cheat Sheet" Part 1: Forms based authentication and anonymous access
Claims-based authentication "Cheat Sheet" Part 2: Configuring a SAML based sign-in

- Lionel Robinson, Program Manager

Using SharePoint workflows with BCS – Full Trust Workflow Activities

lionelro — Tue, 02 Mar 2010 23:54:00 GMT

In the last post, we covered how to use Sandboxed Workflow Actions to lessen the stress on your external system. In this post, we will cover the final option for writing activities that surface BCS data to be used in SharePoint Designer (SPD): Full Trust Activities. These activities can be generic, but to build complex scenarios they will most likely be specific to a given scenario. In this post, I will show you how to write a simple Full Trust Activity that implements the same scenario as the last post. This time, it will be targeted to the SafeLimits external context type and will not require an external list.

Full Trust Activities are written in Visual Studio and need to be installed in the GAC. They also need an actions file to be placed in the workflow template folder on each farm machine’s file system and an AuthorizedTypes entry in the web.config files on each machine to allow the assembly to execute. These have to be deployed on each farm machine, and therefore require much higher permissions and scrutiny.

Tips on Action Usage and Common Mistakes

Use Full Trust Workflow Activities for BCS if:

You have permission to deploy full trust activities
You want to do any of the following:

Read, Create, and Update data in an external system from a workflow with or without having an external list already provisioned.
Make as few calls to the external system as possible.
Have complex logic that requires looping over multiple items or associations to other external content types without using the Sandboxed Code Service.

Things to keep in mind:

Full trust activities, as their name implies, will run in full trust. This means that you should know exactly what the activity does and trust the author of the activity as though they were an administrator before you deploy the activity.
If you are using this activity in SPD, you need to return a fixed number of items as SPD does not support looping.

Building the Scenario- Full Trust Workflow activity

For this post, the activity we are building will return the approver and safe limit for a given type of expense and employee ID. Information about the external content type is already built into the activity, and the activity will only work on that external content type.

Create the Workflow Activity

I’ve attached a Visual Studio project to this post for the activity we will be talking about. Overall, we want to accomplish the same scenario as before, except this time we are not dealing with an external list; we are going to achieve the scenario solely using the Business Data Connectivity (BDC) APIs. This is a little more complex than just reading an item in a list, since the BDC APIs offer much more flexibility than just a flat list. Before we dive into the code, let’s first look at the properties that are exposed by this activity. Although I have exposed all of the activity’s properties so that they can be edited in SPD, I am building in some defaults so that we do not have to enter the information for those that should be consistent across my deployments.

Name	Description
EntityName	The name of the external content type. We will hardcode this in the sample to a default, but allow you to override the value in the property grid. In production you may not want to allow EntityName, EntityNamespace and LobSystemIsntanceName to be passed in to the activity at all, as any user of SPD can change the workflow to point to a different entity and possibly get information they should not. These properties are here mostly so you can easily see how the code works.
EntityNamespace	The namespace of the external content type. We will hardcode this in the sample to a default, but allow you to override the value in the property grid.
LobSystemInstanceName	The External Systems Instance Name. All of the Instance Names in SPD are the names you type in when discovering the external system. However external content types made outside of SPD can have multiple names, which is why we need to specify one. We will hardcode this in the sample to a default, but allow you to override the value in the property grid.
EmployeeID	This is the Employee ID we are looking up.
Category	The Category of expenses. The implementation in the sample supports Morale, Equipment and Travel as the only valid inputs for this parameter.
Approver	This is returned to the user from the external system. It is the user who should approve the expense.
SafeLimit	This is returned to the user from the external system. It is the limit for that particular user, and for that type of expense.

The next step is to look at the activity code itself. I am not going to go into any real depth into general workflow activity design and instead will just be going over the BCS specific parts of the code. The first thing to notice is that all of the code is contained in the Execute method, which is what the workflow host calls to execute the activity. In this call, the first BDC action we need to do is to get the metadata catalog that contains the “SafeLimits” external content type, as seen in the function below:

private IMetadataCatalog GetMetadataCatalog(SPSite site, ISharePointService hostInterface)

The code may look simple, but there are a few things to note. First, the catalog is farm scoped, not site scoped. This means that this activity will work on any site that is using the same BDC service. The second thing to notice is that the SharePoint APIs we are using to get the metadata catalog will return null if the item is not found. This is different than the BDC APIs, which will throw exceptions if items are not found (more on this later).

Now that we have the metadata catalog, we have to find the external content type and return the correct data. This code is in this function:

private void GetSafeLimits(IMetadataCatalog catalog, ISharePointService hostInterface)

Notice in the code sample that GetEntity is in a try catch block. This is because if the external content type was deleted, or was just not in this catalog, a MetadataObjectNotFoundException will be thrown. Instead of just crashing the workflow right there, I wanted to add an entry into the workflow history list to inform us of this, and then re-throw the exception (which would allow the workflow to fail).

Once we have an external content type, we need to find a specific instance of that external content type. To do this, we use the FindSpecific method of IEntity. This function takes two parameters. First it requires the Identity of the specific instance. In our example, EmployeeID was set as the identifier, so we need to pass the EmployeeID as the Identity. You can see at this point how this has to be targeting this particular external content type. Another entity may have a different identifier with a different name and even a different type. The second parameter is the Lob System Instance that we use to find the item. As stated before, in our case, since we are using SPD, there will be only one Lob System Instance, and we will use the name that is stored in the code for that. You will also notice that FindSpecific will throw a different exception if the item is not found: an ObjectNotFoundException. Again, we will want to catch this and give a nice error to the user before re-throwing the exception so the workflow can fail. Once we have the Entity Instance, the rest is pretty simple. We can use the field names in the square brackets to get the values of the instance.

Now let’s see it in action. J

Setup Instructions

To use the attached sample, you will need to do the following

Unzip the attached file
Open in Visual Studio 2010
Rebuild the solution

Before you rebuild, make sure the defaults for entity name, namespace and lob system instance name are updated to match your setting. These are defined in the GetSafeLimits function.

Deploy the solution

See http://blogs.msdn.com/sharepointdesigner/archive/2007/03/15/adding-activities-to-spd.aspx and look at steps 2-4 on how to deploy the dll, actions file(BCSReadActivity.Actions in the zip) and how to update the web.config

Building the Workflow

In SPD, go to the ExpenseReports document library you created in the last post
Click on New List Workflow
Give the workflow a name and description and continue

Note: At this point, you are in the workflow designer, and you can start to add your actions.

Insert the “Get Safe Limits for Employee” Action

For the first two parameters, you can leave them alone. These are the output variables from the function for Approver and SafeLimit.
For the third parameter, we want to specify the Category. This is already defined on our list, so as before, pick the ExpenseType column using the CurrentItem by using the button
For the last parameter, pick the EmployeeID column using the CurrentItem by using the button
You should end up with something that looks like this:

And that’s it. I hope you found these articles useful and good luck with workflow and BCS!

- JD Klaka

Deploying BCS Simple Solutions

lionelro — Thu, 25 Feb 2010 19:49:57 GMT

Previous posts have mentioned how to represent business data as external content types, creating external lists out of these external content types, and how to eventually make this business data available offline in Office applications like Outlook and SharePoint Workspace. The posts showed that this data is taken offline in form of a BCS simple solution which the user then installs on the client machine. In this post I will talk a little bit more on the nature of this BCS solution and point out some information that is important to know to ensure that you have a seamless experience deploying simple solutions.

When you click the “Connect to Outlook” or “Sync to SharePoint Workspace” button on an external list, the first thing we do is to package all the artifacts relevant to this external list into a ClickOnce application package, which then constitutes our BCS solution. As such, a BCS solution is inherently a ClickOnce application, and all the rules, regulations and limitations that govern general ClickOnce applications are applicable to our BCS solutions. Once this ClickOnce application has been successfully created, and the necessary prerequisites are met on the client, then the Visual Studio Tools for the Office system (VSTO) installer is launched to install the application.

Security in ClickOnce Applications

ClickOnce security model relies on trusted publishers or user prompting to determine elevation of privileges that would allow a ClickOnce application to be deployed to a client. If a ClickOnce application has been signed by a trusted publisher, then the application, without prompting, automatically elevates its own privileges and goes ahead with the installation. However, if the application has not been signed by a trusted publisher, then ClickOnce does not automatically trust this application, and you are prompted to confirm that you want to install the application. However, this prompting is neither automatic nor is it guaranteed: it is determined by the security zone from which the ClickOnce application is being installed from, as explained below.

User Prompting Based on Security Zones

ClickOnce makes use of Code Access Security (CAS) to determine the deployment experience, for instance whether or not you should see a prompt asking if you want to install a solution. By default, ClickOnce relies on the five built-in security zones that are defined in Internet Explorer (IE):

MyComputer
LocalIntranet
Internet
TrustedSites
UntrustedSites

These zones are used by CAS to make trust decisions for prompting level and behavior. Each zone is determined by the full path address of the deployment manifest file (in the BCS case, this is the URL of the BCS solution in the external list.) Below is an example of some URLs and their corresponding security zones:

ClickOnce application URL	Security Zone
C:\contoso\clientsolution\customer.vsto	MyComputer
http://contoso/clientsolution/customer.vsto	LocalIntranet
\\contoso\clientsolution\customer.vsto	LocalIntranet
http://fabrikam.contoso/clientsolution/customer.vsto	Internet
http://www.contoso.com/clientsolution/customer.vsto	Internet
\\127.0.0.1\clientsolution\customer.vsto	Internet

Whether or not a user should see a prompt before a ClickOnce application can be installed is determined by the security zone as follows:

Security Zone	Default Trusting Prompt Behavior
MyComputer	Allow user prompting
LocalIntranet	Allow user prompting
Internet	No user prompting allowed unless the solution is signed by a certificate whose issuer is a trusted Certificate Authority (CA)
TrustedSites	Allow user prompting
UntrustedSites	No user prompting allowed unless the solution is signed by a trusted certificate whose issuer is a trusted CA

The MSDN article “Configuring ClickOnce Trusted Publishers” has more information on security zones and how to deal with prompting (pay special attention to the “Get into the Zone” section).

Our out-of-box deployment experience uses a self-signed certificate to sign our BCS solutions. Being self-signed, therefore, the certificate is not from a trusted CA. This leads to the following default experience for LocalIntranet and Internet zones:

If your external list is in a LocalIntranet zone when you take it offline, then you’ll see a prompt like the following:
If your external list is in an Internet zone then you won’t be prompted, and deployment will thus fail with an error like the following:

Avoiding Deployment Failures due to Default Prompting Policy

To get around the deployment failure described above, then you can choose to do any of the following:

In an enterprise scenario, administrators can provide CA-issued certificates to be used to sign their BCS solutions. Another post on our BCS blog, “How can I get rid of the “Publisher cannot be verified” message when taking external lists offline?” explains how to do this.
In an enterprise scenario, administrators can also push a Trust prompting policy through their Group Policy infrastructure if they want different prompting policy than the default ones explained above.
Users can add the SharePoint site to the list of Trusted sites or the Local intranet sites in IE. Needless to say, this is not recommended for every site and should only be done for sites that the user can vouch for.
Users can add a PromptingLevel registry key that defines the behavior that they want. The MSDN article “How to: Configure Inclusion List Security” explains how to do this. Something to note here is that if you have installed the 32 bit version of Office on a 64 bit machine, then the registry key needs to be created in the corresponding Wow6432Node node.

Prerequisites for BCS Solution Deployment

It is worth mentioning that connecting an external list to either Outlook or SharePoint Workspace can only be done from a SharePoint Server 2010 with Enterprise Client Access License – all other servers such as SharePoint Foundation 2010 will not be supported. Now, for a BCS solution to be deployed to a client machine, it needs to have Office Professional Plus 2010 or Outlook 2010 standalone already installed. Other requirements for connecting an external list to function are the following:

.NET Framework 3.5 (.Net 3.5)
Service pack 1 is also supported. If .Net 3.5 is not installed, then you will see this error message when you try to connect an external list to Outlook:

SharePoint Workspace will show the following error:
Business Connectivity Services (BCS) feature

BCS feature is installed by default when Office Professional Plus 2010 is installed. However, the feature is dependent on .Net 3.5, so if .Net 3.5 is not installed at the time of Office installation, then BCS will not be installed. You will therefore see the above error message if you try to take an external list offline as we’ll first detect that .Net 3.5 is not installed.
If you eventually install .Net 3.5 and then take an external list offline, the first thing we do is to perform BCS Install on Demand. You will at this point see Office being configured, and once this is done, VSTO installer will be launched to install the solution.
If you have .Net 3.5 installed but choose not to install BCS (by marking it as “Not Available” in Office Installation Options under Office Shared Features) then you will see the following error message when you try to take the external list offline to Outlook:

SharePoint Workspace will show the following error:

Browser Support for BCS Solution Deployment

Our mechanism for taking external lists offline makes use of an ActiveX control to check the prerequisites mentioned above. Since only IE supports ActiveX controls, taking external lists offline is only supported in IE. The experience in non IE browsers like Firefox is that the “Connect to Outlook” and “Sync to SharePoint Workspace” buttons will be disabled as shown below:

Summary

In this post, we discussed the nature of BCS solutions and pointed out that they are essentially ClickOnce applications. We talked about the security model surrounding ClickOnce applications, including using trusted publishers to sign the applications, and making use of the various security zones to determine the default prompting behavior. We provided some ways of avoiding deployment failures due to default prompting policy, and finally, we mentioned some prerequisites that are necessary for successful deployment of BCS simple solutions.

- Sam Kabue, Software Development Engineer in Test

Making Web Services BCS Friendly – Part 2

lionelro — Wed, 24 Feb 2010 00:28:47 GMT

As mentioned in the overview post, Business Connectivity Services in SharePoint 2010 allows end users to interact with external data using SharePoint and Office interfaces. For this, BCS provides an abstract data access layer that SharePoint and Office applications can consume. The abstraction is achieved through some stereotype methods supported in BCS. These stereotypes are based on some of the common patterns in popular external systems.

In Making web services BCS friendly- Part 1, we reviewed the semantic requirements for the Read methods required to present external data in the form of an external list in SharePoint 2010. In this post we will look at enabling “Create”, “Update” and “Delete” capability (CUD) and review the semantic requirements that the corresponding backend web service methods have to satisfy.

As mentioned in part 1, these recommendations are applicable to .Net assemblies and WCF services as well. You can see the full list of stereotypes on MSDN.

Creator

BCS supports create functionality through the “Creator” stereotype. This stereotype is used to create an item in the external system. For example, given a business object called “Customer”, this method can be used to create a new customer.

Part1 described a view in BCS. For a create method, the set of fields that are required to create the new item is referred to as the “Creator View”.

Recommendations for the corresponding web service method

To qualify as a Creator, the corresponding web service method should:

Take the fields required to create the item as input parameters
Either take the identifier (or combination of identifiers) of the item as input – OR – Return the Identifier (or combination of identifiers) for the newly created item
The Creator View should be equal to or a subset of the view of the specific finder method (see example below)
If there are multiple specific finders, the creator view should be equal or subset of at least one specific finder view

Creator View and Specific Finder View

As mentioned above, the creator view must be equal to or a subset of at least one specific finder view (It may include read only fields). This is because in external lists, the BCS auto generated forms used for creating items are based on the Specific Finder view. If the creator view has extra fields, these will not be displayed on the auto generated forms and can cause problem with the create operation. To avoid this, BCS will check for this dependency and disable the create operation on the external list if the creator view is larger than the specific finder view.

If Creator View is a subset of the specific finder view, BCS will allow creation of the item with the create operation in external lists. But for offline scenarios (for instance Outlook), an update method will be required as BCS will call the update method immediately after the create method (for updating the fields that are not included in the creator view).

See examples later in this post for different scenarios in which create is allowed or not allowed.

Other notes

The backend web service method should ensure that the create operation is transactional. The BCS stereotype merely maps to the backend method.

Updater

BCS supports update functionality through the “Updater” stereotype. This stereotype is used for updating an item given its identifier (or combination of identifiers). For example, given a business object called “Customer”, this method can be used to update the customer’s phone number.

For an update method, the set of fields that are required to update the item is referred to as the “Updater View”.

Recommendations for the corresponding web service method

To qualify as an Updater, the corresponding web service method should:

Take the identifier (or combination of identifiers) of the item as input
The updater view
- Must contain all non-read only fields in the specific finder view
- Must be equal to or a subset of the specific finder view
If there are multiple specific finders, the updater view should be equal or subset of at least one specific finder view.

Updater View and Specific Finder View

As in case of the Creator view, the Updater view must be equal to or a subset of the view of one of the specific finders. This is because the BCS auto generated forms are dependent on the Specific Finder view. If the Updater view is not equal to or a subset of the view of one of the specific finders, the extra fields in the updater view will not be updatable. Just like the create operation, BCS will check for this dependency and disable the update operation on the external list.

Updatable identifier (or combination of identifiers) can also be updated by this stereotype BUT they are not supported by the default BCS forms.

Other notes

The backend web service method should ensure that the update operation is transactional. The BCS stereotype merely maps to the backend method.

· The BCS update stereotype maps to a single API of the back-end. For aggregation the .NET connector should be used.

Examples

The following table summarizes the dependencies for Creator View and Update View and status for create and update operations:

Scenario #	Fields in Specific Finder View	Fields in Creator View	Fields in Updater View	Operations allowed	Notes
1.	A B (read only) C D E (read only)	A B C D E F	A C D F	Create: Not allowed Update: Not allowed Offline Create: Not allowed Offline Update: Not allowed	The field F for the creator and updater cannot be found on the specific finder. This causes BCS to disable both create and update operations.
2.	A B (read only) C D E (read only)	A B C D E	A C D	Create: Allowed Update: Allowed Offline Create: Allowed Offline Update: Allowed	Values of fields B and E can be specified by user during create but cannot be updated later.
3.	A B (read only) C D E (read only)	A B	A C D	Create: Allowed Update: Allowed Offline Create: Allowed Offline Update: Allowed	Value of field B can be specified by user during create but cannot be updated later. Value of field E is assigned by the external system and cannot be updated by the user. For offline create, the update operation is called immediately after create.
4.	A B (read only) C D E (read only)	A B	A C	Create: Allowed on default EL forms only Update: Not allowed Offline Create: Not allowed Offline Update: Not allowed	Field D cannot be updated. This inconsistency causes BCS to disable the update operation. Offline create is disabled because there is no valid update operation.
5.	A B (read only) C D E (read only)	A C D	No update operation defined	Create: Allowed Update: Not Allowed Offline Create: Allowed Offline Update: Not allowed	Values of fields B and E are both assigned by the external system.
6.	A B (read only) C D E (read only)	A B C	No update operation defined	Create: Allowed on default EL forms only Update: Not allowed Offline Create: Not allowed Offline Update: Not allowed	Value of Field D cannot be set during create and there is no valid update operation defined for setting it later. This causes BCS to disable offline create operation.

Deleter

This stereotype is used for deleting an item given its identifier. For example, given a business object called “Customer”, this method can be used to delete a customer with Id 1001.

Recommendations for the corresponding web service method

To qualify as a Deleter, the corresponding web service method:

Should take the identifier (or combination of identifiers) of the item as input.

In this post we discussed create, update and delete stereotypes required for enabling CUD capability. Future posts that will cover the other advanced stereotypes such as Association Navigators, Id Enumerator etc.

- Sanjay Rama, Program Manager

Overview of BCS Advanced Code-Based Solutions

lionelro — Sat, 20 Feb 2010 01:19:28 GMT

Business Connectivity Services (BCS) provides many out-of-box capabilities that deliver rich, end-to-end solutions. BCS is also a programmable platform, allowing developers to further extend BCS to create customized solutions.

Three types of BCS solutions that can be created are:

Simple Solutions (which leverage out-of-box capabilities)
Intermediate Declarative Solutions
Advanced Code-Based Solutions

For a description of these three types of BCS solutions see Brad Stevenson’s post Types of Solutions.

This post will focus on Advanced Code-Based solutions, which are solutions and extensions coded by developers. They range from rich end-to-end solutions with code components running on the client and on the server, to re-usable BCS components that can be re-used by Power Users for declarative-type solutions.

To begin, let’s look at an important distinguishing factor for these types of solutions: the Object Model (OM) that the developers code against.

Object Model

Developers will code against a particular OM to retrieve and manipulate External Data, either directly from the External System (while connected) or locally from the BCS Rich Client Cache.

There are two significant OMs to consider: the SharePoint OM and the BDC OM. Each display unique advantages and limitations, and as a result are appropriate for use with different types of solutions. The intention of this post is to guide developers to ask the right questions during the planning stages and thus help them to arrive at the best possible design for their solution.

OM Choice – List Parity

External Data can be presented in the form of an External List, which is designed to be analogous to a regular SharePoint List (SPList). Because of this, developers can either code against these SharePoint “External” Lists similar to how they do with regular lists, or they can choose to code against the BDC OM by manipulating the External Content Type directly.

Both the SharePoint OM and the BDC OM have a server component and a client component, and each is described below.

SharePoint OM

External Data can be manipulated through External Lists using the SPList class in the Microsoft.SharePoint namespace of the server-side OM. Developers can also manipulate lists in code from the client, through the corresponding List class counterpart in the client-side OM (known as the CSOM) in the Microsoft.SharePoint.Client and Microsoft.SharePoint.Client.Silverlight namespaces.

BDC OM

The BDC OM is available on both the server and the client in SharePoint Foundation 2010, SharePoint Server 2010 and Office Professional Plus 2010. Operations are performed on the External Content Type, so an External List is not necessary.

For more detail view the Business Connectivity Services Class Library on MSDN.

SharePoint OM versus BDC OM

The following diagram may help to assist you in visualizing the External Data access stack.

The BDC OM performs Create, Read, Update and Delete (CRUD) operations through an External Content Type on the External Data, from both the server and the client.

The SPList class sits on top of the BDC OM to perform CRUD operations on an External List from the server.

The CSOM wraps the server-side OM to perform operations from the client.

When developing on the server, there is an option for which entry point in the stack to code against. The recommended option is to code against the SPList class to manipulate External List data. SPList has benefits such as the ability to run in a Partially Trusted Environment, being widely available to developers and works with any code already written to perform list operations against regular SharePoint lists.

However, there are certain limitations displayed by the SPList class pertaining to External Data. When these limitations are encountered, the secondary option is to code against the lower-level APIs of the BDC OM by manipulating the External Content Types directly. These limitations for fields are encountered when the following requirements occur:

Complex and custom types
No mapping between .NET type to SPField type exists (e.g. System.Object)
ID field is of type int64
Paging or “data chunking” of data
Bulk operations (e.g. reading 25 instances simultaneously)
Streaming data from External Systems (e.g. binary large objects aka BLOBs)
Different back-end views for Create and Update operations
Invoking a SpecificFinder (Read Item Operation) with non-integer IDs

Types of Solutions

Developers can create client components (where the code runs on the end-user’s client machine), server components (browser-based, where the code runs on the server machine), or end-to-end solutions that span both server and client.

For client components, developers can choose to create a Microsoft Office Add-in or a Microsoft Silverlight Application.

There are several different BCS solution designs available, and depend on some of the following factors:

Client or server component
Office Add-in or Silverlight Application
Online-only solution, or using the BCS Rich Client Cache
Will the solution be self-contained, or will it leverage metadata and subscriptions that have already been deployed through other solutions
For Outlook solutions, should you use the Outlook Integrated Runtime Solution Manifest for data cross-synchronization between Outlook and the BCS Rich Client Cache

The following flowchart will assist you to select an appropriate solution design based on these requirements: (Click the image to view it in full resolution.)

As you can see, there are several different types of BCS solutions:

Custom Server Component
Silverlight Application
Office Add-in
- Pre-deployed
- Self-contained
- Online-only
Office Outlook Add-in
- Self-contained

Further documentation in the SharePoint 2010 Software Development Kit (SDK) will explain each of these design types in detail, but for now here are some key explanations:

Pre-deployed: an Office Add-in can leverage the solutions that have already had metadata and subscriptions deployed to the client of a machine. These types of solutions discover and reflect upon data in the BCS Rich Client Cache.

Online-only: an Office Add-in which accesses data directly from the External System through the BDC OM or by using the client-side OM to go through the server when special circumstances require them to do so (i.e. a corporate firewall blocking direct access to the External System).

Self-contained: an Office Add-in similar to the pre-deployed type, except that is uses a special data-only deployment package (aka Data Solution) to bring the data to the cache.

Summary

In this post we’ve looked in more detail at one of the three types of BCS solutions: the Advanced Code-Based Solution. We mentioned that developers can create re-usable components, client solution components, server solution components, or full end-to-end solutions that span server and client.

Then, we introduced the differences between Object Models and our recommendations for when to use the SharePoint OM and when to use the BDC OM.

Finally, we looked at some important design considerations and the types of solutions that these design characteristics can lead us to. We also brought up some of the different variations of data deployment for Office Add-ins.

Thanks for reading.

- Russell Palmer, Program Manager

BCS PowerShell: Introduction and Throttle Management

lionelro — Wed, 17 Feb 2010 01:10:29 GMT

Your external list worked great when you tested it, but it’s not working on the deployment server! Reading the SharePoint logs, you see a message like one of these:

Timeout expired. BDC Runtime Object Model has throttled the response. The response from the service took more than '180000' milliseconds. The limit can be changed via the "Set-SPBusinessDataCatalogThrottleConfig' cmdlet.

WCF Service Connector has throttled the response. The response from the WCF service contains more than '3000000' bytes. The maximum amount of data that can be read through WCF Service Connector is '3000000' bytes. The limit can be changed via the 'Set-SPBusinessDataCatalogThrottleConfig' cmdlet.

Web Service Connector has throttled the response. The response from the web service contains more than ‘3000000’ bytes. The maximum amount of data that can be read through Web Service Connector is ‘3000000’ bytes. The limit can be changed via the ‘Set-SPBusinessDataCatalogThrottleConfig’ cmdlet.

Opening of a new connection is throttled. Maximum enabled connections ‘200’ for proxy are already been opened.

Database response throttled. Maximum number of rows that can be read through the database system utility is 2000.

I’m Adam Outcalt, a Software Development Engineer for BCS, and in this post, we’ll look at what these messages mean and how to address them.

This type of error means that BCS has throttled an external system call for taking too long, or trying to transfer too much data. This feature is enabled by default to prevent Denial of Service attacks that could adversely affect SharePoint or external system health by performing huge transactions. If you have a business need, you may want to increase the limits or disable them entirely, but keep in mind that you increase your farm’s exposure to Denial of Service threats. (It is possible to throttle on the Office rich clients as well, but this is separately controlled by Local Policies and is outside the scope of this post.)

Only farm administrators can change throttling rules, and the rules cannot be changed through the web controls or SharePoint Designer; you must change them from one of the farm machines, using public APIs or PowerShell. This means that if you are a tenant administrator or site collection administrator, you cannot change throttling rules for your hosting farm; in this case, you should contact your host or administrator to discuss the limits with them.

If you are the farm administrator, good news! We’ve made a broad set of PowerShell cmdlets to help you with various administration tasks. Almost anything that you can do through the web interface, you can also automate through PowerShell, from importing models to managing permissions, and there are a few things that you can exclusively manage in PowerShell. For this post, we’ll be focusing on throttle management, but it helps to have a little context on the SharePoint 2010 Management Shell in general.

SharePoint 2010 Management Shell

The SharePoint 2010 Management Shell is just like any other PowerShell prompt, except that when you start it, it automatically loads the SharePoint cmdlets. If you want to use another shell, just type this command to get all of the BCS cmdlets:

Add-PSSnapin Microsoft.Sharepoint.PowerShell.dll

The Get-Command cmdlet is very useful for finding out about commands available to you. You can see all of the BCS cmdlets by typing:

Get-Command -Noun SP*BusinessData*

Throttle Management

The entry point for most BCS cmdlets is the Service Context, which represents a tenant using a BCS Service Application Proxy. Throttle Management works instead on the Proxy itself, irrespective of tenants. This means that if you are a host farm administrator, you cannot change limits per-tenant. (If you want to do so, you can set up a unique proxy for each tenant, and associate those proxies with their respective web applications.)

The first step, then, is to get the BCS proxy that you want to work with. You can get all of the proxies on your farm with Get-SPServiceApplicationProxy. You can filter this by name to get the one you want, or index into the results. One easy way to get the BCS proxy is:

$bdcProxy = Get-SPServiceApplicationProxy | where {$_.GetType().FullName -eq ('Microsoft.SharePoint.BusinessData.SharedService.' + 'BdcServiceApplicationProxy')}

Once you have a handle on the proxy, you can start working with the throttling rules. BCS throttle management works on a change-and-refresh strategy. If you do anything to change a throttling rule, you should re-fetch it from BCS to be sure that you have the latest copy. To view a rule, run a command like this one:

$dbRule = Get-SPBusinessDataCatalogThrottleConfig -Scope Database -ThrottleType Items -ServiceApplicationProxy $bdcProxy

If you then type $dbRule by itself, you’ll see something like this:

Scope: Database
ThrottleType: Items
Enforced: True
Default : 2000
Max: 1000000

Scope and ThrottleType describe the rule you’re working with. Enforced represents whether it’s turned on, and Default is the limit that is applied to external list. Custom web parts can override the Default limit and work on more data than external lists; they are instead limited by the Max limit.

Once you have a rule, you can change it using a command like any of these examples:

#Default and Maximum must be provided together. This increases the limit for external lists to 3000.
Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Maximum 1000000 -Default 3000

#This disables a throttling rule. Notice the “:” instead of a space.
Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Enforced:$false

#This enables a throttling rule.
Set-SPBusinessDataCatalogThrottleConfig -Identity $dbRule -Enforced:$true

Remember that if you run one of these commands and immediately type $dbRule, you won’t see the changes reflected; you should re-fetch the rule using Get-SPBusinessDataCatalogThrottleConfig again. Your changes are immediately committed by Set-SPBusinessDataCatalogThrottleConfig, but it may take a few minutes for them to be reflected in external lists and other runtime scenarios.

So, what kinds of rules can you set? There are four ThrottleTypes and five Scopes:

ThrottleType	Meaning
Items	The number of records returned
Size	The amount of data returned, in bytes
Connections	The number of connections opened to the database, web service, or .NET assembly
Timeout	The time until an open connection is terminated, in milliseconds

Scope	Meaning
Global	Applies to Database, Web Service, WCF, and .NET Assembly Connectors (not to Custom Connectors)
Database	Applies to Database Connectors
WebService	Applies to Web Service Connectors
Wcf	Applies to WCF Connectors
Custom	Applies to Custom Connectors

Here’s a table of the rules that exist:

	Global	Database	WebService	Wcf
Items
Size
Connections
Timeout

You’ll notice that there are no rules for the Custom Scope. This value is reserved for advanced scenarios outside the scope of this post.

Once you have your throttles set up how you like them, be sure to check out some of our other cmdlets with:

Get-Help <cmdlet> -Examples

They can save you a lot of time on repeated tasks!

- Adam Outcalt, Software Development Engineer

Surfacing business data in Word 2010 using External Data Columns and the External Item Content Control.

lionelro — Tue, 16 Feb 2010 01:34:57 GMT

Another post for BCS… this is Juan Balmori, I’ve being working in the SharePoint 2010 release on how to simplify customers lives by using data from external systems in Office client applications such as Word and Outlook. This time I am going to focus on how to pump business data into Word 2010. In order to be successful here you’ll need to understand the concept of External Data Columns (aka EDCs) and content controls.

In SharePoint 2010 there are three main artifacts you can use to play with external data: External Lists (which have being discussed in great detail on this blog), External Data Web Parts, and External Data Columns, the main subject of this blog entry. An External Data Column enables you to aggregate information from external systems into regular SharePoint lists, such as a document library. Typical scenario for this is to add a ‘Customer’ column (whose data lives in a CRM system) into document library that stores ‘Contracts’. This feature was previously shipped with MOSS 2007 as Business Data Column (or BDIL), but this time you can pick the desired external data in Microsoft Word 2010 inside the document content (and not just resolve on the document properties panel).

We also learned that many users require embedding data from external systems into Word documents (i.e. customer and/or product names, prices info into purchase orders documents). Typically this implies switching to a complex backend system, finding the data you are interested, copying that data, switch back to word and paste and format the information there. We aimed to simplify that experience by making external data columns available as content controls inside Word 2010.

It is the combination of both concepts (EDCs and Content Controls) which makes it possible to have external data into Word! Now let’s clarify the concepts with a simple example:

Assuming you already have an External Content Type ‘Customer’ (from your CRM system) you want to create a document library of ‘Contracts’ and you need to add customer information on it (such as name, address, phone, etc.). You’ll need to follow these steps to pick a ‘Customer’ for the contract directly from the CRM system and within Word:

Create a ‘Contracts’ document library based on Microsoft Word documents.
Create an External Data Column ‘Customer’ to the list and configure it to use the ‘Customer’ ECT.
- Select the primary field: represents the field to be shown in the column and content control.
- Select the secondary fields: additional fields to be automatically filled upon primary field selection.
Edit the template associated with the document library and insert the EDC fields (by navigating to the document library settings, then select ‘Advanced settings” and finally click on the ‘Edit template’ option).
- Once in Word, on the Insert Tab -> Quick Parts -> Document Property dropdown you’ll find the primary and secondary fields that where defined on the ‘Contracts’ document library.
- Notice that the primary field is represented by the external item content control on the document. Secondary fields are represented as read-only text boxes.
Save the template.
Create a new document on the ‘Contracts’ document library
Use the external item content control to indicate the customer for this contract. If you are familiarized with the people picker, the you are going to love this content control which will enable you to either:
- Pick a customer for the contract: by clicking on the ‘Select’ button of the external item content control you’ll pop the external item picker that will let you choose the item, in this case customer that will sign the contract. There are a few important points you need to consider while designing an ECT that is desired to be used for picking purposes that directly impact the data that is shown and the behavior of the picker dialog:
  - How the picker retrieves data: As you know BCS provides functionality to work offline by storing external data on the BCS client cache. The picker tries to search data as fast as possible so if the cache has data it will show what’s available there. Depending on the nature of the problem you are working on, for example if ‘Customers’ are added very frequently, the cache may not have the latest data available, and you may not find the item you are looking for. You’ll see a ‘More Results’ button on the picker dialog that will enable you to search beyond the cache and find more items online. By hitting the ‘More results’ button you’ll also refresh the cache with the latest data, so in subsequent picking operations you can find refreshed cached data.
  - Read List Method (aka Finder): The query that is going to be executed to get the initial set of items to be shown on the picker will be the default read list method, in the way described above.
  - Show in picker option: While designing an ECT you can configure which fields/columns are to be shown on the picker (for example Customer Name, Address, Phone, etc). Check those columns you would like to see by using the ‘Show in Picker’ option while designing your ECT on SharePoint Designer. If none is specified by default we’ll show all the columns of the ECT.
  - Filter configuration: Always define filters so that you can narrow your search. Especially when working with high volume of items, there might be cases in which you may have millions of items to pick, we’ll show up to 200 items and if the query exceeds that, we’ll pop a warning to narrow your search criteria. We can only do this if you have defined filters on your finder, so use them, otherwise you can potentially not find the item you are looking for.
  - Display name: Once you have picked (or resolved, see below) an item, the ‘Customer’ field that is going to appear in the content control is going to be the field specified as ‘Title’ (by default), unless in your model you have identified a display name field for that ECT. Set the title while designing your ECT on SPD o the operations design view, select the field and then click on the ‘Set as Title’ button.
  - Resolve the customer: This option is useful if you know the identifier of the item of interest. For example you know in advance that customer Contoso’s ID is ‘123’ you can type it on the content control and hit resolve (or type ‘Ctrl + K’), if it exists it will show the field marked as ‘display name’. The other two options are that the item does not exist or that there are several items with that name, for example, ‘Contoso Inc’ and ‘Contoso Northwest’, if that’s the case a disambiguation dialog will be popped so that you can select the right item, this is when it’s important to have good fields to be ‘shown in picker’ so that you can decide which is the correct item to be selected. Whatever is used in the specific finder will serve as the key to match what you are looking for.

That’s the basic functionality. You’ll probably have the following questions:

Can I use Word to change the item values and update the external system? No, you cannot change the item on word. This is read only functionality. The columns on the list will be updated with the values picked in the document.
How can I write back to the external systems on these scenarios then? What you can do is to create a workflow when the document is uploaded to the document library that will be capable of writing back to the LOB systems as JD describes on his workflow post.
What happens if I forward the document to a colleague? The correct and recommended way to share a document is to provide the link to the document in the document library. That way, users will have full access to the functionality.If you copy the document in a share or send it as an e-mail the following scenarios may happen:

Scenario	Behavior
Recipient has Word 2010, BCS installed and data on the cache	Users will be able to read the data in the document and pick and resolve from the cache, they can upload the document manually to the library. Document will replace existing copies.
Recipient has Word 2010, BCS installed and an empty cache	User will be able to read the data but will not be able to pick or resolve new data.
Recipient has word but not BCS installed	May receive prompts to install .NET framework 3.5. User will be able to read the data but will not be able to pick or resolve new data.
User has Word 2007	User will be able to read the data but will not be able to pick or resolve new data, the external item picker control will not be shown.
User has a previous version of Word.	Data is read only as text.

-Juan Balmori Labra, Program Manager

Working with Complex Data Types in Business Connectivity Services

lionelro — Thu, 11 Feb 2010 06:25:00 GMT

Business Connectivity Services is an enabling technology for integrating data from external systems (e.g. line-of-business like an ERP or CRM system) with SharePoint 2010 and Office. Data in external systems is frequently exposed through granular web services with complex message structures. While parameter structures of almost any complexity are supported by the schema for external content types, they can create incompatibilities with presentation technologies in SharePoint and Office.

Before I begin talking about solution approaches, let me define what makes a type too complex to handle with out-of-the box capabilities. External content types define stereotyped operations, many of which require a parameter structure that is technically complex. A specific finder (also called Read Item in SharePoint Designer) must return a single complex type which contains the set of fields for an external content type, e.g. a type “Customer” with fields “CustomerID”, “Name”, and “PhoneNumber”. The child elements of this structure map to the columns of an external list. Most external content types contain a finder method which returns a collection of the same type as the specific finder. Issues arise when any of the fields has child elements of its own or if a field is of a type that is not supported by the presentation technologies, e.g. if a “CustomerAddress” field were added to the customer structure which has in turn child elements defining “Street”, “City”, “State” and “PostalCode”.

One of the primary user interface components for external data SharePoint is the external list. It is by design a flat, row-based representation of data. Without additional work by a developer, complex types and simple types that are not supported in the SharePoint List type system will be omitted from the list view.

The BCS external content type schema supports the notion of complex formatting for read-only scenarios if the complex type can be converted to a simple string using a .NET format string (example below). If read-only support is not enough or the complex type cannot be converted with this method SharePoint 2010 provides the capability to create custom field types which provides control over rendering and editing behavior in external lists and list item forms.

Forms represent the primary user interface for external list items. Complex types pose a challenge to form generation because they are very difficult to layout automatically while maintaining a minimum level of visual appeal. External lists may be associated with SharePoint list forms (based on ASP.NET) or InfoPath forms.

Each approach warrants its own post. In the remainder of this post I’ll provide an overview of each technique and links to walkthroughs and articles that can take you the rest of the way. Please send us your comments about what was helpful or where there are gaps so we can add more content where needed.

So, let’s talk about solutions.

Complex Formatting in BCS External Content Type

If the complex type is read-only on the external list and can be rendered as a string, scenarios in which a complex type can be converted to a string, BCS supports the notion of a format string on the type descriptor for a complex type to render it in an external list or item form. Here’s an example based on the Customer type mentioned above:

SharePoint Compatible Service Layer

The cleanest approach, architecturally, is to create a service layer with a flat parameter structure that exists within the limitations of the presentation layers. To continue with the example from above, if the web service were to return all fields representing the customers at the same level, SharePoint could render and edit customer items without any further modification.

This approach comes with the cost of writing, hosting, and maintaining custom code that has to be traded off against the cost of handling complex types in the presentation layers for each application using a complex service. In many enterprise deployments a service layer already exists. Especially for business entities that have broad use in many applications, e.g. Customer or Product, the cost of adding methods for use with SharePoint may be far lower than the cost of handling the type complexity in each application.

.NET Connectivity Assemblies

If adding or extending a service layer is not an option, SharePoint 2010 provides a way to package the external content type definition with a class that encapsulates the business logic behind an external content type operation. You can define the flattened interface of the external content type using the Business Data Connectivity Designer in Visual Studio 2010 and implement any aggregation and type conversion logic needed in the code stubs generated by the tool. Once the implementation is complete, Visual Studio can generate a WSP package containing the external content type definition and the assembly which can be imported to SharePoint. In order to import this type of package, farm administrator privileges are required because the code behind the external content type runs in full trust mode. For more details on the creation of .NET connector assemblies see Boris Scholl’s overview of the Business Data Connectivity Designer in Visual Studio 2010.

Since modifying the behavior of backend services is not always an option, SharePoint 2010 also provides several ways to extend the presentation layer to handle with complex types.

Extending the External List Type System

The list infrastructure supports a set of generic data types out of the box. Those are sufficient to address the most common data entry scenarios. In order to represent complex business data items like addresses or consistently for data viewing and editing, SharePoint provides a way to define custom field types which can be made available across all sites on a SharePoint farm. The custom field type definition file is at the center of a custom field type. It defines how a field is to be rendered on the list and item views. It also defines any variable properties and a reference to the assembly implementing the field type. The field class inherits from SPField and represents particular fields based on the custom field type. It defines the capabilities of a column based on the custom field type, e.g. sorting, as well as the behavior of a cell within a list and the content database. It may also implement custom validation logic.

Most complex custom field types also require a rendering control class, a rendering template or a custom value class. More details about custom field types and all the concepts mentioned see the MSDN article about custom field types and the walkthrough for creating a custom field type.

Once the custom type is defined it can be referenced by type descriptors in the external content type definition like in this example:

<TypeDescriptor TypeName="Customer.Address” Name="CustomerAddress" DefaultDisplayName="Customer Address">
   <Properties>
       <Property Name="SPCustomFieldType" Type="System.String">Customer Address</Property>
   </Properties>
</TypeDescriptor>

With that addition, an external list based on the external content type understands that type descriptor “CustomerAddress” refers to a custom field type and renders it appropriately.

Customizing SharePoint List Forms

When you create a new external list, SharePoint will automatically generate forms to enable the creation, editing and viewing of list items. SharePoint forms are based on .NET Framework controls and share their extensibility capabilities which can be used to render complex types appropriately. Custom field types described above is the extensibility mechanism most often used for complex types. For simpler cases it may be possible to extend a native SharePoint Foundation field. For more details see the MSDN article on list form customization.

Customizing InfoPath Forms

SharePoint 2010 also supports the use of InfoPath forms for viewing and editing list items. InfoPath provides a number of features that make it easy for non-developers to create attractive forms with powerful validation and conditional formatting. Another advantage InfoPath forms have over standard list forms is their ability to function inside the browser as well as standalone or in rich clients. E.g. InfoPath forms are the forms technology used to work with external list data offline in SharePoint Workspaces. In SharePoint 2010 InfoPath forms for external lists cannot be created from scratch in the InfoPath Designer. They can be generated in SharePoint Designer either from the external list or external content type designers. Once generated, they can be customized in InfoPath Designer.

For external content types containing complex types, the forms generator will generate a blank form due to the difficulties in automatically generating a visually appealing layout. However InfoPath does support complex types in the designer. After the form is generated it can be opened in InfoPath Designer from the external list designer in SharePoint Designer. The custom types are available in the field list and can be laid out manually.

Figure 1 - Field List in InfoPath Designer

Once the customizations are complete the updated form has to be published back to the external list using the Quick Publish available in the File menu.

Figure 2 - Quick Publish Button in InfoPath Designer

I hope this post provided guidance on how to overcome the incompatibility issues between the complex world of external systems and the presentation layers in SharePoint and Office.

-Thomas Mechelke, Program Manager on the SharePoint Business Connectivity Services team

Updated 06/14/2010

Test Drive BCS

lionelro — Wed, 10 Feb 2010 01:36:00 GMT

Now that we have shown you the basics on how to:

Create a model using SharePoint Designer
Create an external list using SharePoint Designer or the Browser
Take the external list into Outlook and/or into SharePoint Workspace
Create associations

Go ahead and take BCS out for a test drive! To do this you can download a preloaded 2010 Information Worker Demonstration Virtual Machine (Beta) from MSDN. By downloading machine “A” of the two will get you access to a preconfigured environment with:

Customer from CRM External Content Type mapped to Contact Office native type
Customer from CRM External List
Windows Server 2008 SP2 Standard Edition x64, running as an Active Directory Domain Controller for the “CONTOSO.COM” domain with DNS and WINS
Microsoft SQL Server 2008 SP1 + CU2 Enterprise Edition with Analysis, Notification, and Reporting Services
Microsoft Office Communication Server 2007 R2
Visual Studio 2010 Beta 2 Ultimate Edition
Microsoft SharePoint Server 2010 Enterprise Edition Beta 2
Microsoft Office Web Applications Beta 2
FAST Search for SharePoint 2010 Beta 2
Microsoft Project Server 2010 Beta 2
Microsoft Office 2010 Beta 2
Microsoft Office Communicator 2007 R2

Once you have it up and running go to the http://intranet.contoso.com/sites/SP2010AllUp to try out the Customers from CRM external list, take it offline into Outlook or SharePoint Workspace, or even create an external content type of your own off of the Customers or AdventureWorksDW database using SharePoint Designer. Be sure to take a look at the performance indications on the MSDN download page for an optimal experience.

- Lionel Robinson

Using SharePoint workflows with Business Connectivity Services (BCS) – Sandboxed Workflow Actions

lionelro — Sat, 30 Jan 2010 01:26:00 GMT

In the last post, we covered how to use the out of box SharePoint Workflow activities to work with an External List. In that post, I mentioned that every time you read a column, it will execute both the Read List method (Finder) and the Read Item method (Specific Finder), and that could cause unnecessary calls to your external system. One of the ways to decrease that load on the external system is to get the item once, and store it in a way that you can reuse that data. In this post, we will show how you can implement such a solution using the SPList Object Model to read from the external lists in a Sandboxed Workflow Action. Sandboxed Workflow Actions are code functions that can be used in a declarative workflow designer and called from a declarative workflow. They are written in Visual Studio and uploaded to SharePoint’s Solution Gallery. These actions run in isolation to ensure that the core system process is protected and boundaries respected.

Tips on Action Usage and Common Mistakes

Use sandboxed workflow actions for External Lists if:

You do not have permission to deploy full trust activities and want any of the following:

Read many values from an external list
Write a simple function that will work on regular lists as well as external lists

Things to keep in mind:

Sandboxed actions can cause your workflow to fail if it fails to obtain a sandbox worker process application domain. There are an administrator configured fixed number of these application domains, and they are shared with all sandboxed code (actions, web parts, event receivers, etc.). Because of this, be cautious of using sandboxed actions in the following situations:

Complex sandboxed code actions that take time to complete
Slow external lists
Environment where many workflows will run at roughly the same time
Scenarios that cannot handle workflow failures

Sandbox code service can run these calls with a different account than the application pool account, so you will need to ensure that this account has been set up in the Secure Store Service (SSS).
If you are storing a local copy of the data you have read from an external list item, like I do in the sample below, you will need to ensure that your data is fresh.

Building the Scenario- Sandboxed Workflow Action

To expand upon the previous scenario where we only have one safe limit for all expense submissions, in this scenario we will add Safe Limits for certain budget categories. For instance, I may have different safe limits for travel expenses than I do for equipment expenses. When an employee submits the expense report, the workflow will validate the category and its expense limit to determine what action needs to be taken.

Create the Sandboxed Workflow Action

I’ve attached a Visual Studio project to this post that implements the actions we will be talking about. At a high level, we want to be able to read an item once and then read other properties out of that one transaction to the external system. We also want this action to show up in SharePoint Designer (SPD) so that more users will be able to write workflows against our list. Because SPD does not support looping, the action can’t return an array of items. So instead, we will create an action to read an item and store all of its properties in an xml string. Then we’ll create another action to read a specific field value out of this xml into an SPD variable. There are optimizations you can do to this, like only store the values you know you are going to read, but this example is implemented to be the most generic and reusable way to return properties for an item with any set of properties. Let’s dive into the details!

To read values from the external list we have the function with the following signature:

public static Hashtable GetExternalListItemByField(SPUserCodeWorkflowContext context, String externalListId, string fieldName, string fieldValue)

This function takes an external list id and then finds an item in that list by finding the first item that has “fieldValue” for a field named “fieldName.” Once the item is found, it puts each field into a Data Table and saves it as XML so that we can parse it later. This function is pretty generic and could even work on regular list items as well. This will help us improve our performance because it will only call the finder and the specific finder once and get all of the properties; however, we will still call the finder. This can be optimized so that you only call the finder twice, but that is an exercise we can explore later.

Once we have read the item, we have the function with the following signature:

public static Hashtable GetItemDataFromString(SPUserCodeWorkflowContext context, string itemData, string value)

This function takes the XML string with all the item properties in “itemData”, reconstructs the Data Table, and looks up the field specified by the “value” parameter. This does involve reloading the Data Table on every call of the function, but can be much faster than going to the external system for every call. It also allows the SPD user to pick which columns they want to look at instead of hardcoding it into the function itself.

The other file of interest in the project is the elements.xml file in the BCSWorkflowActivities directory. This elements file is what tells SPD how the sandboxed action should be presented in SPD as well as how the user can map the properties. The details of this file are out of scope for this post, but it’s has the same basic structure as the ACTIONS xml SPD used in 2007.

And now, to play with the sample!

Setup Instructions

To use the attached sample, you will need to do the following

Unzip the attached file
Open in Visual Studio 2010
Rebuild the solution
Deploy the solution

If you are deploying to a separate machine, you can use the WSP created from step 3 and do the following:

Go to Site Settings
Under Galleries, go to Solutions
Upload the WSP and activate it

Building the Workflow

In SPD, create an External Content Type called SafeLimit

It should have seven fields: EmployeeID, EquipmentLimit, EquipmentApprover, MoraleLimit, MoralApprover, TravelLimit, TravelApprover

Create an external list which the SafeLimit External Content Type called SafeLimits
Create a Document Library for expense reports called ExpenseReports

Add two additional columns for EmployeeID and ExpenseType
ExpenseType should be a choice field with the following choices: Morale, Equipment, Travel

In SPD, go to the ExpenseReports document library.
Click on New List Workflow
Give the workflow a name and description and continue

Note: At this point, you are in the workflow designer, and you start to add your actions

Insert the “Get External List Item by Field (Sandboxed Function)”

Pick your SafeLimits external list for the first parameter
Pick the EmployeeID column using the CurrentItem by using the button for the second parameter
Type in “EmployeeID” for the third parameter
You can leave the output parameter the same(Defaults to ItemString)

Now to read a column of data from that item you stored in ItemString, insert the “Read External List Item Data (Sandboxed Function)”

For the first parameter, Type in a field you want to see. For this case, we will use the value stored in the choice field ExpenseType and append “Limit” to it
For the second parameter, choose the ItemString variable
The last parameter will be the value that is found, call it “limit”

Repeat step 8, expect append “Approver” to the ExpenseType Value and change the output to “approver”

You should end up with something that looks like this:

That’s it on Sandboxed Workflow actions and external lists from me. In the next blog post, I will explain how you can build the same scenario using full trust activities that call the BDC APIs. By calling the BDC APIs directly, we will have more control over exactly what calls are made to the external system as well being able to operate on that data without needed an external list.

- JD Klaka

BCS Team Channel: Creating an External Content Type in SharePoint Designer 2010

lionelro — Fri, 29 Jan 2010 00:50:00 GMT

The BCS Team Channel has a new video in which we show you how in just a few minutes you can create an External Content Type using SharePoint Designer. In this video we use the database connector so be sure to subscribe to the channel to be the first to view the follow up videos which demonstrate how to work with other connectors such as Web Services and .Net Assemblies.

If you are new to the channel you can watch other videos such as:

- Lionel Robinson, Program Manager

How can I get rid of the “Publisher cannot be verified” message when taking external lists offline?

lionelro — Fri, 22 Jan 2010 19:29:29 GMT

When a user takes an external list offline to either SharePoint Workspace or Outlook, by default the user gets a dialog box like this one. To take the external list offline successfully, the user has to click on the Install button, which means that he or she trusts the package, even if the publisher of the package cannot be verified.

Essentially, when a user takes an External List offline, SharePoint generates a VSTO ClickOnce package for a client side solution. Every VSTO package should be signed by an Authenticode certificate to specify who published this package. The user installs the package on his or her client through the VSTO Installer and goes through the trusting process to verify if the package was published by a trusted publisher. For more information please see Publish Office Solution for VSTO ClickOnce packages, Granting Trust to Office Solutions for granting trust to general office solutions, and Trusted Application Deployment for granting trust to general ClickOnce Applications. By default, SharePoint does not have a certificate that can be used to sign the package. So, SharePoint generates a self-signed certificate and signs the package with it. Since this is a new self-signed certificate, the client has trusted neither this certificate nor its issuer before and hence it cannot verify the publisher of the package. That is why VSTO installer prompts the user with the message that the publisher cannot be verified. To get rid of the unfriendly prompt, an Authenticode certificate that was issued by a trusted Certification Authority must be provided to SharePoint for signing the VSTO ClickOnce package. In this post, I will show you how to provide such certificate to SharePoint. We are going to need administration privileges on each of the Web Front Ends (WFEs) of the farm.

First, we need an Authenticode certificate that is issued by a trusted Certification Authority, e.g. VeriSign. For details on how to obtain a certificate for signing, see ClickOnce and Authenticode. The certificate MUST contain a private cryptographic key so that it can be used for signing. For this demonstration, we generated two certificates and saved them as ContosoRoot.cer and ContosoBCS.pfx by using MakeCert.exe. The certificate contained in ContosoBCS.pfx has a private key and will be used to sign the VSTO ClickOnce package. The certificate in ContosoRoot.cer serves as the certificate of a Certification Authority and was used to sign the certificate contained in ContosoBCS.pfx. Note that the certificate in ContosoRoot.cer does not have a private key.

In the following steps, we are going to import those two certificates to the certificate stores on both WFEs and clients. Please note that you should never import a certificate to a production machine unless you trust the certificate.

Second, we need to create a certificate store named BusinessConnectivityServices for Local Computer on each of the WFEs of the farm. The BusinessConnectivityServices certificate store for Local Computer is where SharePoint looks for the certificate used to sign a VSTO ClickOnce package. You can create the certificate store by adding the key “BusinessConnectivityServices” under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SystemCertificates. Alternatively, you can copy and paste the following text to a notepad, save it as a .reg file, and then double click the file to create the registry key.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\SystemCertificates\BusinessConnectivityServices]

Third, we need to import the Authenticode certificate, e.g. ContosoBCS.pfx, to the certificate stores for Local Computer on all the WFEs, which can be done through Microsoft Management Console (MMC). On a WFE open the Start menu, click on Run…, and type mmc. This will launch MMC. Add a certificate snap-in for the Local Computer to the MMC. The details on how to add a certificate snap-in can be found in the MSDN article How to: View Certificates with the MMC Snap-in. Make sure that the certificate snap-in is for Local Computer, not for Current User. This can be verified by checking if the entry under Console Root in MMC is Certificates (Local Computer). If the certificate snap-in is for Current User, the entry is Certificates – Current User. In MMC, under Console Root\Certificates (Local Computer), there is an entry BusinessConnectivityServices, which was created when we added the registry key in last step. Right click the entry, then from the pop-up menu, point to All tasks and then click on Import. This will launch the wizard to import certificate. Follow the wizard and import the ContosoBCS.pfx to the store BusinessConnectivityServices. If the certificate of the Certification Authority is not trusted by a WFE, import the certificate of the Certification Authority, e.g. ContosoRoot.cer, to the store Trusted Root Certification Authorities in the same way.

Fourth, on a client machine where a user will take an external list offline to, if the Certification Authority that issues the certificate for signing is not in the Trusted Root Certification Authorities certificate store for current user yet, the user needs to import the certificate for the Certification Authority, e.g. ContosoRoot.cer, to the certificate store Trusted Root Certification Authorities for current user on the client machine. Note that the certificate only needs to contain a public key. Actually, you should not import the certificate for the Certification Authority with a private key to a client machine. Also note that in an organization that has PKI infrastructure in place, the Certification Authority’s certificate may have already been deployed to the Trusted Root Certification Authorities certificate store of the client machines. If this is the case, the fourth step can be skipped. Since in this example we generated the ContosoRoot.cer, our client does not have the certificate in the store and we should import the certificate. Again, we can import this certificate through MMC, as shown in the following figure. Note that at this time, the MMC has a certificate snap-in for Current User.

Now, if the Authenticode certificate used to sign the VSTO ClickOnce package has been imported to the Trusted Publishers certificate store for current user on the client machine, the package will be installed without prompting. Otherwise, when a user takes an external list offline, he or she will see the following friendly prompt with the publisher name.

If the external list has been taken offline before you imported the Authenticode certificate to the WFEs, you may have to remove the old package from the server so that a package signed with the new certificate will be generated. You can do this through SharePoint Designer. For example, if you want to remove the old package for external list “Product List” on site http://contoso, you can open http://contoso in SharePoint Designer, click on All Files under Site Objects in the Navigation pane, and an All Files tab will show up. Click on Lists in the All Files tab then Product List, you should see a folder named ClientSolution. Remove this folder by selecting the folder name and then clicking on the Delete button in the ribbon. From this point on, if a user takes this list offline, he or she will not see the unfriendly prompt shown at the beginning of this post.

Note that, if any of the following is true, taking an external list offline may fail.

The certificate imported into the certificate store BusinessConnectivityServices for Local Machine on a WFE does not contain a private key;
There is more than one certificate in the store BusinessConnectivityServices.

To fix those issues, you have to clean up the certificate store and import a certificate with a private key.

After the certificate is imported, if you get the error “Failed to publish Solution <external list name> because keyset does not exist”, please make sure that the certificate imported to the certificate store LOCAL_MACHINE\BusinessConnectivityServices contains a private key. If the certificate contains a private key, but you still get the error, the security account for the application pool used for the hosting Web application for the SharePoint site may not have access to the private key of the certificate imported from ContosoBCS.pfx. You can examine who has access to the private key by running the following command on WFEs, where ContosoBCS is the subject of certificate:

winhttpcertcfg.exe -l -c LOCAL_MACHINE\BusinessConnectivityServices –s ContosoBCS

This command will show a list of accounts and groups with access to the private key. If neither the local group WSS_WPG nor the security account for the application pool is in the list, the security account for the application pool does not have access to the private key.

To fix this issue, run the following command:

winhttpcertcfg.exe -g -c LOCAL_MACHINE\BusinessConnectivityServices –s ContosoBCS –a <application pool account>

For more information about winhttpcertcfg.exe please see WinHttpCertCfg.exe, a Certificate Configuration Tool.

- Bin Zhang

Updated 02/10/2010

Microsoft Business Connectivity Services Team Blog

BCS Artifact Generator

Creating a Declarative Solution for Outlook

What are the required artifacts?

Using the Artifact Generator

Steps to use the Artifact Generator

Data Solution

BCS Solutions in Outlook

Intermediate Declarative Solution

Advanced Code-Based Solution

BCS Solution Tools

Further Reading

How do I optimize backend synchronization?

How does the client cache work?

Working with Subscriptions

Optimizing Subscriptions

Explicit Identities

Filtering data

Example

Recommended reading

Is that all?

How do I leverage Secure Store from within my .Net Connectivity Assembly?

No-code approach

Code-approach

Reading metadata model properties

Sample implementation of IContextProperty

Sample implementation for reading credentials

Summary

BCS Logging

BCS Team Channel: Secure Store Service

Why it’s important to define filters for a good picking experience

Searching External Data in SharePoint 2010 Using Business Connectivity Services

Overview

Introduction to System Filters

Configuring System Filters

SPD approach:

Hand Written Approach:

Accessing BLOB Data from External Systems Using Business Connectivity Services in SharePoint Server 2010

Overview of the white paper

BCS Solution Packaging Tool

Solution Packaging Tool

Types of Solutions

Command Line Interface

Sample Solution to Get Started

Authenticating to Your External System

Authentication Modes

Pass Through

Secure Store

Revert To Self

Filters for Secondary Authentication

Anonymous Authentication

Other Security Considerations

Recap

Working with BCS in Visual Studio 2010 series

Introducing the SharePoint SPIdentity Team Blog

Using SharePoint workflows with BCS – Full Trust Workflow Activities

Tips on Action Usage and Common Mistakes

Use Full Trust Workflow Activities for BCS if:

Things to keep in mind:

Building the Scenario- Full Trust Workflow activity

Create the Workflow Activity

Setup Instructions

Building the Workflow

Deploying BCS Simple Solutions

Security in ClickOnce Applications

User Prompting Based on Security Zones

Avoiding Deployment Failures due to Default Prompting Policy

Prerequisites for BCS Solution Deployment

Browser Support for BCS Solution Deployment

Summary

Making Web Services BCS Friendly – Part 2

Creator

Recommendations for the corresponding web service method

Creator View and Specific Finder View

Other notes

Updater

Recommendations for the corresponding web service method

Updater View and Specific Finder View

Other notes

Examples