Yesterday, as part of the ongoing Panellist Spotlight series for SharePoint Shoptalk, I presented a session on how to move your data from SharePoint, and in to a data warehouse, and then consume the warehoused data directly within SharePoint. These presentations are recorded, and posted online, and if you’re interested, you can view the entire presentation below:
In it, I demonstrate how SQL Server Integration Services (SSIS) can be used to extract the data from SharePoint, and to store it in a SQL Server data warehouse. Then I walk through the creation of an external content type, and an external list using SharePoint Designer and SharePoint Business Connectivity Services (BCS). Finally, I create a report using SQL Server Reporting Services (SSRS) in SharePoint Integrated mode.
I did my first Search Server Express (SSE) 2010 installation the other day. I expected to see a new Service Application for search available, but what I did not expect to see were a few other services, in particular, the Secure Store Service.
Why do I care about this? Well, as I wrote about previously, the BCS relies on this very heavily when running in a multi server environment without Kerberos. (As an aside, Kerberos, or Cerberus is the three headed dog that guards the gates of Hades. Aptly named I think.). SharePoint Foundation 2010 does not come with this service, which pretty severely limits the value of BCS for these environments.
SSE is a free add-on to SharePoint Foundation 2010, and therefore, suddenly, there is hope! Foundation users CAN use BCS,they just need to install SSE first,and as a nice bonus, they’ll get a much stronger search engine.
When SharePoint 2010 was publicly revealed at the SharePoint conference in October 2009, one of the biggest “wow” announcement was the fact that Business Connectivity Services (BCS) would now be included with SharePoint Foundation (the free version of SharePoint). The feature set that BCS not only replaced, but significantly enhanced was know as Business Data Connectivity (BDC), and in the 2007 version, it was only available with the Enterprise SKU. From Enterprise to Free? It seemed too good to be true. Unfortunately in a few cases, it is.
BCS is surfaced to users primarily as External Lists, which essentially makes back end data look to SharePoint users (and some services) like simple SharePoint list data. An external list uses external content types, which are created using SharePoint Designer 2010.
The first step in creating an external content type is creating a data connection. The data connection can connect to one of three back end sources; a .NET Type, a SQL Server connection, or a WCF Service. A .NET Type can behave however the developer wants it to, but both the SQL server connection and the WCF type run into the same issue very quickly – identity.
These connection don’t use the typical connection strings that most people are used to with Excel or .NET. You specify a server and a database, but your identity options are limited to the 3 choices shown below:
The default option “Connect with User’s Identity” will use the identity of the user at run time to connect to the source data. However,if the source data is on a different server than the one hosting SharePoint,we run immediately into the “double hop” problem. Essentially the server can’t just forward the user’s credentials on to another server at run time. If your organization is using Kerberos, this isn’t a problem for you (which is good, because you probably have many others…), but if not, then your only option is impersonation.
Impersonation is essentially telling the server to use a specific set of credentials (some proxy account) whenever it connects to the back end systems. Typical data connection strings that embed a user ID and password are an example of this. Storing credentials directly in strings is a huge security risk, and SharePoint 2010 has a very good secure store service that will manage these proxy accounts in a highly secure manner. BCS data sources are designed to take advantage of this service, and selecting either of the two impersonation options above will prompt for the Secure Store Application ID to use. Great solution right?
That means that if you’re not using Kerberos, and your data is on a different server, your BCS options are very limited when using SharePoint Foundation. You’re either going to have to write a .NET data type, or pony up the $$ for a SharePoint standard licence if you want to use the BCS features.
I will mention one quick sort of “low rent” approach that may circumvent the need for BCS. The data view web part has been a nice way to show related data or external data in SharePoint going back to SharePoint 2003. What many don’t know is that it’s actually bidirectional, which means that you can do the standard CrUD operations.
It’s actually very easy to use. Using SharePoint designer, first move to the Data Sources node and create a new data source. These sources will allow standard connection strings, so impersonation becomes possible (if less secure – make sure that you use least privilege accounts!). Then create a new web part page. Move to the Insert tab and select the drop down tab below the Data View button, and select your data source.
You will immediately be presented with a read only grid of the data. The ribbon should now also be displaying the Data View Tools tab group, with the Options tab selected. From that tab select the inline editing drop down, and choose the CrUD options that you want used.
There are many options around formatting, what columns are displayed, paging etc. that you can play with, but at this point you’re ready to go.