2005 report farms and the Surface Area Configration tool. The horror! The horror!


I just spent an entertaining hour trying to get a 2005 Reporting Services web farm operational. It’s up now, but I’m a tired, broken man. Here are the mistakes I made:


 


On certain 2005 SKUs, the Remote Connection property for your database engine is set to accept local connections only — which essentially will prevent (among other things) you from configuring new report server nodes to share an existing reportserver database. Use the Surface Area Configuration tool to accept remote connections from BOTH named pipes and sockets on the box hosting the reportserver database, and you’re golden.


 


I’m posting this so I can spare some other poor soul the feeling of intense inadequacy/stupidity I now am dealing with 🙂


 


Here are some errors I saw…hopefully they’ll get indexed by various search engines, and you can say, “Thank you Russell, you gave me back two hours of my life”:


 


With no remote connections accepted:


 


(Trying to use “Database Setup” inside the Reporting Services Configuration Manager to point to the remote ReportServer database):


 


A connection could not be established with the information provided. Hit OK to change your connection settings.


 


A connection was successfully established with the server, but then an error occured during the login process (provider: Named Pipes Provider, error 0 – No process is on the other end of the pipe.)


 


Interestingly enough, if I try to do the same thing with RSConfig.exe, it doesn’t throw any errors, but I get the following error after launching Report Manager..it  finally pointed me in the right direction:


 


The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable) Get Online Help An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)


 


After using the Surface Area Configuration tool to allow remote named pipes connections, rsconfig still doesn’t throw an error, but you continue to see the same error as above.


 


After named pipes is on and you use the Reporting Services Configuration Tool to set the database location, it now throws a different error for the  “Setting Connection Info for Reporting Server” task:


 


ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)


   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)


   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)


 


Finally, turn on remote TCP/IP connections at the primary server and everything works: You can change the database you’re pointing at, the remote node gets initialized, and Report Manager is able to connect once more. Feh!

Comments (20)

  1. Michelle Butler says:

    THANK YOU! This actually did same me a lot of time – and I learned something new.

  2. Promod K Antony says:

    You said is soo true : "Thank you Russell, you gave me back two hours of my life"

  3. Doug says:

    You are my hero! Allowed me to stop banging my head against the wall with the Sept. Yukon CTP … GAK!!!!!!!!

  4. Derrick says:

    Like a fool I wasted the 2 hours before doing a web search. Thanks for giving me immediate relief once I did so!

  5. Neil says:

    This is great!! I did the google after half an hour…

  6. jaygulati says:

    It’s helpful and to the point. Great!!!

  7. Pavan says:

    I am getting the following error when I run the following command :

    aspnet_regsql -ed -E -d MS

    An connection was sucessfully established with the sever,but an error occured during prelogin handshake.

    When connecting to SQLServer 2005,this error may be caused by the fact that under the default settings SQL Sever does not

    allow remote connections.<Provider:Named pipes provider,error:0 – No process is on the other end of the pipe.

    Please respond to me at itsjpavank@yahoo.com

  8. Jimmy says:

    This was great I have been struggling this quite some hours before I found this.

    Thanks a 1.000.000 🙂

  9. bob says:

    Thank you Russell. You gave other one back two hours of my life":

  10. ketan patel says:

    I am trying to deploy my report for a long time. I try the micrososft website but it did not help. Your information is very helpful.

    thank you.

  11. Mello says:

    How Do I turn on remote TCP/IP connections at the primary server.

  12. Travich! says:

    I’m trying to understand why we must use named pipes.  Is there another work around if someone doesn’t want to turn on named pipes???

  13. alex says:

    This was helpful, just to know that others have had the same problem and were able to recover.  I had to turn off "local and remote connections", switching to "local connections only", restart the  server, and then change back to "local and remote connections".

    Why the server stopped accepting connections, could be some newly installed certificates, or a newly installed Microsoft WSE 3.0 for Visual Studio…

  14. datakix says:

    Thanks Russell

    Yes, that was the problem, and to think I spent 10 – 15 minutes looking at every aspect of the ConnectionString….

  15. Roman says:

    I’ve turned on remote connections but it still doesn’t work…. still it throws the exception:

    ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

      at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)

      at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)

  16. red says:

    should both named pipes and tcp/ip be used? i still get the same error…

  17. Prarthana says:

    In my application,  I need to stop and start the SQLExpress swervice in the VB code itself. I have no problem when I first stop the service, take a back up of mdf,ldf files, Start the service again and run my query. The problem occurs when I run a query on my database first, and then stop and start the service and try running a query again. Please help. I get this error:A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)

  18. It sounds like you’re trying to read the recently stopped/restarted SQLExpress instance on the same ADO.NET connection that was open before you stopped/restarted the service. Do you .Close() the connection object and .Open() it again after you bounce SQLExpress? I would expect an error if you’re not.

  19. Kevin Cole says:

    This fixed my problem in regards to an exception I was having in SetDatabaseConnection

    (The named pipes part)