Difficult question regarding Reporting Services

We are having an issue that 1) I can not find any documentation on how
to setup correclty and 2) have not had any postings from other experts
that have actually worked.
Here's the issue along with our environment:
Server 1 - Windows 2003 SP1 with SQL Server 2005 SP1
Server 2 - Windows 2003 SP! with SQL Server 2005 Analysis Services SP1
Server 3 - Windows 2003 SP1 with SQL Server 2005 Reporting Services
SP1
Server 1 and 2 are both member servers in the same domain. Server 3
is a workgroup server only, not a member of any domain. Due to
security concerns we can not make this server part of the domain.
Objective - to provide external access of reports to outside users.
Server 3 will sit in a DMZ and communicate with the inside data
sources.
Server 1 contains the Report Server Database
Server 2 contains the user databases used by the reports
Server 3 contains the Report Server and Report Manager websites and
Reporting Services engine which points to Server 1 for the report
server database
Every thing works fine with one exception. The website will load
correctly, authenticate the user (we are using a local account on
workgroup Server 3 to control access to the Report Manager websites).
The report data sources are configured to Store Credentials Securely
in the Database. When we store and use a SQL Server Standard Login,
the reports load correctly. We can monitor in Profiler on both Server
1 and Server 2 and see Reporting Services first hit the Report Server
database and then hit the user database defined in the data source.
The problem is that we must use a Windows Account so that our reports
may access Analysis Services (which does not support Standard
Logins). Please do not reply stating to make sure the check box in
the data source is checked as it is and I wish this were that simple.
If I join server 3 to the domain, the report runs perfectly using the
exact same data source definition defined with the domain windows
account and the checkbox selected to used Windows Credentials. But
when I take server 3 back out of the domain and into just a workgroup,
the report fails to run, specifically in the RS logs during the
RendReport process. I'm beginning to think that you simply can not
store and use Windows account information in the data source when the
Report Server is not in the domain, which to me does not make any
sense at all but will all the testing we have gone through, that's the
only conclusion I can see.
Has anyone out there experienced this issue or a similar issue? Or if
any of you MVPs out there want to try this out, use Virtual PC. We
tested it and the behavior is exactly the same as on our physical
servers.
Any assistance anyone can provide is GREATLY appreciated!!
Thanks in advance.
MartinI have not done exactly this but I just read up on Analysis services and
believe I have a solution for you. Create a local windows user on the
Analysis services box. Give it the read only rights you want it to have in
Analysis services. Then use this username and password for your credentials.
Another possibility (in case you didn't alread try this) is try the windows
domain user by putting domainname\username in for the user and then its
password. See if that works as well.
Bruce Loehle-Conger
<martinghale@.gmail.com> wrote in message
news:1172175247.449272.235750@.k78g2000cwa.googlegroups.com...
> We are having an issue that 1) I can not find any documentation on how
> to setup correclty and 2) have not had any postings from other experts
> that have actually worked.
> Here's the issue along with our environment:
> Server 1 - Windows 2003 SP1 with SQL Server 2005 SP1
> Server 2 - Windows 2003 SP! with SQL Server 2005 Analysis Services SP1
> Server 3 - Windows 2003 SP1 with SQL Server 2005 Reporting Services
> SP1
> Server 1 and 2 are both member servers in the same domain. Server 3
> is a workgroup server only, not a member of any domain. Due to
> security concerns we can not make this server part of the domain.
> Objective - to provide external access of reports to outside users.
> Server 3 will sit in a DMZ and communicate with the inside data
> sources.
> Server 1 contains the Report Server Database
> Server 2 contains the user databases used by the reports
> Server 3 contains the Report Server and Report Manager websites and
> Reporting Services engine which points to Server 1 for the report
> server database
> Every thing works fine with one exception. The website will load
> correctly, authenticate the user (we are using a local account on
> workgroup Server 3 to control access to the Report Manager websites).
> The report data sources are configured to Store Credentials Securely
> in the Database. When we store and use a SQL Server Standard Login,
> the reports load correctly. We can monitor in Profiler on both Server
> 1 and Server 2 and see Reporting Services first hit the Report Server
> database and then hit the user database defined in the data source.
> The problem is that we must use a Windows Account so that our reports
> may access Analysis Services (which does not support Standard
> Logins). Please do not reply stating to make sure the check box in
> the data source is checked as it is and I wish this were that simple.
> If I join server 3 to the domain, the report runs perfectly using the
> exact same data source definition defined with the domain windows
> account and the checkbox selected to used Windows Credentials. But
> when I take server 3 back out of the domain and into just a workgroup,
> the report fails to run, specifically in the RS logs during the
> RendReport process. I'm beginning to think that you simply can not
> store and use Windows account information in the data source when the
> Report Server is not in the domain, which to me does not make any
> sense at all but will all the testing we have gone through, that's the
> only conclusion I can see.
> Has anyone out there experienced this issue or a similar issue? Or if
> any of you MVPs out there want to try this out, use Virtual PC. We
> tested it and the behavior is exactly the same as on our physical
> servers.
> Any assistance anyone can provide is GREATLY appreciated!!
> Thanks in advance.
> Martin
>|||Bruce,
Thanks for the advice. That was similar to what I had researched as
well. It didn't work before but thought I would give it another try.
I found something interesting. The local account not only needs to be
on the Analysis Server but also on the external report report server.
Once I created the same account name locally on both servers, RS was
able to make the connection with the Windows credentials stored in the
Data Source.
This is something that obviously is not needed when all machines are
in the same domain or are in trusted domains. I have only found this
issue when the report server is not of the domain or a trusted domain,
but only a workgroup...
Thanks for your input!|||Good to know (about creating both places). Glad to see it works for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<martinghale@.gmail.com> wrote in message
news:1172245522.365928.221680@.p10g2000cwp.googlegroups.com...
> Bruce,
> Thanks for the advice. That was similar to what I had researched as
> well. It didn't work before but thought I would give it another try.
> I found something interesting. The local account not only needs to be
> on the Analysis Server but also on the external report report server.
> Once I created the same account name locally on both servers, RS was
> able to make the connection with the Windows credentials stored in the
> Data Source.
> This is something that obviously is not needed when all machines are
> in the same domain or are in trusted domains. I have only found this
> issue when the report server is not of the domain or a trusted domain,
> but only a workgroup...
> Thanks for your input!
>sql