by Martin Laukkanen | Mar 31, 2011 | Reporting, SQL
A long time ago (it seems) I blogged about reporting directly from the SharePoint workspace sites using SQL Reporting Services to get around the limitations with the reporting database refresh. Using XML provided a neat method for pulling data directly from all the out-of-the-box lists such as Risks and Issues, as well as other custom lists.
Unfortunately setup and maintenance was difficult requiring a little knowledge of web services as well as some XML syntax, but fortunately now with SQL Server 2008 R2 things have improved drastically!
What’s New: Now you can directly create ‘Microsoft SharePoint List’ data sources!
See the screenshot:
Better yet when you create a Data Set using the above data source the Query Designer is completely new:
Finally no more messing around with column names using XML and having to have specific views configured in your SharePoint sites.
That should make things much easier!
BTW. Lastly it’s definitely worth mentioning that the above also works with Report Builder 3.0!
by Martin Laukkanen | Feb 26, 2011 | Project 2010, SharePoint 2010, Troubleshooting
I came across an unexplained issue recently with an ‘old’ Project Server 2010 customer, in short what happened was many of the permissions across the Project workspace sites were lost. Unfortunately the loss was triggered by performing a Playbooks (2010 version) backup of the server configuration which was even more concerning, considering that is such a commonly used tool.
In short we needed a way to restore all of the workspace site permissions to what they should be, without having to manually action each site.
Some background:
As it turns out Project Server 2010 has (once again) drastically changed the way that workspace site permissions are managed, fortunately changed for the better as now the behaviour is something like this:
- On first publish or creation of the workspace all appropriate users are granted permissions based on their group memberships and if they are a member of the project team. (Just as it previously worked)
- Subsequent publish jobs trigger another ‘Workspace Create’ job which now only adds new permissions but does not remove any existing ones, such as when a new team member is added to the plan. (This is similar to 2007 SP2)
- Active Directory Sync jobs trigger a permissions sync which acts just like #2.
The big differences are the following:
- During the sync NO permissions are removed. Thus correcting the old situation where an AD sync during business hours could result temporary permission losses.
- Manually changed permissions at the SharePoint level are respected. For instance if you remove a particular user from a SharePoint site, Project Server will not add it back in either of the standard permissions sync jobs above.
The Solution: To Restore All Permissions
As you may have found for yourself due to the new behaviour if a user is deleted from the SharePoint site, re-publishing a project will no longer fix the permissions. As such a new solution was needed in my case where permissions were lost across all sites:
- Create a new Project Server group containing all users.
- Assign My Organisation Category to that group.
- Assign Permission ‘View Project Site’ to My Organisation category.
- Save the new group (and wait for queue jobs to complete).
- Once confirmed permissions working delete the group (and again wait for the queue).
This will force a full refresh of the workspace site permissions across all users and all sites. Due the the new method of handling permissions it will only add each user with a minimum of ‘Reader’ unless they have another group which applies like Project Manager or Administrator, once you complete step 5 those unnecessary Readers will be removed, leaving only the correct permissions.
HTH,
by Martin Laukkanen | Jan 21, 2011 | SharePoint 2010
Following on from my previous post about Project Server and PerformancePoint in a multi-tenanted environment, realised that I had not told the whole story!
I neglected to include this when I wrote the above, Excel Services is another critical part of Project Server (more so than PerformancePoint really), and although it does have some support for Multi-tenancy it is far from perfect.
In short, when you configure Excel Services (no partitioning options are available) it does appear to honour subscriptions in the configuration, ie you can use a tenanted Secure Store to create Excel Services Application IDs, however the catch is that it will only talk to the Secure Store in the Default proxy group!
This presents a problem, as you read above PerformancePoint also only talks to the Default Secure Store, however unlike Excel Services PerformancePoint can not talk to a tenanted Secure Store app in the default group so you are left with a catch 22. Either both Excel Services and PerformancePoint can work together using a NON-tenanted default secure store, or one or the other cannot be installed!
*sigh*
If this stuff were simple we wouldn’t be here would we?
by Martin Laukkanen | Jan 12, 2011 | Reporting, SharePoint 2010, Troubleshooting
Update 21/01/2011: Update below regarding Excel Services and Multi-tenancy!
One of the exciting new features of SharePoint 2010 is multi-tenancy, if you’re working in hosted or shared environments then it is no less than a must-have feature.
Unfortunately in the real world of software nothing is perfect at RTM, and this would have to be one of those cases!
In short neither Project Server or PerformancePoint appear to support multi-tenancy in SharePoint 2010, it would seem that the feature has yet to be fully implemented for either service application, however the situation is not as bad as you might fear.
Project Server in a multi-tenanted environment
Firstly although Project Server does not accept any subscription related parameters when provisioning a new instance using PowerShell or the Central Admin, it does appear to work in a tenanted environment. Basically the fact that any provisioned PWA site is a site collection of its own means that once you have provisioned your PWA instance you can use the following PowerShell command to associate your PWA site collection with your tenant subscription:
Set-SpSite $PWASiteUrl -SiteSubscription $subscription
More good news is that once provisioned a PWA instance is able to communicate with other service applications belonging to the same subscription. Most importantly: Secure Store Service. Without that Excel Services wouldn’t work!
All is not good though, especially if you like to use the full feature set of the 2010 product, read on..
PerformancePoint in a multi-tenanted environment
This is where the news gets bad, it would appear that PerformancePoint in 2010 does not support multi-tenancy at all, it actually doesn’t appear to respect tenant subscriptions and so as a result you might end up with errors like the following when attempting to run Dashboard Designer or configuring the service application unattended account;
w3wp.exe (0x04E4) 0x0ED8 Secure Store Service Secure Store 7557 Critical The Secure Store Service application Secure Store Service Proxy is not accessible. The full exception text is: Access is denied. adfdd2a3-b6e5-4d92-8c5e-5a44fd821969
w3wp.exe (0x04E4) 0x0ED8 Secure Store Service Secure Store d9ld Unexpected Unexpected exception from endpoint address
w3wp.exe (0x04E4) 0x0ED8 Secure Store Service Secure Store d9le Unexpected Logging unknown/unexpected client side exception: SecurityAccessDeniedException. This will cause this application server to be removed from the load balancer queue. Exception: System.ServiceModel.Security.SecurityAccessDeniedException: Access is denied
Some searching will lead you to a number of sources talking about the lack of a Master Key being created in the Secure Store which is most certainly the cause of the issue, unfortunately it appears that in the case where your Secure Store is created with tenancy enabled PerformancePoint is unable to see the Secure Store service application (and thus unable to retrieve the master key)!
Some deep reading led me to some TechNet articles detailing the requirements for PerformancePoint (sorry can’t find the link ATM), basically PPS requires that a Secure Store Service Application exists in the default proxy group, and as I’ve found that app also cannot be tenanted.
Fortunately this can also be worked around, maintaining a non-tenanted default Secure Store app dedicated to PPS does not as I see it introduce any security implications, in particular as you will have to provision a separate PPS service for each tenant in your farm (thus losing much of the benefits of multi-tenancy).
All I can say on this one is that I can’t wait for SP1 for PPS, maybe then we’ll even be able to name our databases and lose that BETA PowerShell command line syntax? :)
Update 21/01/2011:
See the following blog for information on how Excel Services impacts the above configurations!
by Martin Laukkanen | Jan 12, 2011 | SharePoint 2010, Troubleshooting
With a large customer going live on a fully multi-tenanted claims authenticated platform in the last month I’ve had the chance to really see the limits of these two new features in SharePoint 2010. This issue was one of the big impact problems that I’m hoping that I’ve now found the solution to, so with that in mind it’s definitely worth sharing.
Problem:
After a few days of working normally claims authentication stops working unexpectedly on any given server in the farm, the below errors are logged.
Event Logs
Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Event ID: 8306
Task Category: Claims Authentication
Level: Error
Description:
An exception occurred when trying to issue security token: The server was unable to process the request due to an internal error. For more information about the error, either turn on IncludeExceptionDetailInFaults (either from ServiceBehaviorAttribute or from the <serviceDebug> configuration behavior) on the server in order to send the exception information back to the client, or turn on tracing as per the Microsoft .NET Framework 3.0 SDK documentation and inspect the server trace logs..
Log Name: Application
Source: Microsoft-SharePoint Products-SharePoint Foundation
Event ID: 8306
Task Category: Claims Authentication
Level: Error
Description:
An exception occurred when trying to issue security token: The security token username and password could not be validated..
Log Name: Application
Source: Microsoft-Windows-User Profiles Service
Event ID: 1511
Task Category: None
Level: Error
Description:
Windows cannot find the local profile and is logging you on with a temporary profile. Changes you make to this profile will be lost when you log off.
ULS Logs:
01/04/2011 13:38:52.17 w3wp.exe (0x037C) 0x0660 SharePoint Server Shared Services olgq Exception System.Runtime.InteropServices
.COMException (0x800703FA): Illegal operation attempted on a registry key that has been marked for deletion. at System.DirectoryServices.DirectoryEntry.Bind(…
01/04/2011 13:38:52.17 w3wp.exe (0x0554) 0x0F30 SharePoint Foundation Claims Authentication 8306 Critical An exception occurred when trying to issue security token: The security token username and password could not be validated..
Cause:
The third the Event log error above I included as although it is one that you often see, it was the message that eventually led me to the what looks like the source of this issue, with that combined with the “registry key that has been marked for” message in the ULS I was lead to the following DCOM blog:
Resolution:
It seems that the Claims provider breaks when for some reason or other the App Pool account logs off unexpectedly, the solution (at least after 2 weeks with no reoccurrence) is as suggested in the above blog;
As a workaround it may be necessary to disable this feature which is the default behavior. The policy setting ‘Do not forcefully unload the user registry at user logoff’ counters the default behavior of Windows 2008. When enabled, Windows 2008 does not forcefully unload the registry and waits until no other processes are using the user registry before it unloads it.
The policy can be found in the group policy editor (gpedit.msc)
Computer Configuration->Administrative Templates->System-> UserProfiles
Do not forcefully unload the user registry at user logoff
Change the setting from “Not Configured” to “Enabled”, which disables the new User Profile Service feature.
‘DisableForceUnload’ is the value added to the registry
I’ll update this blog entry if the problem comes back.
