More issues with Sql Reporting Services - The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel

Problem: Installed on a new server, and cannot go to:

https://localhost/reports

I receive: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

Odd, as ssl is ok (and the root ca authority which is the server itself has been added as a trusted root on the system).

This is interesting in my case, the web service identity wasn't set and it wouldn't allow me to set it to the "ReportServer" app pool, I received an error

ReportServicesConfigUI.WMIProvider.WMIProviderException: A virtual directory must first be created before performing this operation.
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.SetWebServiceIdentity(String applicationPool)



I tried to apply default settings and received:

System.Runtime.InteropServices.COMException (0x80004005) at System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) at System.Management.ManagementObject.InvokeMethod(String methodName, ManagementBaseObject inParameters, InvokeMethodOptions options) at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ResetVirtualDirectoryMappings()

awesome.

Since this is a shared server, I tried to create a new virtual directory so I could use a specific virtual dir and app pool I'd create. Upon giving a name to create, the config tool crashed.

most awesome.

Upon loading the tool and trying to set web service identity again, it let me do it for the Report Server, but failed for the Report Manager with:

ReportServicesConfigUI.WMIProvider.WMIProviderException: A virtual directory must first be created before performing this operation.
at ReportServicesConfigUI.WMIProvider.RSReportManagerAdmin.SetReportManagerIdentity(String applicationPool)

yes, a virtual directory indeed exists and somehow it doesn't recognize it.

ok.. lets go back a bit. Im not sure when sp2 was installed on this machine and I have seen issues with sp2 having to be installed again, but thats usually a different error along the lines of:

Sql errorystem.Data.SqlClient.SqlException: Could not find stored procedure 'GetDBVersion'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

So I just deleted the virtual directories from IIS for ReportServer and Reports, recreated them in the config tool, and chi was restored to the universe.

installed the certificate to:

Enterprise Trust\Local Computer and verified it was trusted root authority.

same issue.


Turning of ssl for that folder and trying with http://localhost/reports

same issue.

In Reporting services configuration manager -> Report server virtual directory -> Certificate name, nothing is specified (plus thats the /ReportServer url)


Based on some readings, I reset IIS since some people had an issue unchecking ssl until they restarted IIS. No go for me.

I checked everything with the certificate. The server's certificate is from Exchange 2007 - IE auto generated when it was installed. I checked in trusted root certification authorities and the server's root ca certificate was in there.
I checked in IIS - the certificate was OK, not expired and showing up as OK as well.

since I was trying to connect to localhost/reports I used the system name:
http://lois/reports

same problem.

so at this point, ssl is off from the sql server reporting services configuration tool, and I've unchecked it from the virtual directory in IIS.

So whats the solution?
In the file notepad d:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager\RSWebApplication.config
there are two items which need addressing

1. ReportServerUrl, which in my case was blank and ReportServerVirtualDirectory which was set to Reports

According to MS, they are mutually exclusive, IE you can only set one or the other. So I removed the virtual directory entry and set the report server url, and saved it and voila.. it worked:

<reportserverurl>http://lois/reportserver</reportserverurl>
<reportservervirtualdirectory></reportservervirtualdirectory>


another note: if you have generated your own certificate, the system you are deploying from won't be able to deploy to the target server as well. You will receive the same error in addition (in visual studio) viewing the extended error information you may see:
"The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. "
"The remote certificate is invalid according to the validation procedure. (System)"

So, navigate from your system to the report server. View the certificate and import it into the trusted root certification authorities, and then on the certificate path tab (right click on the document and choose properties) check to make sure there are no Xs on the certificate issuer. If there is - import it from there into the same store - trsuted root certification authorities.


OH but wait!
I can see all the report definitions, but when I attempt to run them I receive:
The request failed with HTTP status 401: Unauthorized.

hmm. I tried all different auth options, and removing ssl.

nothing.
If I turned off integrated auth and turned on anonymous access, I'd receive
The permissions granted to user 'ABBSATT\reportuser' are insufficient for performing this operation.


now.. if I used the url to go to the report server using ssl directly, it worked just fine. That was the clue I wasn't using the correct url in the rswebapplication.config

So, I went into the reporting services config tool, and did the "Apply Default Settings" on each so I could start from scratch.

Again (as expected) I was back to:
The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel.

I went into rswebapplication.config and changed the url I modified above to https://......
instead of http, and voila. it worked. all good... in all scenarios.