I want to be able to install Microsoft SQL Server 2005 Reporting Services on two servers.
I have a three server setup without any domain server. A public frontend server, a backend server and a database server behind a firewall (not public). On the database server I have installed Microsoft SQL Server 2005 SP2 + Reporting Services. When using http://localhost/reports
on the database server everything works fine. I can view and change the reports with sufficient rights.
WHAT I WANNA DO:
To be able to have the end user access the report services I have installed the reports web site on the frontend aswell. When I then use this http://mysite/reports
I get prompted for username/password. If I use the username of the user of the database server I cannot login. This is obvious since that user is not known to the frontend. If I create a user on the frontend with the same name, I can log in but the Report Services complain that the user does not have enough rights to use the reporting services. Problem is that I cant create a frontend user on the database server.
My idea now is to setup a domain controller and use a domain user instead. Problem about that is that this is a costly affair (hosting used is not cheap). But if its the only solution and if I can be sure it works thats what I gonna do.
I am hoping that some other solution can be presented here though :)
Thanks in advance