I had a crazy, frustrating time getting my SQL Server 2008 Express Edition with Advanced Services installation to let me access Reporting Services. I followed a lot of different blog posts and suggestions that included tweaks to trusted sites, adjusting virtual directory permissions (which isn’t even applicable because the virtual directories don’t appear in IIS) and changing permissions on the database. While these some of this is necessary, they weren’t all needed and they weren’t the whole solution.
No matter what I did, this is what I saw when I went to Report Manager. Notice that “Site Settings” is not an option:
And this is what I saw on the Report Server:
There were two main problems:
1. Under the Windows 7 security model, Administrator access is not sent to applications automatically. Out of the box, Reporting Services gives access only to “BuiltIn\Administrators”, and a user just logging into IE will never appear to be part of “BuiltIn\Administrators” unless they “run as administrator.”
2. IE8 is set by default not to pass Windows credentials through to a website. So Report Manager prompts for credentials. But when a user types in those credentials, we have the same problem – the “BuiltIn\Administrators” roles is not passed through.
So to solve it we need to accomplish two things:
1. Give your account rights to Report Manager and the folders based on its identity or membership in a role other than “BuiltIn\Administrators”
2. Set IE to pass credentials through to the site
Below are the detailed steps:
1. Launch IE as Administrator
2. Go to Tools –> Internet Options and lower the Trusted Sites security level to Low (or Custom with the setting for User Authentication/Logon set to “Automatic logon with current name and password”. Add your computer as a trusted site (mine is named “work”, hence http://work in the screenshot):
3. Now browse to the Report Manager site. It should not ask you for credentials and should now have a lot more options
4. Click on Site Settings and click on the Security tab. You’ll see that only BUILTIN\Administrators have access
5. Click on New Role Assignment and give your ID access as an administrator. This can be done by directly granting rights to your account or to a role it belongs to that is not an administrator account.
6. Go back to the home page of Report Manager and click on Folder Settings
7. Give your account or group the rights you need on this folder. At a minimum you need Browser to see it in Report Manager and Publisher to see it in Report Server.
8. Now you can close IE, launch it normally, and have full rights. Report Server may not be pretty, but the error is gone.