Category: SQL Server

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

Installation Considerations for Reporting Services in SharePoint Integrated Mode

Published May 6, 2010

Ever Since SQL Server 2005 R2, it has been possible (with varying degrees of difficulty) to tightly integrate Reporting Services with SharePoint. What this means is effectively discarding the management and user interfaces that come with a Reporting Services native mode installation, and managing and use all reporting through the SharePoint interface.

This has really nice benefits for those managing the Reporting Services environment, mainly because you can simply leverage your SharePoint storage and security infrastructure instead of having to create and maintain another one simply for reports. Users benefit from a consistent user experience, and are easily able to create filtered reports through the Report Viewer web part.

Integrated mode is however not without its complications, and with the release this week of SQL Server 2008 R2 (which includes some very nice Reporting enhancements), I thought that I’d go over them. Here are some of the more important moving parts:

Reporting Services Add-In must be installed on EACH front end web server in the farm
Reporting Services database must be created in SharePoint integrated mode
Anonymous access must be disabled for the target application
The machine hosting Reporting services must be a member of the SharePoint farm

If everything that you’re running is on a single server, you really don’t need to read any further. In that scenario, everything is straightforward. But if you’re not on a demo system, or you have more than 3 users, chances are that your farm is distributed, and least broken out into one Web Front End (WFE) and one SQL Server.

As I’ve mentioned previously,The Reporting Services add in now installs as a prerequisite with SharePoint 2010,which makes the first point a no brainer. However, if you have SharePoint 2007 and multiple web front ends (your query servers count), you need to install the add in. Also if you add a new Front End server down the road, don’t forget the add in.

If you’re currently have a Reporting Services server running in native mode, and you’re looking to upgrade to SharePoint Integrated mode, don’t expect an enjoyable experience. You can’t upgrade your existing native mode database to integrated mode, you’ll need to export everything, create a new database in integrated mode, and move everything into it. You can however switch back and forth on the server side if you need to, but as you do, it’s all or nothing.

One thing that I wanted to do was to take an election results analysis demo that I’ve recently done with SQL Server 2008 R2 mapping and expose it to the world. Unfortunately, that’s not going to be an option because you can’t run the application in anonymous mode with the plug in. If you have this requirement, then Integrated Mode is not for you.

Finally, the big requirement is that the machine running Reporting Services must be a member of the SharePoint farm. Typically you only install the SharePoint bits on the WFEs and the Index servers, and the SQL Server itself is left alone. Basic guidance from Microsoft indicates that now you need to install the SharePoint bits on the SQL server and then join the farm. This may be daunting to those that have yet to “enjoy” the challenges of running a multiple WFE farm. It also might be a particular challenge if you don’t “own” the SQL server itself.

My preference (typically, not always) is to take the other approach, and install Reporting Sevices itself (none of the other services, database engine, etc) on one of the SharePoint front end servers. In our case, we have a 3 server farm (one front end, one indexer/query/central admin, and one SQL, and Reporting Services is on the Indexer/query/central admin box (I need a better name for it).

In this scenario, the Reporting Services databases themselves still live on the main SQL server, but the services and rendering all happen from the WFE machine. This makes for a very low touch solution in terms of your SQL server, and is much easier to maintain. This model also also allows you to get going with the new features available in SSRS R2 without having to first upgrade your central SQL server.

One caveat – with 2007, I always found that it was a requirement to also run the Central Administration application on the machine with Reporting Services. I don’t know if that was a true limitation, or something that I was missing. I haven’t yet tried it using R2 and 2010, but when I do, I’ll come back here and update this. Also – if you know something I don’t, please feel free to enlighten me!

5 Comments

Required SQL Server Versions and Patch Levels for SharePoint 2010

Published April 28, 2010

Microsoft has been very clear about the requirements for installing SharePoint 2010. The biggest thing in this release is that it’s 64 bit only, not just on the Operating System side, but also in the SQL Server requirements. In addition, it’s also quite fussy about which versions it supports.

On the operating system side, a complete list can be found here. On the SQL Server side, it’s generally thought that it supports only SQL Server 2008 and above. However, this isn’t true – it supports SQL Server 2005 – provided that it’s 64 bit mode. However, the devil is in the details. It’s very specific about the patch level that you’re running. Glenn Berry has a list of the supported versions, with patch levels, and the SQL script for determining your precise version levels. There are only 3, so I’ll repeat them below:

SQL Server 2005 SP3 CU3 (Build 4220) or greater
SQL Server 2008 SP1 CU2 (Build 2714) or greater
SQL Server 2008 R2

The kicker is that neither the SP3 level of 2005, nor the SP1 level of 2008 will cut it,and you will have a failure if you do not conform to these versions. This failure will not appear until you run the SharePoint Products configuration wizard,which is pretty much past the point of commitment. In addition, the stsam.exe –o preupgradecheck command on a 2007 farm does not appear to detect this deficiency (at least it hasn’t yet in my experience).

Do yourself a favour, and patch up your SQL servers before you start your install/upgrade. The most recent cumulative update packages, as of this writing are:

I’ve used CU7 for 2008 on my installs thus far, and can attest that it works. I’m still waiting, hopefully not long now, for 2008 R2…

Not Supported Error While Doing A Clean Install of SharePoint 2010 RTM

Published April 23, 2010

I ran into an odd error today while installing the RTM of SharePoint 2010 on a clean server farm. I had built everything up from scratch on current versions, including SQL Server. I had applied SP1 and run all Windows updates. When I got to the point of actually running the Products and Technologies Configuration Wizard, I received an error stating that my SQL Server was running an unsupported version of SQL, version 2007.100.2531.0. Yes, it was 64 bit, as was everything else. I thought that was rather odd, and thought I’d try to find something newer.

Cumulative Update 6 for SQL Server 2008 is available so I thought I’d try applying that. Lo and behold it worked, and I continue to install away.

I just hope that it’ll be just as happy with SQL Server 2008 R2….