Report Builder 2.0 from ASP

• ejpolson

  Old Hand

  Points: 353

  More actions

  November 27, 2009 at 1:18 am

  #213041

  We provide a database service using SQL Server 2005 with Reporting Services 2005. We have two servers that are members of the same domain, which I’ll call DOMAIN. For the sake of this issue, I’ll call our IIS server WEB and our SQL Server DATA. Both are running on up-to-date versions of Windows Server 2003 and are co-hosted at the same facility.

  There is an installation of SQL Server 2008 (with Reporting Services) on a third computer that I’ll call MAIL. I have snapshot replication set up with MAIL as a pull client getting data from DATA to be made available for Ad Hoc reporting.

  Our application is build on classic ASP (not migrated to .NET yet due to complexity of the application).

  I have a Windows account created, which I’ll simply call DOMAIN, that has been added to reporting services as Browser, My Reports, Publisher and Report Builder

  I have a SQL Server account that I’ll call SQL that is granted access to the data we want to provide.

  I have a shared data source at the root of Report Manager that I’ll call Shared that uses NTLM with DOMAIN, and another in a client (CLIENT) location that I’ll call DSClient located at CLIENT\Data Sources\ that uses SQL’s credentials.

  I am using the following lines within the ASP page to launch Report Builder 2.0:

  SQL Server authentication

  http://Mail.WebSite.com/ReportServer/ReportBuilder/ReportBuilder_2_0_0_0.application?model=http://mail.WebSite.com/ReportServer/SharedDataSource

  NTLM authentication

  http://Mail.WebSite.com/ReportServer/ReportBuilder/ReportBuilder_2_0_0_0.application?model=http://mail.WebSite.com/reportserver/DSClient/Data+Sources/ModelAccess

  I used the “?model=” as a holdover from ReportBuilder 1.0, and it works, or at least it properly lists the data source as available in Report Builder 2.0. Report Builder launches just fine after providing the proper NTLM account for directory access. When I try to add data to the blank report, I’m prompted to select the data source (as expected). For the version that requires SQL authentication, I will immediately receive the error message that follows:

  “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)”

  After dismissing the error, I am prompted for user login and password to complete the access to the data… leading me to believe that I will have access to the data. After a period of time, I am again presented with the same error message again and the process repeats itself.

  I found a blog entry that discusses this exact error (but not form the web perspective that we are using) and tried/verified all steps in it without resolution:

  http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

  The solution we need is:

  Ad Hoc reports available through a web interface that is NOT intranet, but extranet (hence our hope that either Report Builder 2.0 or Report Builder 3.0 is more configurable). Our clients are based all over the country and their data is hosted by our database server.

  We need to be able to control the folders that are visible by our clients. I do not want our clients to be able to use the Save dialog and be able to step back up through the directory structure on our server and view the folders that other clients have.