Configuring a new SQL Reporting Services server

I am now getting into my stride on upgrading to SQL 2008. We have a reasonably compact environment here with only a handful of production servers. We use mirroring and replication in places and we have a couple of report servers to keep the data flowing to the users. I have just moved a database that has a gang of reports pointing to it and have been going through the directory settings for security and so forth and got to the point where I was repeatedly selecting an object (data sources or folders mostly) and going to the properties and selecting "hidden" and applying the change. That got tedious really quickly so I moved over to SSMS and started reviewing the Catalog table in the ReportServer database. 

Now lets get things clear from the start, mucking about in system generated objects on a working server can easily result in a big mess around your ankles very quickly so only go here if you are totally happy that you can undo the changes you have made if you get something wrong.

Right, still with me? Great. First of all we need to get to the database and find some information about the objects we want to work with.

USE [ReportServer]
go

SELECT top 20 

    [c].[Path] ,
    [c].[Name] ,
    [c].[Type] ,
    [c].[Description] ,
    [c].[Hidden]
FROM    [dbo].[Catalog] AS c

so this shows we have a column called hidden that we can switch to a value of 1 to hide the objects we want and objects have a type value. Now some research on my 2005 server tells me that there are 6 types that I need to deal with and they match up to objects as:

--    1-directory
--    2-report
--    3-other file
--    4-linkedreport
--    5-data source
--    6-reportbuilder object

So, in my case, I want to hide all images and text files as they only appear in reports, noone goes to a report server to view a picture or a text file and can use

UPDATE  [dbo].[Catalog]
SET     [Hidden] = 1
WHERE   [Hidden] != 1
        AND [Type] = 3 -- images

Now, I also want to hide any directories called "Data Sources" as I dont want these in the way of users navigating the report directories and I can do that with

UPDATE  [dbo].[Catalog]
SET     [Hidden] = 1
WHERE   [Hidden] != 1
        AND ( [Type] = 1 -- directories
              AND [dbo].[Catalog].[Name] LIKE 'Data%Sources' -- directories with specified name(s)
            ) 

Now I had to use a LIKE and add a % in the name as some of our projects got created with a typo in the deployment properties and the names are either "DataSources" or "Data Sources"; you may not need this or you may need to add other code to include all the variations you have.

So there you have it, the whole report server deployment gets configured pretty much instantly. Just remember to run this once in a while as other projects come online so that nothing sneaks past you. Maybe even slip the code into your scheduled weekly maintenance job(s).


[Edit - 27/Mar/2010]
I am adding the following section after receiving some messages (via http://twitter.com/fatherjack) that this technique is inappropriate.


For clarity that may be missing from the above, this is not a method to apply any form of security over a report server and its contents, this is simply to hide items that otherwise clog up the view of a user browsing the report server. There is, to my knowledge, no option in setting permissions on a report server object that allows the user to still access the item but not be able to see it. I apply the hidden property on items that the user inherently HAS permission to consume - images may be used in reports they have full access to, the data sources are obviously needed for the reports to run properly. There is no need for the user to have to navigate past these items, so I hide them.
Permissions are used to control access to data and strongly tie in with Active Directory users and groups and SQL Server Logins and Users, this ensures that no person has access to a report that contains data they are not authorised to view.


Please be sure to read the code disclaimer page before you use any code in this blog.

PowerShell in use - moving SQL Mail Profiles

I am in the middle of a SQL Server 2008 installation and today I needed to transfer the Mail Accounts and Profiles to the new instance in order to be ready for use by the incoming system. I never been a fan of the GUI to configure Mail on SQL 2005 as it involves a lot of back and forth clicking to get everything done.

I decided to take a look at what PowerShell would let me do. After getting connected to the out-going server it was simply a case of changing to the mail directory and then the accounts directory and using the power of the script method of the objects to get the scripts into a file on my local hard drive.

set-Location SQLServer:\mail\accounts
get-ChildItem | foreach {$_.script()}| out-File C:\SQLServer_MailAccounts.sql

then I just moved to the profiles directory and repeated the process

set-Location SQLServer:\mail\profiles
get-ChildItem | foreach {$_.script()}| out-File C:\SQLServer_MailProfiles.sql

Once that was done, I opened the file sin SSMS, connected to the new server and ran them to create the mail accounts first and then the profiles.

Really simple, really quick. I am beginning to see ways that PowerShell will continue to help me

Please be sure to read the code disclaimer page before you use any code in this blog.

How to slipstream SQL Server 2008 RTM and SQL Server 2008 SP1

I have a number of SQL Servers to upgrade from 2005 to 2008 this year and as service pack 1 is already out it makes sense to install this at the same time as the main software. In Microsoft parlance this is known as 'slipstreaming'.

In theory it involves getting the set up files for SQL Server 2008 and applying the service pack to them rather than to the installed software. Its covered on an MSDN blog here http://blogs.msdn.com/petersad/archive/2009/02/25/sql-server-2008-creating-a-merged-slisptream-drop.aspx and these are the instructions that I followed. However there are a couple of pointers that I have had to work through so this could be considered a supplemental blog for the slipstream creation process. The step numbers used relate to those in Peter Saddow's blog.

Step 1: I got iso files downloaded. These need to be mounted (Virtual Clone Drive) or extracted to disc and then the contents copied to the directory as described. Note: I could not get PowerShell to do the copy from my VCD drive - it errored saying the drive was not the right format. Windows drag and drop does it anyway.
Step 2: No problems (apart from time taken to download!)
Step 3: Execute each statement listed one at a time and leave it a few minutes before you start the next one. You will see an extracting progressbar for each package.
Step 4: This is simply moving them up a directory level and overwriting the files of the same name already there. Robocopy is a tool installed as part of a server admin resource kit. You will need to install that in order to copy the command given otherwise follow the instructions exactly.
Step 5: Robocopy again but simple
Step 6: Easy enough to edit 3 .ini files and add a line.
Step 7: Install SQL Server 2008 and note the slipstream identifiers as you go through the installation process.

Hope this helps.

Other useful links
SQL Server 2008 Slipstream FAQ : http://blogs.msdn.com/petersad/archive/2009/04/24/sql-server-2008-slipstream-frequently-asked-questions.aspx
CSS SQL Server Engineers How to fix your SQL Server 2008 Setup before you run setup... Part 1
CSS SQL Server Engineers How to fix your SQL Server 2008 Setup before you run setup... Part 2

Please be sure to read the code disclaimer page before you use any code in this blog.