Cannot access SQL Server 2000 Report Services published report from other than local admin

Posted on 2004-11-04
Medium Priority
Last Modified: 2007-12-19
I have development system (XP Pro-SP2) with SQL Server 2000 (SP3).  Just installed Visual Studio 2003 and MS Reporting Services (+SP1).  Currently, I'm running both the development and the deployment environment on this system for initial test.  I developed reports in Visual Studio 2003 and successfully published them to Report Services.  I can access reports in report server via //localhost/reportserver and also via report manager as I'm signed on as the local admin.  Works great.  When I try to access IIS on this system from another machine I get a request for a userid/password that I can't get around (I know that IIS access works for other pages...so its the report services page requesting the credential).  In the documentation it states that report services only has the local admin as an authorized user.  How do I create an end-user userid/password with just report browsing permissions and assign the user specific reports to access?  (I just want a basic end-user to connect via the browser, put in a userid/password to authorize their access...then be able to run a report).  P.S.  Also created a limited user on my development machine and tried to access //localhost/reportserver...was denied access for what I think is the same permissions problem (which I think is the ability for report server to access the reportserver database on SQL Server 2000).  (I've read the manuals until I'm cross-eyed..I'm just missing it...so I've assigned some extra points for someone to say "just DO the following 100 steps....lol".  Thanks!)
Question by:webdork
LVL 32

Expert Comment

by:Brendt Hess
ID: 12499487
To start:

Reporting Services uses Internet Information Services (IIS) and Windows security to authenticate users to a report server.  ****Each user who requires access to a report server must have a valid Windows user account or be a member of a Windows group account****.  You can include accounts from other domains as long as those domains are trusted. The accounts must have access to the Web server hosting the report server, and must be subsequently assigned to roles in order to gain access to specific report server operations.

Since it is Role-based, you must configure the users or groups with Roles:


If a person is truely remote (not on the Windows network) then that user needs to be able to provide an authorized local login through that login box you were seeing to access the reports.

More needed?

Accepted Solution

cubical10 earned 900 total points
ID: 12505714
If all of you are using a domain, then easy solution is to go here (assuming you used default setup):
http://<server name>/Reports/
Then click on the 'Properties' tab.  You should see 1 group in the list, BUILTIN\Administrators Content Manager.
Click on new Role Assignment and the 'Group or user name' text field enter "<domain name>\Domain Users" and check the Browser role type.

That will allow all of your domain users to access your reports.  You can then get fancy and limit access to reports based on domain groups.

Hope this helps...

Author Comment

ID: 12509939
Thanks...I'm making progress.  I now can properly set up Roles in my Report Server (I'm not a Windows or SQL Server guru...most of my work is with a large DB vendor (not Oracle) and the term "role" had a slightly different meaning to me).  I can now grant or revoke report privledges from a limited user on my test system (Earlier, I had set up a limited user on my test system and could not get to any reports...now this works great).  I still have the problem that I cannot access the report manager via the web....I get a userid/password box.  When I type in the userid and password of the limited user (or even my own admin id), I cannot get connected (I ultimately get an HTTP 401.3 - Access Denied by ACL on Resource Internet Information Services).  I know that I am getting to my IIS server because I can access other pages that don't require any special security.  Thanks to the other helpful person about mentioning domains....however, my test system where I'm running IIS/report services is not on a domain, only a workgroup.  I've given permissions for the limited user to read the SQL Server database controlling report services (and, as mentioned, the whole thing works on the local system when signing on as the limited user)....so I'm now a little stumped about what security I'm tripping over.  (If this could be a hint...I read somewhere that "...if you're still having problems, make sure you DO NOT have anonymous access to reportmanager or reports....".  Could this be something and, if so, how do I reset this if applicable?).  Thanks again for great assistance.

Expert Comment

by:Pedro Keson
ID: 24372451
Hi cubical10,
accidentaly, I removed the builtin administrators group from the reports manager so now I can not edit the reports nor roles at there is probably no other user with enough rights to do it.
IS THERE A WAY TO add a new user with the highest role permissions or somehow reset the default builtin administrators role for the reporting server? Thanks

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question