SharePoint Foundation – Page 2

Connection Limitations using BCS With SharePoint Foundation and a Workaround

Published August 26, 2010

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?

The problem is, SharePoint Foundation doesn’t include the Secure Store Service.

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.

Finally, if you want a good comparison of what features are included in which editions of SharePoint, you’ll find it here

UPDATE – Sept 23 2010

As I’ve just learned, Search Server Express 2010 (SSE) comes with the Secure Store Service. SSE 2010 is a free add on to SharePoint Foundation. So if you’re in this situation, go grab it and install it. That will solve the problem.

3 Comments

Reporting Services Error After Creating a SharePoint Site

Published August 23, 2010

I ran into a nasty little problem at a client site this morning. I had just finished setting up Reporting Services on a SharePoint Foundation front end server and everything was working just fine. The client had asked for a central site to store reports by default, so I of course created a new blank subsite called Reports (creative, right?). However, immediately after the site was created, I got this error:

“This operation is not supported on a report server that is configured to run in SharePoint integrated mode. (rsOperationNotSupportedSharePointMode)”

Huh?

That’s a reporting Services error – why am I getting this from SharePoint? Of course I wasn’t. When I had configured Reporting Services, I had also configured the Virtual Directories for the Reporting Services web services, and for the Report Manager, which is unused in Integrated mode. I allowed the default values for both to be used, which are ReportServer and Reports respectively.

That was of course my problem http://servername/Reports was already a valid URL so instead of going to my newly created site, it tried to access the Reporting Services URL, which doesn’t work in integrated mode.

Fixing this was a little bit tricky. The first part was easy, create a site that isn’t named either Reports or ReportServer,and set it up. The tricky part was deleting a site that I couldn’t navigate to. It would be easy with Server,but this was Foundation. SharePoint designer did the trick nicely. With a site open, you can manipulate sites one level down, and I was able to delete the offending site.

The moral of the story? Be aware of existing virtual directory names when creating subsites on the same server.

2 Comments

SQL Express, Microsoft Internal Database, and SharePoint – Be Careful Upgrading

Published August 16, 2010

Anyone that has worked with SharePoint for some time will tell you that setting it up using the Basic or default install is a big no no. Doing so uses only local accounts, prevents you from adding any additional servers to the farm, and because the SharePoint system account is local, it effectively prevents SharePoint from seeing any data on any other systems. Reversing this requires a complete uninstall of SharePoint. It is much better to do a proper farm install, even if the databases are to be stored on the same machine.

There is however one business case where the basic install has historically been the right approach, and it’s very specific. To explain, a little explanation of some different flavours of SQL is required. Most people are familiar with SQL Server Standard Edition and Enterprise Edition. Also available for download is a free edition of SQL Server entitled SQL Server Express, which has a few limitations, most notably a single database size limit of 4 GB (and has none of the BI components). There is (or was) also a lesser known version of SQL Server known as the Windows Internal Database. It can’t be downloaded, but occasionally will show up on a system when a Microsoft product that requires SQL Server is installed into an environment without an available SQL Server.

What’s the difference between the Windows Internal Database (WID) and SQL Express? SQL Express is a little easier to manage (and possible to uninstall, unlike WID). WID is relatively invisible, but the major difference between the two is the size restriction. Microsoft will often change the maximum DB size limit for the WID in the interest of the product that it is backing. This is very much true of SharePoint 2007. Given SharePoint’s focus on storing large content,Microsoft didn’t want people to bump into that 4 GB size restriction too quickly,so for WSS 3.0 (the free version of SharePoint 2007), they removed the limit completely.

This means that if you use WSS 3.0, and you install using the basic configuration option, you have no licensing limits on the amount of content that you can store within SharePoint. It is very important to note that this is ONLY true of WSS 3.0, if you install SharePoint Server 2007 in basic mode, once you hit 4 GB everything stops working. Also, if you decide that maybe you should do a best practice farm install, and you install SQL Express separately from SharePoint, you will run into that 4 GB wall with either WSS or SharePoint server.

This then is the one business case where it is appropriate to use the basic installation. If the feature set of WSS 3.0 is adequate, and you will want to store more than 4 GB in a single site collection, basic installation is for you.

Thus far I’ve been addressing WSS 3.0 and SharePoint Server 2007. With SharePoint Foundation 2010, and SharePoint Server 2010, Microsoft made some significant changes to the model. Now, they exclusively rely on SQL Express for the basic installation. While this is great for anyone that’s ever wanted to uninstall an instance of the Windows Internal Database, Express has that 4 GB limit for both editions, which essentially means that they’ve closed the door on the free storage ride.

This also means that if you are in this category of organizations using more than 4 GB of SharePoint storage, you’re going to need to spring for a SQL Server license if you want to upgrade to SharePoint Foundation 2010. You will also need to do an uninstall, and a reinstall to get yourself into a farm configuration. It’s OK, it’s not as scary as it sounds.

The SharePoint 2010 changes mean that there is now no good reason to use basic installation in a production environment. Development or testing, OK, but why not use farm in that scenario as well? It more closely approximates the real world.

UPDATE – 24/08/201

I was listening to Todd Klindt’s Admin Netcast #53 today, and he was addressing some of the above issues. He brought up two very good related points. Firstly, in SQL Express 2008 R2, the storage limit has been increased to 10 GB, so that might help you out. Also, you can use the SQL FileStream Remote Blob storage to help get the size of your content database down.

3 Comments