How To Rename Your Reporting Services Databases

I've installed TFS in more than one environment where Reporting Services is already in use.  The big problem that everyone yells about is that TFS requires a blank RS instance in order to install it successfully.  In case you don't have a spare SQL database instance laying around, a nice workaround is to rename the RS databases from their default names.  This allows TFS to create the new RS databases it requires with the default and you can move on with your life.

Thanks to Bob Coppedge over at Simplex-IT for his original blog post on this.  I was able to follow his steps with some slight variations to rename the Reporting Services databases.

1. Backup the ReportServer and ReportServerTempDB databases you plan on renaming--just in case something goes wrong...which never happens, of course.
2. Stop Reporting Services using the Reporting Services Configuration Tool.
3. Script the stored procedures from the ReportServer database to new query window.
4. In the script, search for and replace CREATE PROC with ALTER PROC (one instance contains two spaces between CREATE and PROC.)
5. Save the script.
6. Rename the ReportServer and ReportServerTempDB databases.
7. Go back to the stored procedure script and search for and replace ReportServer with the new database name (ex. ReportServer to ReportServer-NEW.)
8. Save a copy of the script with a new name.
9. Execute the script.
10. Start Reporting Services using the Reporting Services Configuration Tool.
11. Go to the Database Setup "tab".
12. Click the Connect button and then click OK on the pop-up.
13. Select the new database name in the dropdown list.
14. Click Apply and then click OK on the pop-up.  An error will probably occur when saving the new connection info.  Click Apply again and it should succeed.

Finished!  You should be able to test the reports now.  I would go so far as to recommend this as a best practice.  You could leverage the same database server for multiple RS instances this way.  At the very least, it enables you to install Team Foundation Server against a database server that is already supporting an existing RS instance.

jb

Issues with Reporting Services Permissions

After working with TFS security for the better part of a year, I suddenly ran into a nasty permissions issue in Reporting Services this week.  I'll illustrate the problem using pictures.  When I look at security at the top level in Reporting Services using my tfsSetup account, I see that only the TFS Administrators group has the Content Manager role.  That's good because I don't necessarily want everyone looking at the project reports across the board.

Now I'll drill down into the folder for my team project.  In this case, the team project name is JobBoard.  Here I've broken the security inheritance and given my jobBoardProjectAdmin account the Content Manager role.  The expectation is that he or she will be able to manage security for that folder and any of the contained items without having to bug me.  I'd like to fully delegate that function to the JobBoard Team Project Administrator.

With that set up, let's see if the jobBoardProjectAdmin account has access to view the reports as well as administer security for the JobBoard folder.

Looks like I can see the list of reports but then I click the Properties tab...uh oh!  That doesn't look right.  Reporting Services gives me an error stating "The permissions granted to user 'VSTS2008\jobBoardProjectAdmin' are insufficient for performing this operation. (rsAccessDenied)".  I just gave that account the Content Manager role!  I'll check one of the reports.  Maybe that will work.

Same error.  I can view the report just fine, but I can't edit the permissions.  Argh!

So I do some Googling and really don't find much except for folks talking about giving the user some sort of access at a higher level.  I'd rather not have to do that but if I'm going, I'd prefer to do it once and get it over with.  I'll try giving all Authenticated Users the Browser role at the top level.  This may seem to fly in the face of my earlier statement about not wanting everyone to see all of the projects reports.  However, since I broke the security inheritance for the JobBoard folder, the Authenticated Users group won't have the Browser role at that level.  Only the users explicitly given permissions to the folder will be able to see it.  I'll use that policy going forward so the Authenticated Users approach is okay.

Now I'll I try the Properties tab for the JobBoard folder using the jobBoardProjectAdmin account.  The result:  I can see it AND manage permissions.

Success!...kind of.  Why does this work?  Why does this fix the problem?  Why do I have to do this?  Is this the way it's supposed to work?  I really don't know, but I'll have to live with it for now.  I'm working with SQL Server 2008 here.  The problem can be seen in SQL Server 2005, as well.  If anyone has feedback or ideas about this, feel free to respond.  If you're looking for a solution to the problem in your own environment, the Authenticated Users should work just fine.  You could always use a more specific domain group if you like.

On a side note, during this process I discovered a nice little feature in Visual Studio Team System that I hadn't seen before.  In the Security and Group Membership dialogs that are part of Team Explorer there are these links for SQL Server Reporting Services and Windows SharePoint Services Site Administration at the bottom.  I never paid attention to those until now.  They actually take you straight to the appropriate locations for administering security for those resources.

Interestingly enough, the Reporting Services link takes to you to the Properties tab of the top-level folder rather than the folder for the team project.  Strange...

jb

The Dreaded System Health Check

Just behind the "progress-bar-almost-at-the-end-when-an-error-message-pops-up-and-the-whole-damn-thing-rolls-back" scenario, one of the most dreaded aspects of an installation of Team Foundation Server is the System Health Check: another progress bar followed by The Red X of Doom, The Yellow Triangle of Worry, or—if you’re lucky—The Green Check of Bliss. In case you’re wondering just what exactly the System Health Check is checking, there’s a nice little XML file in the AT folder on the installation media called hcpackage.xml. This file contains all of the warning messages along with the associated WMI queries. The queries include checks for processor speed, available disk space, and the version of SQL Server you are using, among other things. They even tag the issues with codes for the applicable scenarios such as a single-server or dual-server or even a build machine. It's pretty enlightening so give it a look if having trouble with that System Health Check.

hcpackage.xml

jb

New Visual Studio 2010 CTP

Microsoft just released a new CTP of Rosario, now officially known as Visual Studio Team System 2010. It comes in 11 (!) parts so you'll either want to use Firefox or Free Download Manager or some other utility to get it. Enjoy!

UPDATE: Jeff Beehler (same initials!) has posted a great rundown of the new walkthroughs in this CTP.

jb

Not @ PDC 2008?

If you're like me, you're probably not at Microsoft's Professional Developers Conference (PDC2008) in LA right now. Fear not! There are options for virtual attendance! Free! Just in the last 24 hours I've seen tons of content out there available to the masses.

Check out the MicrosoftVSTS feed on Twitter for some official tweets including quotes from some of the panel discussions. It's like you're there!

A group called NotAtPDC has started up with a variety content mediums including a website, a Twitter feed, and a Facebook group. Thanks to them, I learned about the new .NET logo! Nice!

jb

TF30063: You are not authorized to access...

Ever try to delete a team project and get the above message? Are you in the Team Foundation Administrators group and scratching your head? I thought TFS admins could do everything! So I finally decided to get a clear understanding of the root cause of this problem.

The resolution is simple: add your account to the site collection administrators for the SharePoint project portal (described here). You can also do this as a Farm Administrator under the Application Management section in Central Administration. Hopefully, as a TFS admin, you've been added to the Farm Administrators group in SharePoint. The nice thing about the Farm Admins group is that it supports AD groups. The site collection administrators, however, does not. From Central Administration, you can specify a primary and secondary site collection administrator. Within the settings for the project portal site, you specify additional users, but you cannot specify groups.

Why can't Farm Administrators admin site collections by default? That's just the way it is. TechNet has a great article describing how SharePoint security works. The key content is as follows:

"Members of the Farm Administrators group have no administrative access to individual sites or their content by default. However, they can take control of a specific site collection to view any content. For example, if a site collection administrator leaves the organization and a new administrator must be added, farm administrators can add themselves as site collection administrators, which action is recorded in the audit logs."

So there it is. In short, the MSDN entry on TFSDeleteProject should include a prerequisite step to make sure you have site collection administrator permissions before running the command.

jb

New MSBuild Extension Pack

Brian Harry posted today about a new MSBuild Extension Pack project on CodePlex. I figured this would be a good opportunity to demonstrate how you can use it to version the assemblies in your builds. Here are the steps for setting up assembly versioning for your builds using the MSBuild Extension Pack.

1. Download and install the MSBuild Extension Pack on your build machine(s).
2. Create a base target file with the following XML:

<?xml version="1.0" encoding="utf-8"?>
<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003" >
<Import Project="$(MSBuildExtensionsPath)\ExtensionPack\MSBuild.ExtensionPack.tasks" />

<!-- ASSEMBLY VERSIONING -->
<PropertyGroup>
<AssemblyMajor>1</AssemblyMajor>
<AssemblyMinor>0</AssemblyMinor>
</PropertyGroup>

<Target Name="GenerateAssemblyVersion">
<!-- Get a version number based on the elapsed days since a given date -->
<!--TfsVersion TaskAction="GetVersion" BuildName="$(BuildDefinition)" TfsBuildNumber="$(BuildNumber)" VersionFormat="Elapsed" StartDate="17 Nov 1976" PaddingCount="4" PaddingDigit="1" Major="$(AssemblyMajor)" Minor="$(AssemblyMinor)">
<Output TaskParameter="Version" PropertyName="AssemblyVersion" />
</TfsVersion-->
<!-- Get a version number based on the format of a given datetime -->
<TfsVersion TaskAction="GetVersion" BuildName="$(BuildDefinition)" TfsBuildNumber="$(BuildNumber)" VersionFormat="DateTime" DateFormat="MMdd" PaddingCount="5" PaddingDigit="1" Major="$(AssemblyMajor)" Minor="$(AssemblyMinor)">
<Output TaskParameter="Version" PropertyName="AssemblyVersion" />
</TfsVersion>
<Message Text="New Version is $(AssemblyVersion)" />
</Target>

<Target Name="VersionAssemblies">
<!-- Run the CreateItem task to populate the group after the source code has been downloaded. -->
<CreateItem Include="$(SolutionRoot)\**\AssemblyInfo.cs">
<Output TaskParameter="Include" ItemName="FilesToVersion" />
</CreateItem>
<!-- Set the version in a collection of files -->
<TfsVersion TaskAction="SetVersion" SetAssemblyVersion="true" Files="%(FilesToVersion.Identity)" Version="$(AssemblyVersion)" />
</Target>
</Project>

3. Save the file and copy it out to the MSBuild directory under Program Files on your build machine(s).

The GenerateAssemblyVersion target shows a couple of ways to use the GetVersion action of the TfsVersion task to generate different assembly version formats. The task has several attributes that allow you to customize the format to fit your needs. Check out the help file included with the MSBuild Extension Pack installation for more details.

To actually use this functionality in your builds, just add the following XML to your TFSBuild.proj files.

<Import Project="$(MSBuildExtensionsPath)\My.TeamFoundation.Build.targets" />

<PropertyGroup>
<AssemblyMajor>3</AssemblyMajor>
<AssemblyMinor>5</AssemblyMinor>
</PropertyGroup>

<Target Name="AfterGet">
<CallTarget Targets="GenerateAssemblyVersion;VersionAssemblies" />
</Target>

Here, you override the base values of the AssemblyMajor and AssemblyMinor properties from your base target file. In addition, you're overriding the built-in AfterGet target that is part of Team Build to call the custom targets in your base target file. This keeps the amount of duplicated MSBuild script very low.

UPDATE: Mike Fourie has a great post on some additional things you should consider when setting up assembly versioning in your builds. Of note is the separation of the AssemblyVersion and AssemblyFileVersion attributes when working with strong-named assemblies.

jb