blogger counters

Tuesday, March 10, 2009

SharePoint Access Denied on SPWebApplication

I've been working on a SharePoint solution that needed to write properties to the current web application. Everything worked just fine on my development machine. But on some of our test machines, I found the following message in the SharePoint logs:

The SPPersistedObject, SPWebApplication Name=Default Web Site Parent=SPWebService, could not be updated because the current user is not a Farm Administrator.

System.Security.SecurityException: Access denied.

This turns out to be correct behavior, but it took a while to understand what was going on and how to setup my development machine to reproduce the problem.

In my code to set the web application property value, I was using RunWithElevatedPrivileges to ensure I had rights. However, what I didn't realize is that elevating privileges gives you full access to the content database, but not to the configuration database. SPWebApplication's property bag is stored in the configuration database, not the content database. That was one piece to the puzzle.

The other piece to the puzzle was how I had my development machine setup. I was using the same account on both the Central Administration application pool and the application pool used to run regular the web applications. So in essence, I was implicitly granting my code rights to write to the configuration database.

When I modified the application pools to use two different accounts, I was able to reproduce this problem on my development machine.

Here is a summary:

  • RunWithElevatedPrivileges only provides full access to the content database for the current web application. In other words, you can modify read-only lists, write SPWeb properties, etc. But you can't make changes to the SPWebApplication because it is persisted to the configuration database.
  • Always make sure your application pools for normal web applications are using a different account than the Central Administration application pool. This is recommended practice for production machines, but you should also setup your development and test machines this way to catch problems early.

Thursday, March 05, 2009

TFS Report Issues with SQL Server 2008

Some of the reports that shipped with Team Foundation Server 2005 and 2008 run into problems when you attempt to run them on SQL Server 2008 Reporting Services. Here is an overview of of the problems and fixes for those problems. Most of these problems are a result of changes in how Analysis Services processes queries in 2008 vs 2005.

Complement and Except Errors

Several of the TFS reports show one of these errors when running under SQL 2008:

The set must have a single hierarchy to be used with the complement operator.

Two sets specified in the EXCEPT function have different dimensionality.

The reports that are affected are Scenario Details and Unplanned Work.

What does this mean? Basically, it means that the WHERE clause in the query is using the "-" operator to exclude a specific attribute from the query. For example, the Scenario Details reports includes the following inside the WHERE clause:

,STRTOSET( @AreaParam )

,STRTOSET( @IterationParam )

,STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]")

,-{STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")}


The minus sign in front of the last line indicates that we want to filter out work items that are in the Proposed state. However, this doesn't work in SQL 2008. You'll notice that we're trying to exclude an a specific value without mentioning the other values.

The solution is to remove the exclusion from the WHERE clause and put it into the body of the query:

,STRTOSET( @AreaParam )

,STRTOSET( @IterationParam )


    Descendants([Work Item].[System_State].[System_State])

    ,STRTOMEMBER("[Work Item].[System_State].[System_State].[+@Proposed+]")



The key here is that we need to include all states except for the Proposed state, which is what the Except function above does.

WHERE vs Sub Cube

Many of the reports were written using the WHERE clause to filter the results. For example, so the results would be filtered on a single team project instead of the entire cube. However, the behavior of the WHERE clause changed somewhat in SQL Server Analysis Services 2008. The simplest solution is to rewrite the WHERE clause as a sub-cube expression instead. Not only will this fix some of the problems, but it should also make reports run faster.

Old Version:

FROM [Team System]



  STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

  STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")


New Version:





      STRTOMEMBER("[Team Project].[Team Project].[" + @Project + "]"),

      STRTOMEMBER("[Work Item].[System_WorkItemType].[" + @Bug + "]")


  FROM [Team System]



The WHERE clause above is applied after the rest of the MDX expression has been evaluated. But the second expression defines a subset of the cube that will be used to evaluate the rest of the expression. In other words, using a sub cube, you're restricting the set of cells to a smaller set before evaluating the query.

Extract Problems

Here is the error message you might see:

The Extract function was called with hierarchy 'System_Id' that does not exist in the set.

That's certainly not a very helpful error message. This message appears in the report called Bugs Found Without Corresponding Tests. The solution, as it turns out, is to use a sub-cube instead of a WHERE clause (described above).

Tuple Problem

In several of the reports, selecting all of the iterations produces the following error message:

The function expects a tuple expression for the argument. A tuple set expression was used.

More than 4,294,967,296 tuples

This error occurs with the following reports:

  • Bugs by Priority
  • Bugs Found Without Corresponding Tests
  • Issues and Blocked Work Items
  • Related Work Items
  • Requirement Details
  • Requirements Test History and Overview
  • Scenario Details
  • Triage
  • Unplanned Work

The solution to this problem is also to replace the WHERE clause at the end of the MDX query with a sub-cube expression, as described above.

SQL Reports and the IN Clause

The Load Test Summary report is a SQL report, and it contains an IN clause in the WHERE statement. This is used to allow multiple values to be selected and passed to the query. However, a change in behavior in SQL 2008 Reporting Services means that you'll see the following message when you have no values:

Incorrect syntax near ')'.

Here is why you see this message. The WHERE clause in the Load Test Summary report looks like this:

WHERE (Result.__ID IN (@ResultParam))
AND ([Test Result].[Result Record Count] = 1)

When there are no results passed into this query, Reporting Services 2008 simply removes @ResultParam, so you get something like "IN ()", and you get a syntax error, preventing this query from running.

The solution is to add an expression to the @ResultParam query parameter. This expression is evaluated in order to determine what is passed to the query. Here is what works:

=IIF(Parameters!ResultParam.Count > 0, Parameters!ResultParam.Value, "")