SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Reporting Services-reports fail to work when Datasource is on different server than Reporting...


Reporting Services-reports fail to work when Datasource is on different server than Reporting Services

Author
Message
Tom Kovarik
Tom Kovarik
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1

We are running SQL Server 2000 Reporting Services on Windows XP, with Integrated Security.
Our users CAN access our reports, ONLY if the database is on the same server as Reporting Services.
If the database is on a different server than Reporting Services server, users receive the error message:
"An error has occurred during report processing. (rsErrorOpeningAborted). Cannot create a connection to data source ..... (rsErrorOpeningConnection) Login failed for user 'NT AUTHORITY/ANONYMOUS LOGON'."

....Help please, from Tom in IT at State of Minnesota Courts


stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: Moderators
Points: 9372 Visits: 3757

Obviously it's a proof of concept system running on XP

I think that what's possibly happening is that you're IIS (on XP) is not being able to pass the credntials to the target DB server. Do you have the DS properties set up as 'Windows NT Integrated Security' or have you stored credentials in the RS database? I'm assuming the former, so what could be happening is that the users credentials get validated on the XP IIS (with success) but then these aren't allowed to be passed to any other servers (ie your other DB server/s). You could test this by setting a single set of credentials for the DS (set them up as windows credentials if required/preferred) and then test run a reportthat uses the datasource.

HTH,



Steve.
stevefromOZ
stevefromOZ
SSCrazy Eights
SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)SSCrazy Eights (9.4K reputation)

Group: Moderators
Points: 9372 Visits: 3757

Hey Tom,

Further to last post, in the help file within Report Manager, the following indicates that you're seeing expected behaviour (if using integrated). Basically this means you can only have a single 'hop' (passing of credentials) and this is used when the user authenticates against the RS server (on the XP box). If you were using 2003 there is a setting that could help (lets credentials flow) but if this isn't possible, you'll either need kerberos (ergh) or simply change your method of connecting (use SQLauthentication, use a single UID/PWD etc etc).

Windows NT Integrated Security

Use the Windows credentials of the current user to access the data source. Choose this option when the credentials that are used to access a data source are the same as those used to logon to the network domain.

This option works best when Kerberos is enabled for your domain, or when the data source is on the same computer as the report server. If Kerberos is not enabled, Windows credentials can be passed to one other computer. If additional computer connections are required, you will get an error instead of the data you expect.

Do not use this option to run unattended reports or reports that are available for subscription. The report server initiates the running of unattended reports. The credentials of the report server that are used to access the report server database cannot be used to access external data sources.

Cheers,



Steve.
philcart
philcart
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9639 Visits: 1441

You should also note that Windows XP has a limit of 10 concurrent network connections.



Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface

Australian SQL Server User Groups - My profile
Phills Philosophies
Murrumbeena Cricket Club
lconsiglio
lconsiglio
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 279
I need to access data on a server other than my reporting server. I have tried using the UID/PWD, tried using a linked server, stored the credientials in the data source, tried using an ole db datasource; none of this has worked for me.

When I run the report in the dev environment it runs fine but when I deploy it I get the "can't access server" error.

Any suggestions?

Thanks
Lois



lconsiglio
lconsiglio
SSC Veteran
SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)SSC Veteran (225 reputation)

Group: General Forum Members
Points: 225 Visits: 279
I finally found the problem and yes, it was between the keyboard and the chair. When you make changes to a data source, be sure to delete the old one from the reporting server if you have your environment set up to not overwrite the data source, this is the defalult behavior.

So to access data on another server use a user id & password, store them on the reporting server option and life is good!



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search