Installation Considerations for Reporting Services in SharePoint Integrated Mode

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!

Advertisements

Configuring Profile Import in SharePoint 2010 – A Way Around the Minefields

Author’s Note – July 17 2012. When I originally wrote this in early 2010 it was based on some (at the time) sketchy Technet documentation and experience. It was meant to be an easy to understand guide to setting up the User Profile Service. I want to point out that there is a much more comprehensive guide out there on the topic, the Rational Guide to the User Profile Service by Spencer Harbar. It’s the reference I use when I get into trouble, and if this article doesn’t do it for you, I recommend going there.

Profile synchronization has changed drastically in SharePoint 2010 compared to 2007. The 2010 profile synchronization uses the Forefront Identity Management services. There are a lot of good things about this, one of which is that it provides for bi-directional synchronization. User changes to their profile store can be synchronized back to their identity store (Active directory, etc). This of course can be tightly controlled, on a field by field basis. There’s a great post on how to do so here.

Of course, all of this added power is not without its cost in complexity. I had a great deal of trouble even getting profile imports to work at all with some of the pre-release builds, and the final release is still a little rough around the edges. There is a Technet document available here that details precisely how to configure profile imports. It’s completely accurate, but doesn’t necessarily answer all of your questions. This post is my attempt to help guide around the worst of the thorns, and get it working with Active Directory.

First, you’ll need to have a Profile application running. If you’ve done a migration, you already do. If you’ve run the setup wizard, and selected user profile application, you also already have one. Synchronization will however not be happening,even if you’ve done a migration. It will need to be configured.

If you don’t already have a Profile Service application,you’ll need to create one. You do that from the Manage Service Applications screen and choose the New button in the upper left. You’ll want to select “User Profile Service Application”.

New User Profile Service

There is a lot of configuration here, make sure that you scroll to the bottom and fill out all of the relevant options. You’ll be choosing databases for social tagging, a database for storing user profiles, a database for storing user tags, and the URL of your MySite Host. If you haven’t already created a MySite site collection, the configuration screen here will allow you to do so. Once you have successfully created the application, you may then proceed to starting the service. This of course is in a completely different are where the services on the server are controlled. Once there, start the User Profile Synchronization Service.

User Profile Synchronization Service

When you start most of the services, they either start immediately, or give you a configuration screen and start quickly thereafter. Clicking this one gives you the configuration screen where you’re prompted to associate the service with your application from above, and the credentials that the two Windows Service accounts will run with. However once completed, you’ll notice that the service is in a “starting state”. This is normally a bad thing with SharePoint, and indicates that something is hung. Not so in this case. It takes a very long time for this service to start. When it does, you should see the following two services started:

New Profile Import Services

DO NOT attempt to start these services manually, and that will confuse the system in a very big way. Just have patience, and all should be well. You should now be ready to go ahead and perform an import. However, there’s a very import step that likely needs to be performed. The service account that was specified above to run the synchronization service (the one that the two forefront services are running as) need to be granted the “replicating directory changes” permission. There is another Technet article on how to do this, so I won’t go into detail on how.

If you do not perform this step, your import will fail, and you’ll have very little idea as to why. The error message is far from clear. Below I’ll talk a little about how you can troubleshoot import issues.

The next step is to set up the import itself. To do this, open up or manage your Profile service application, and select “Configure Synchronization Connections”

Create New Profile Connection

Once here, you either edit your existing connection(s), or create a new one. The options for import source are greatly increased from 2007 and now include Active Directory, Active Directory Resource (ADAM), Active Directory Logon Data,  BDC (they’ve forgotten to rename it to BCS), IBM Tivoli, Novell eDirectory, or Sun Java System Directory Server. Another improvement in this version is that the import can now use Forms authentication credentials, but can also make use of Claims based authentication if available.

Give the connection a name, select an authentication type, and any appropriate credentials. Once you have done this, press the “Populate Containers” button. If everything is OK, you should see your import source appear below:

Profile Import Container Selection

This dialog is NOT particularly responsive, be prepared for long waits between mouse clicks. The beauty of it is that you can now very easily cherry pick which containers, and even users get imported very easily. This is not something that was straightforward in 2007. Once completed, select OK, and your connection should be created. Note – subsequent edits of the connection will not retain the credential information. This is normal.

At this point you are ready to perform your firs import. Return to the Profile Service Application main page, and in the Synchronization section, select “Start Profile Synchronization”. You then have the option to choose full or incremental sync. Once selected, synchronization runs, and you can keep track of its status on the main profile application screen:

Profile Synchronization Manager Client for SharePoint 2010

This screen shows no problems, but if there are, they will be displayed for each step in a great amount of detail. I’ve used it to troubleshoot a few connection issues already.

In conclusion, the new profile synchronization system in 2010 has quite a few more moving parts, is a little rough around the edges (at the moment), and can be a bit of a bear to get going. However, it’s new capabilities make it well worth the effort, and lay the groundwork for what I can see to be some great new features down the road.

Enabling Meaningful Errors in SharePoint 2010

Thanks to Rez’s blog spot for this one. Here’s how to get replace the “Unexpected Error Has Occurred” message with some ASP.NET debug code that you might be able to use to resolve whatever problem you’re having.

Edit the web.config of your SharePoint application – should be found in

drive:inetpubwwwrootwssVirtualDirectories<port>

Set the following values:

  1. Debug=”true” instead of the default of Debug=”false” (do a find on “debug”)
  2. CallStack=”true” instead of the default of CallStack=”false” (do a find on “callstack”)
  3. customErrors=”Off” instead of the default of customErrors=”On” (do a find on “customErrors”)

Finally, open the web.config file found at

drive:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14TemplateLAYOUTS

And change the value of customErrors to “Off”.

After an IISReset, you should see better errors.

Confusion When Upgrading from SharePoint 2007 to SharePoint 2010 In Place

When You install SharePoint 2010 on a server that already has SharePoint 2007 installed on it, it will install all of the 2010 bits on that server in a side-by-side fashion with the 2007 bits. This is a good thing, for all sorts of reasons.  However, it can be the source of some confusion, and at least once already in my case, an opportunity for slip ups.

After you run the new SharePoint Products Configuration Wizard (note the new lack of “And Technologies” in the name), you will have converted your farm, which is all of your databases. However, you still have access to all of the code from the 2007 install. This includes the SharePoint Products and Technologies Configuration wizard from the 2007 install.

This can be a real problem, particularly if you have always used a shortcut to run it, and you expect the shortcut to be upgraded. It won’t be… Take care that you’re running the right tool. It’s now installed off the start menu in the “Microsoft SharePoint 2010 Products” folder, and its name is slightly different,“SharePoint 2010 Products Configuration Wizard”. The chrome around it is slightly different,but not enough to tell, but one visual clue that you’ll get while inside the wizard is that the prompt that warns you about services restarting has “v4” in most of the Service names.

Required SQL Server Versions and Patch Levels for SharePoint 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…