Report Server connecting to another SQL Server

Our report server works if connecting to databases in the same SQL Server
instance. However, if I try to connect to a database of another SQL Server,
it does not work. The authentication fails.
We are using our custom data extension and use web service to connect. Do
you know what else we can do? We know that report server runs under the
NetworkService account. Can we change that?
Thanks in advance!Hi,
Welcome to use MSDN Managed Newsgroup!
Would you please clarify more clearly about how do you connect to the
database of another SQL Server? Would you please let me know your detailed
steps to connect another SQL Server and the detailed error message?
>> We know that report server runs under the
>> NetworkService account. Can we change that?
Of course you could. Change the startup account to domain account or local
system. I will recommand you change it to a domain account that also has
the administrator privilege on local machine.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael!
Please read my response inline. Thanks!
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi,
> Welcome to use MSDN Managed Newsgroup!
> Would you please clarify more clearly about how do you connect to the
> database of another SQL Server? Would you please let me know your detailed
> steps to connect another SQL Server and the detailed error message?
We use our custom data extension to connect to SQL Server via our MiddleTier
using web service (given a url). Basically, when it gets to our MiddleTier,
the request is from NetworkService instead of the current user.
NetworkService fails the authentication process.
> >> We know that report server runs under the
> >> NetworkService account. Can we change that?
> Of course you could. Change the startup account to domain account or local
> system. I will recommand you change it to a domain account that also has
> the administrator privilege on local machine.
This is what we have attempted:
(1) Looked for the ReportServer application pool in IIS Manager
(2) In ReportServer properties, switched to the Identity tab
(3) Changed it from using the predefined 'Network Service' account to
configurable and specify a local administrator account
(4) Ran a report in Report Manager
(5) Got the following error "The Report Server Web Service is unable to
access secure information in the report server. Please verify that the
WebServiceAccount is specified correctly in the report server config file.
(rsAccessDeniedToSecureData) Get Online Help"
(6) Modified the report server config file to change the value of the
WebServiceAccount
(7) Ran the same report in Report Manager
(8) Didn't get the above error this time BUT still failed to access our
MiddleTier
When a user runs a report, we want that user identity to reach our
MiddleTier, NOT a special local admin account or NetworkService. The major
reason is that we want to be able to filter the data based on the user
identity.
When we ran the same report from within Report Designer, it worked
perfectly. The reason was that it was the right user identity that reached
our Middle Tier.
Please let us know what else we could do. Thanks!!
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>
>|||Hi Jenny,
Sorry for the late response.
I think you may consider use forms authentication to securely authenticate
and authorize users. Refer the articles below for more information about
impersonate.
Impersonate in Reporting Services ASP.NET Web Clients
http://blogs.msdn.com/bryanke/archive/2004/03/03/83345.aspx
Using Forms Authentication in Reporting Services
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/ufairs.asp?frame=true
Authentication, Role-based Security, and SQL Reporting Services Web
Services
http://odetocode.com/Articles/216.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thank you Michael for the links. We will not consider using Forms
authentication now, but we are still open. If possible, we still like to
keep using the Windows Integrated security.
The first and third links are both useful in terms of using impersonation in
web clients. However, do you know how to impersonate when using the new
ReportViewer control to run remote reports (those reports that reside in a
report server) in a WinForms application?
Thanks again
Jenny
"Michael Cheng [MSFT]" wrote:
> Hi Jenny,
> Sorry for the late response.
> I think you may consider use forms authentication to securely authenticate
> and authorize users. Refer the articles below for more information about
> impersonate.
> Impersonate in Reporting Services ASP.NET Web Clients
> http://blogs.msdn.com/bryanke/archive/2004/03/03/83345.aspx
> Using Forms Authentication in Reporting Services
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
> l/ufairs.asp?frame=true
> Authentication, Role-based Security, and SQL Reporting Services Web
> Services
> http://odetocode.com/Articles/216.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Jenny,
Happy New Year!
For impersonating another user with the Report Viewer control in SQL Server
Reporting Services 2005, you may refer the article below
http://blogs.msdn.com/bimusings/archive/2005/11/18/494436.aspx
Please let me know whether it helps and if there is anything more I could
contribute.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.