Create External Content Type Using SP Designer 2010 with Remote Database

With the release of SharePoint 2010 came many significant improvements to the SharePoint platform. Among them was the release of SharePoint Designer 2010. After working with the new release of SharePoint Designer I have been *very* impressed with the improvements that have been made.

It wasn't until today that I was problem free with SharePoint Designer. It started off with beginning to work more closely with External Content Types using the Secure Store Service. Due to the amount of time spent trying to come up with a solution alone, was reason enough for me to write something about it.

Scenario: Connect to remote SharePoint site collection in SharePoint Designer 2010 to create an External Content Type from a SQL database that isn't on the same server as SharePoint.

Problem: Because I'm using NTLM authentication I am unable to connect to the database through SharePoint using Pass Through Authentication. The limitation with NTLM is that it doesn't support multiple server hops.

Procedure: First step is to create a Secure Store Application for my connection to the remote MS SQL database. The reason for this is it acts as a bypass to the multiple server hop limitation with NTLM by authenticating against the Secure Store Service using my NTLM credentials to retrieve the credentials needed to authenticate against SQL server and then having SharePoint use those credentials and authenticating against SQL server.

Because SQL server is on a different domain than my local box I decided to use SQL authentication not Windows authentication to login against SQL server. I created a SQL account that I could use for the external content type and mapped that user to my SharePoint account in the Secure Store Application.

Everything was setup to work properly...the problem was when I tried to connect to the database I was getting back an error message:

I then looked in the Secure Store Audit table in the SQL database and noticed that nothing was being logged for the requests from Designer. My next step was to close down SharePoint Designer due to a possible issue with caching. After reopening I selected the Impersonate Custom Identity option and entered the Secure Store Application ID which would return the windows identity credentials I had stored in the application. When I was prompted with a Windows login, I entered the SQL DB user credentials which worked!! When I opened the connection properties I noticed that the Secure Store Application ID wasn't what I had first entered...wierd. The secure store application ID was what I had configured to return the SQL server credentials. Bottom line...try and try again.

So, looks like there are still some kinks with SharePoint Designer 2010. If I discover any further oddities I'll be sure to update this blog.

Recap from Houston SharePoint User Group

First I wanted to thank everyone for coming out to the user group last night in Houston. It was great to meet many of you all and I thoroughly enjoyed my time. Discussing integration of SharePoint with an enterprise infrastructure can be quite daunting but I feel like the message got across based on some questions during and after the presentation. I would appreciate any constructive feedback related to my presentation by commenting below.

As a side note, one of the attendees asked a very good question around authorization of multiple SharePoint lists from different SharePoint farms. I wanted to highlight this at the beginning and provide a more complete answer. Because of the nature of mashing SharePoint lists in a different environment such as Presto the security context in SharePoint no longer applies.

Therefore we have security built around the SharePoint lists registered in mashup server which then carry over to mashups created using the SharePoint lists. In doing such the creator of the mashup can setup custom authorization to that SharePoint list. I didn't want the audience to think that we were compromising security in order to simplify access to external SharePoint data. I hope this is clear.

In brief there are three challenges organizations will have to face related to integration when deploying SharePoint as part of their information infrastructure. We have titled them as the "Inside-Inside", "Outside-Inside" and "Inside-Outside" problems.

"Inside-Inside" Problem
With the exponential growth of data within a SharePoint deployment comes the question of how to manage the data as well as consolidating the data in a rapid, yet concise fashion. One example that an individual asked me about last night was being able to bring a contact list from one SharePoint site over to another SharePoint site and allowing users to update the data in the new location. This is a classic example of the types of issues many organizations are facing today. At JackBe we like to use the phrase "Rapid Intelligence"; being able to harvest data, quickly, within SharePoint in order to help the end user make fast, intelligent decisions. Out of the box it is very difficult and tedious to manage SharePoint data stored in lists across multiple site collections. At JackBe we have developed a product that can quickly and easily "mash" SharePoint lists in a manner that can help minimize the SharePoint crawl problem.

"Outside-In" Problem
Within organizations is the challenge of collecting data external to SharePoint and brining it into SharePoint with as little development cost as possible. SharePoint 2007 and 2010 answers this type of problem using the "Composites" piece of the SharePoint pie. More specifically the Business Data Catalog (BDC) in Moss 2007 and Business Connectivity Services (BCS) in SharePoint Foundation 4.0 and SharePoint Server 2010. In my demonstration list night I showed the audience how it is possible to gather data external to SharePoint such as a MS SQL table or a WCF web service and display that data within a SharePoint list using External Content Types; and do this without any code, all within SharePoint designer. I also pointed out that while the SharePoint Designer is capable of such tasks it is still quite limited in the types of LOB systems it can connect to. We are currently working on expanding the ability for the end user to be able to connect to most of the popular data sources and bring them into SharePoint as External Content Types without any custom development involved.

"Inside-Out" Problem
Once data begins to grow within SharePoint, organizations are also looking for ways to pull this data into other external portals or data centers. Currently the only way to accomplish such a task is to custom develop a SOAP client that is able to connect to SharePoint's web services and pull the list data out. At JackBe we have already included this capability within Presto by allowing users to register SharePoint lists and then Mash them together and publish them as Apps to just about any type of portal using simple scripting or even to other SharePoint farms.

SharePoint Designer and Workflows...beware

It seems that no matter how many times I'm disappointed using SharePoint Designer 2007 I still find myself using it. The ability to quickly browse content on my SharePoint site, copy content into my Visual Studio project and quickly create Workflows that are attached to lists...**halt**... That's what I thought about 2 weeks ago until it came time to migrate the site I was currently working to another web application. Little did I know that workflows, created in SharePoint Designer, are assigned to lists using the lists's GUID (which makes perfect sense).

Okay, enough of the background. If you're like me and you get into something where you end up getting stuck (classic tar-baby) you won't give up without a fight. Well, fight hard I did. It turns out that it IS possible to reattach the workflow to its list after exporting your SharePoint site to a new site collection...the answer in a nutshell... make a call to SharePoint's web part service (duh...obviously...not).

Here's what I did, in a nutshell.

1) Create a feature event receiver to attach the workflow to the new list.
2) Open the workflow configuration files (usually stored in the "Workflows" list in your site) and replace all the list GUIDs from the old site with the GUIDs from the new site.
3) Call the SharePoint web part service to reassociate the workflow with the list. Here is a code sample for this step.

WebPartPagesWebService service = new WebPartPagesWebService();
service.Url = String.Format("{0}/{1}", SPWeb.Url, "_vti_bin/webpartpages.asmx");
service.UseDefaultCredentials = true;
service.ValidateWorkflowMarkupAndCreateSupportObjects(, null, , "2");
service.AssociateWorkflowMarkup(cfgFile.Url, "V" + cfgFile.UIVersionLabel);

**Important thing to note is the service will not associate the workflow with the list unless the version number of the config. file is greater than what is there currently. In other words you need to make sure the version of the workflow configuration file increases before calling the service to associate the workflow with the list.