SQL Server Performance

rsErrorOpeningConnection

Discussion in 'SQL Server Reporting Services' started by iandobrien, Jul 22, 2006.

  1. iandobrien New Member

    Hi,
    I really really need some help with the problem below. I am having problems trying to run reports from a VB.Net application. The problem is as follows:

    SQL Server 2005, SQL Server Reporting Services and the VB .NET application called ‘risk-op#%92 are on a server.

    A user tries to access the application from a remote computer. The application is displayed and the user is able to do everything. However when the user tries to click ‘Go#%92 for one of the reports the following error is displayed:


    An error has occurred during processing (rsProcessingAborted)
    Cannot create a connection to data source ‘risk-op#%92 (rsErrorOpeningConnection)

    This error occurs because the user is not a user on the SQL server database. However if I add the user to the database the user is able to then view the report.

    The reason for this is because reporting services is configured to use windows authentication as shown below. As a result when the user presses ‘Go#%92 to view a report, reporting services tries to connect to the database using windows authentication and if that user is not setup as a user on the database the user is then not able to view the report.

    I need to get around this problem. I don#%92t want to have to manually add users to the database every time (that maintenance for a couple of hundred people would be a disaster!)

    I believe that a way around this would be to use ‘Credentials stored securely in the report server#%92 in SQL Server Reporting Services. Is this the way to do it and if so, where do I add these credentials to the report server?

    I came across an article that said “Credentials are stored in reversible encryption in the report server database. You can specify one set of stored credentials for each data source used in a report. The credentials you provide retrieve the same data for every user who runs the report.”

    How do I add these credentials and is this the answer to my problem?

    Do I have to change something in the RSreportserver.config file?
    Any help would be much appreciated, thanks!
    Ian
  2. satya Moderator

  3. Anil New Member

    If authentication is mixed. Try by changing the data source properties. Change to use SQL Server authentication and specify a database user. It worked for me.

    Regards,
    Anil Kumar
  4. bparow New Member

    I am getting this message as well.

    An error has occurred during report processing. (rsProcessingAborted)
    Cannot create a connection to data source 'ACSReportServer'. (rsErrorOpeningConnection)
    For more information about this error navigate to the report server on the local server machine, or enable remote errors

    But only sometimes. There doesn't seem to be any rhyme or reason and permission to the database can't be the issue as I can sometimes connect. The server and database do not have anybody connected to it either so I do not understand what else it could be?
  5. bugster New Member

    bparow,
    I had a similar problem with the error happening at times, but that was caused by my network firewall.
    On your first one however, when I had a constant problem with users, I just created it using group permissions.

    the
    bugster

    The
    Bugster

  6. bparow New Member

    Hey bugster,

    That seems like a good place to start.
    I have the database on one server, the reporting server on another and I'm accessing the report manager from a workstation.
    Where would I start with the firewall?

    I have converted reports from Crystal and have no problem accessing the database with them. They access almost immediately.
    It seems to only happen after I leave the reports alone for a while but once I get them to work they work fine until I leave them alone again. Like the connection is lost and takes up to 15 minutes to connect again.
    I am using a database user login account, not windows authentication...
  7. bparow New Member

    Can somebody tell me what the best way is to debug this issue?

    I am trying to check the event log's Security tab on the Sql server for something but it never shows any success or failures.
    If I connect, or fail to connect to the database on a SQL server where would the logs be to show me?
    If the connection is actually not reaching the SQL server and failing on the reporting server where would I find the logs for this?
  8. markbate New Member

    this might help you http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=219964&SiteID=1
  9. backborn New Member

    [FONT=宋体]Hi,[/FONT][FONT=宋体][/FONT]
    [FONT=宋体]You can try RAQ Report,it is better than Crystal Report in many aspects,[/FONT]
    [FONT=宋体]such as it makes reporting faster, easier and preciser with excel-like design interface.[/FONT]
    [FONT=宋体]You can get it at [/FONT][FONT=宋体]http://www.raqsoft.com/[/FONT][FONT=宋体][/FONT]
    [FONT=宋体]
    [/FONT]

Share This Page