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

Posted on 2004-11-04
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 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 I've assigned some extra points for someone to say "just DO the following 100".  Thanks!)
Question by:webdork
    LVL 32

    Expert Comment

    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?
    LVL 1

    Accepted Solution

    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

    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 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) 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

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now