Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Reporting Services-reports fail to work when Datasource is on different server than Reporting Services Expand / Collapse
Author
Message
Posted Friday, July 1, 2005 12:33 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 5, 2005 7:25 AM
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

Post #196449
Posted Sunday, July 3, 2005 9:22 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Today @ 6:17 AM
Points: 1,815, Visits: 3,465

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.
Post #196595
Posted Sunday, July 3, 2005 9:32 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Today @ 6:17 AM
Points: 1,815, Visits: 3,465

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.
Post #196597
Posted Sunday, July 3, 2005 9:43 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 4:59 PM
Points: 2,693, Visits: 1,204

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
Post #196600
Posted Wednesday, March 12, 2008 12:23 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:00 PM
Points: 173, Visits: 198
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



Post #468318
Posted Wednesday, March 12, 2008 12:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 1:00 PM
Points: 173, Visits: 198
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!



Post #468329
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse