TIP: Configure SQL Server Reporting Service 2008

Key words: SSRS 2008, Reporting Service, Report Manager Blank, rsAccessDenied

Most of you who work on Microsoft .NET platform may use SQL Server, as well as one of its components Reporting Service. Some of you may suffer for SSRS configuration as sometimes it's really tricky to make it server for everyone. It's lucky that I experienced this issue again today and really struggle against the configuration for a well.

Symptom: I have to install a new SQL Server 2008 on a Windows Server 2008 R2 machine, and SSRS is included in the installation. After the installation process completed successfully, I found that I can't get any content from http://localhost/Reports or http://localhost/ReportServer. Report Manager gives me nothing except a standard header of report manager site, no Site Settings link at all. Report Server gives me an error that access denied for the current user.

1). Report Manager site works well but no content displayed. That means the current user doesn't have permission to access Reporting Service. The problem is when I install SQL Server, Administrators was added as a Role for Reporting Service, but the current user Allan is a member of Administrators. You are still able to browse Report Manger site but no content, that's security issue. But how can I change the security settings without Site Settings menu?

2). Report Server throws me an access denied(rsAccessDenied) error, that's still security issue, how can I assign permissions to the current user?

Problem:

User Allan is a member of Administrators group and was used to install SQL Server. Even Allan was assigned access to both SQL Server engine and Reporting Service, but it is still not allowed to access Reporting Service, we need to find a way to assign the permission to the current user.

Solution:

When you install SQL Server Reporting Service and initialize SSRS, it will automatically generate encryption key pair used to protect sensitive data. Normally it generates the encryption key associated with Administrator user, when you don't use Administrator as the login name, then the encryption key should be recreated in order to allow the current user access Reporting Service without any other permission settings.

Let's have a look on the definition of Encryption Key: An important part of report server configuration is creating a backup copy of the symmetric key used for encrypting sensitive information. A backup copy of the key is required for many routine operations, and enables you to reuse an existing report server database in a new installation.

Reporting Services uses encryption keys to secure credentials and connection information that is stored in a report server database. In Reporting Services, encryption keys include a combination of public, private, and symmetric keys that are used to protect sensitive data. The symmetric key is created during report server initialization when you install or configure the report server, and it is used by the report server to encrypt sensitive data that is stored in the report server. Public and private keys are created by the operating system, and they are used to protect the symmetric key. A public and private key pair is created for each report server instance that stores sensitive data in a report server database.

These key file .snk. will have to be used during your disaster recovery, any config. changes in your domain level and for any other changes that RS depends on its environmental factors.

Then how do we fix it? We understand the concepts then the fix is extremely easy:

1). Delete Encryption Content.

2) Start Internet Explorer and Run as Administrator, type http://localhost/Reports. Then you are able to see Site Settings at the top right of the page.

3) Click Site Settings -> Security. And click New Role Assignment to add the account/group to system level access list.

4) Or click Home -> Properties -> Security to add the users/groups to site level access list with different roles.

Hope this helpsJ