Uninstalling Reporting Server 2008 on Windows Server 2008

Piotr Rodak — Thu, 23 Dec 2010 00:03:18 GMT

Ha. I had quite disputable pleasure of installing and reinstalling and reinstalling and reinstalling – I think about 5 times before it worked – Reporting Server 2008 on Windows Server with the same year number in name.

During my struggle I came across an error which seems to be not quite unfamiliar to some more unfortunate developers and admins who happen to uninstall SSRS 2008 from the server. I had the SSRS 2008 installed as named instance, SQL2008. I wanted to uninstall the server and install it to default instance. And this is when it bit me – not the first time and not the last that day . The setup complained that it couldn’t access a DLL:

Error message:

TITLE: Microsoft SQL Server 2008 Setup

------------------------------

The following error has occurred:

Access to the path 'C:\Windows\SysWOW64\perf-ReportServer$SQL2008-rsctr.dll' is denied.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.0.1600.22&EvtType=0x60797DC7%25400x84E8D3C0

------------------------------

BUTTONS:

OK

This is a screenshot that shows the above error:

This issue seems to have a bit of literature dedicated to it and even seemingly a KB article http://support.microsoft.com/kb/956173 and a similar Connect item: http://connect.microsoft.com/SQLServer/feedback/details/363653/error-messages-when-upgrading-from-sql-2008-rc0-to-rtm

The article describes issue as following:

When you try to uninstall Microsoft SQL Server 2008 Reporting Services from the server, you may receive the following error message:

An error has occurred:
Access to the path 'Drive_Letter:\WINDOWS\system32\perf-ReportServer-rsctr.dll' is denied.

Note Drive_Letter refers to the disc drive into which the SQL Server installation media is inserted.

In my case, the Note was not true; the error pointed to a dll that was located in Windows folder on C:\, not where the installation media were.

Despite this difference I tried to identify any processes that might be keeping lock on the dll. I downloaded Sysinternals process explorer and ran it to find any processes I could stop. Unfortunately, there was no such process.

I tried to rerun the installation, but it failed at the same step.

Eventually I decided to remove the dll before the setup was executed. I changed name of the dll to be able to restore it in case of some issues. Interestingly, Windows let me do it, which means that indeed, it was not locked by any process.

I ran the setup and this time it uninstalled the instance without any problems:

To summarize my experience I should say – be very careful, don’t leave any leftovers after uninstallation – remove/rename any folders that are left after setup has finished. For some reason, setup doesn’t remove folders and certain files. Installation on Windows Server 2008 requires more attention than on Windows 2003 because of the changed security model, some actions can be executed only by administrator in elevated execution mode. In general, you have to get used to UAC and a bit different experience than with Windows Server 2003.

Technorati Tags: SQL Server 2008,Windows Server 2008,SRS,Reporting Services

Setting reporting database–what happens behind the scenes

Piotr Rodak — Sun, 28 Nov 2010 14:30:07 GMT

When you install reporting server, you have in general two options available – either you install the reporting server and configure its database at the same time, or install just reporting service and point it to an existing database later.

We had recently opportunity to configure server to point to existing reporting database. We encountered a security related issue, because the account we were using to connect to the SQL Server database engine was not ‘good enough’ to configure reporting service connectivity. Namely, the installation fails if you are not a sysadmin, security admin, or dbo on master and msdb databases.

So what is happening if you are configuring reporting server to point to existing database? This is how it seems to work in SQL Server 2008 R2:

First, new role called RSExecRole is added to master database if it doesn’t exist:

1 use master
2 go
3 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
4 BEGIN
5  EXEC sp_addrole 'RSExecRole'
6 END
7 
8 go

Then do the same in the msdb database:

1 use msdb
2 
3 go
4 
5 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
6 BEGIN
7  EXEC sp_addrole 'RSExecRole'
8 END
9 go

And in reporting database and the temp database for reporting:

 1 USE [ReportServer$SQL1]
 2 
 3 go
 4 
 5 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
 6 BEGIN
 7  EXEC sp_addrole 'RSExecRole'
 8 END
 9 
10 go
11 
12 USE [ReportServer$SQL1TempDB]
13 go
14 
15 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
16 BEGIN
17  EXEC sp_addrole 'RSExecRole'
18 END
19 
20 go
21

Next the reporting server configuration manager is mapping user to the login we want to use in each of the databases, interestingly in the order as below:

 1 
 2 USE [ReportServer$SQL1]
 3 
 4 go
 5 
 6 
 7 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
 8 BEGIN
 9     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
10     BEGIN
11         EXEC sp_grantdbaccess N'RSUser'
12     END
13 END
14 
15 go
16 
17 
18 USE [ReportServer$SQL1TempDB]
19 
20 go
21 
22 
23 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
24 BEGIN
25     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
26     BEGIN
27         EXEC sp_grantdbaccess N'RSUser'
28     END
29 END
30 
31 go
32 
33 
34 USE [msdb]
35 
36 go
37 
38 
39 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
40 BEGIN
41     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
42     BEGIN
43         EXEC sp_grantdbaccess N'RSUser'
44     END
45 END
46 
47 go
48 
49 USE [master]
50 go
51 
52 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
53 BEGIN
54     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
55     BEGIN
56         EXEC sp_grantdbaccess N'RSUser'
57     END
58 END
59 
60 GO
61

Eventually the user is added to the RSExecRole:

 1 
 2 USE [ReportServer$SQL1]
 3 go
 4 
 5 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
 6 BEGIN
 7     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
 8 END
 9 
10 go
11 
12 
13 USE [msdb]
14 go
15 
16 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
17 BEGIN
18     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
19 END
20 go
21 
22 USE [master]
23 go
24 
25 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
26 BEGIN
27     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
28 END
29 
30 go
31 
32 
33 USE [ReportServer$SQL1TempDB]
34 go
35 
36 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
37 BEGIN
38     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
39 END
40 
41

Ha. I was about to give a rant about implementation of sp_addrolemember system procedure, which in SQL 2005 is using sysusers and other obsolete objects. The version in SQL Server 2008 R2 has been rewritten from what I see, and is using sys.database_principals in its logic. Well done Microsoft .

Technorati Tags: SQL Server,t-sql,reporting services

Piotr Rodak : SSRS

Uninstalling Reporting Server 2008 on Windows Server 2008

Setting reporting database–what happens behind the scenes