locked
the report server cannot open a connection: Need help to go right direction RRS feed

  • Question

  • Hello,

    Today is the first day that I started to use Reporting service to create reports. As I expected, I am having many basic issues on how to use Reporting service, how to configure it and how to create my first report. By reading some MSDN pages, I managed to configure 'Reporting services configuration manager' and I am able to see the home page of SQL Server Reporting Services. Further, I managed to create datasource to connect my test SQL server as well. However, when I try to create a report using Report builder, I dot following message

    'No Report builder models are available. To use report builder at least one model must be published to the report server'

    From above message, I realized that I need to have at least one report model. Therefore I started to create report model using 'SQL Server Business Intelligence Development Studio' and managed to create date source and model. However, when I try to deploy my model I am getting following error message

    "The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.  0 0 "

    How to fix this issue? I tested my connection successfully in the data source and it is working fine. I cannot find the cause of this issue. Can some one please help me on this? Apart from this, can some one point the right direction on 'Creating reports in Sql server reporting service'

    Please help me

    Thank you for reading my post,

    Raj


    Dreaming a world without any war in anywhere

    Friday, May 11, 2012 6:44 PM

Answers

  • Hi Raj,

    Thanks for your posting.

    Based on the detailed error message you have posted above, the issue occurs because the Database Engine instance that hosts the report server database is not configured for remote connections. To resolve the issue, please follow the steps below:

    1. Open the SQL Server Configuration Management tool.
    2. Click the triangle in front of “SQL Server Network Configuration”, and click the “Protocols for MSSQLSERVER”. (If you are using a named SQL Server instance, please select the named instance instead.)
    3. Please make sure both the “TCP/IP” and “Named Pipes” protocols are enabled. To enable a protocol, please right-click on the protocol and click “Enable”.

    Then, you should be able to deploy the report model properly. When deploying a report model for the first time, we need to deploy the entire report model project.

    References:

    Regards,
    Mike Yin

    • Marked as answer by Rajeeshun Wednesday, May 16, 2012 1:11 PM
    Tuesday, May 15, 2012 4:01 AM

All replies

  • Hi Raj

    Could be your access to the reporting database (not the report model database).  Check your current logon can access the ReportServer database.


    Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

    Monday, May 14, 2012 1:52 AM
  • Hello,

    Check out the threads below. It may help you.

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a0320f0f-f1f1-4e5b-aa26-4d90a53b119f

    http://sqljoe.wordpress.com/2011/02/16/ssrs-errors-rsreportserverdatabaseunavailable-the-report-server-cannot-open-a-connection-to-the-report-server-database/

    • Proposed as answer by jonnygrim1 Tuesday, May 6, 2014 9:09 AM
    Monday, May 14, 2012 7:19 AM
  • Hi Raj,

    This error occurs when the report server cannot connect to the report server catalog database. The issue may be caused by that the current Service Account has insufficient permissions to login the report server database. At this time, I suggest that you refer to the steps below:

    1. Open SQL Server Reporting Services Configuration Manager, click the "Database" tab.
    2. Click "Change Credential". In the coming dialog, make sure the “Current User-Integrated Security” is selected. Click Next.
    3. Check the “Authentication Type” is set to “Service Credential”. This will use the user that the Reporting Services running under to connect to the report server database. The user is the one we configured in "Service Account" tab.
    4. Click Next to confirm the information. Click Next to apply.

    This will auto add the specified user to login group, set this user to be the owner of report server catalog database, and also give this user "RSExce" role. For more information, please see:
    Configuring a Report Server Database Connection

    In addition, to learn how to create a report step by step, I suggest that you refer to the tutorials:
    Tutorials (Reporting Services)

    If the issue persists, please show us your SQL Server version and post the detailed error message for further analysis.

    Regards,
    Mike Yin

    Monday, May 14, 2012 1:14 PM
  • Thank you everyone for taking time and helping me

    Mike Yin,

    I tried all 3 steps you advised me, but getting same error message as below

    Error 1 The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing.  0 0 

    Following steps I tried as per your advice

    1.  I selected 'Current User-Integrated Security' and then in the next step, I selected 'Service Credentials'. It showed 'Generating Scripts', 'Applying connection rights' and 'Setting DSN' as successful

    2.  I noticed that 'Apply' button disabled and not able to press

    3. I checked the reporting database and able to see 'NT AUTHORITY\NETWORK SERVICE' as a user. (Even I can see this in the Security-->Login) and I noticed that 'RsExecRole' has been assigned to this user

    4. After I got above Error message, I went and added more roles to 'NT AUTHORITY\NETWORK SERVICE'. I added 'db_SecurityOwner' role too. But no luck yet

    My SQL server version:  'Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)   Standard Edition (64-bit) ' and running on Windows Server 2008 R2 Enterprise'

    I cannot see any detailed error message except the above message (Error 1 The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing"). Is there any way that I can see detailed error message and logs?

    Please help me... I have been trying this from Last Thursday and no luck yet

    Raj


    Dreaming a world without any war in anywhere

    Monday, May 14, 2012 2:27 PM
  • I found the Log file and below is the exact error message

    runningjobs!ReportServer_0-4!2fb4!05/14/2012-10:57:18:: e ERROR: Error in timer Running Requests DB : Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)


    Dreaming a world without any war in anywhere

    Monday, May 14, 2012 3:02 PM
  • Hi Raj,

    Thanks for your posting.

    Based on the detailed error message you have posted above, the issue occurs because the Database Engine instance that hosts the report server database is not configured for remote connections. To resolve the issue, please follow the steps below:

    1. Open the SQL Server Configuration Management tool.
    2. Click the triangle in front of “SQL Server Network Configuration”, and click the “Protocols for MSSQLSERVER”. (If you are using a named SQL Server instance, please select the named instance instead.)
    3. Please make sure both the “TCP/IP” and “Named Pipes” protocols are enabled. To enable a protocol, please right-click on the protocol and click “Enable”.

    Then, you should be able to deploy the report model properly. When deploying a report model for the first time, we need to deploy the entire report model project.

    References:

    Regards,
    Mike Yin

    • Marked as answer by Rajeeshun Wednesday, May 16, 2012 1:11 PM
    Tuesday, May 15, 2012 4:01 AM
  • Hi Mike,

    Thanks for the reply.

    Yes, I figured out the issue after reading another similar post in MSDN and configured accordingly. Apart from this configuration, when we create a model from other computers (remote machines), we need to specify the  targetServerURL in Project properties as Report server webservice Virtual directory. Otherwise default targetServerURL points to the local host

    1. Project -->Properties

    2. Check the targetServerURL. If that is pointing to your http://localhost/.. then change that to your 'Report server webservice Virtual directory'. The 'Report server webservice Virtual directory' details can be found Reporting Services Configuration Manager-->Web Service URL

    Hope this also helps some people who faces same kind of issues

    Thank you again,

    Raj


    Dreaming a world without any war in anywhere

    Tuesday, May 15, 2012 1:30 PM
  • Hi Raj,

    Thanks for your posting.

    I am glad to hear that the issue has been resolved. Also thanks for sharing your experience on the deployment configuration.

    Regards,
    Mike Yin

    Thursday, May 17, 2012 12:54 AM
  • Hi Mike

    .  I selected 'Current User-Integrated Security' and then in the next step, I selected 'Service Credentials'. It showed 'Generating Scripts', 'Applying connection rights' and 'Setting DSN' as successful

    This worked perfectly for me.

    I had the same problem after I had patched the server. There were something like 80 patches and afeter a reboot the SCOM Reporting stopped.

    Thanks to everyone in this chain.

    Pete

    Friday, May 19, 2017 8:15 AM
  • This worked for me. Whoo-Hooo!!!
    Sunday, August 26, 2018 8:28 PM