locked
URL problems with SQL Server Reporting Services 2012 with wildcard SSL certificate RRS feed

  • Question

  • Hi,
    I have single server, domain member, with SQL Server 2012 SP1 Reporting Services.

    I am trying to get work with url: https://reports.mydomain.com
    I have valid wildcard certificate (*.mydomain.com) implemented and configured URLs in Configuration Manager.

    https://reports.mydomain.com/ReportServer - works fine

    https://reports.3pro.hr/Reports/ - I got error:

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

    In rsreportserver.config I have:
    <Add Key="SecureConnectionLevel" Value="2"/>

    When looking my ReportServerService_date.log file I have something like:

    configmanager!DefaultDomain!3f4c!03/10/2013-20:24:34:: i INFO: Using report server internal url https://localhost:443/ReportServer.
    configmanager!DefaultDomain!3f4c!03/10/2013-20:24:34:: i INFO: Using report server external url https://serverhostname:443/ReportServer.
    configmanager!DefaultDomain!3f4c!03/10/2013-20:24:34:: i INFO: Using url root https://reports.mydomain.com/ReportServer.
    configmanager!DefaultDomain!3f4c!03/10/2013-20:24:34:: i INFO: Using report server internal url https://localhost:443/ReportServer.
    configmanager!DefaultDomain!3f4c!03/10/2013-20:24:34:: i INFO: Using report server external url https://serverhostname:443/ReportServer.
    configmanager!DefaultDomain!3f4c!03/10/2013-20:24:34:: i INFO: Using url root https://reports.mydomain.com/ReportServer.


    Also, error shown in log file:
    appdomainmanager!ReportManager_0-2!4c50!03/10/2013-20:24:53:: e ERROR: Remote certificate error RemoteCertificateNameMismatch encountered for url https://localhost/ReportServer/ReportService2010.asmx.

    ui!ReportManager_0-2!4c50!03/10/2013-20:24:54:: e ERROR: System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.


    Btw, is there a way to delete/disable access using https://localhost and/or servername (not FQDN) since SSL will not work in this way for me, and I want access only by full url - https://reports.mydomain.com , not localhost ..


    -- Hrvoje Kusulja


    Sunday, March 10, 2013 7:35 PM

Answers

  • I spent one of my 4 free support incidents with Microsoft (part of MSDN subscription) this year to get this investigated.  The tech support person helped me through several issues but had to leave to attend some training, and I got past the last hurdle before she called me back.  Here are the steps that resolved this issue for me.  I know for sure that step 5 was necessary.  Step 1 may not apply to you, and steps 2-4 may or may not have been necessary (they didn't immediately fix the issue, but I didn't roll them back either so they may have been necessary.)

    Step 1:
    Ensure you are editing the correct rsreportserver.config file.  I had been making changes to a file that was installed in C:\Program Files\Common Files\microsoft shared\Web Server Extensions\14\WebServices\Reporting, but that was a rsreportserver.config file for some sharepoint integration that I'm not using.  The correct path on my system was E:\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config, but yours may vary. If you can't figure it out, look in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS11.MSSQLSERVER\Setup in the key named SQLPath, and then go to the ReportServer subdirectory of that path.

    Step 2: 

    In rsreportserver.config, ensure that SecureConnectionLevel is set to the value 3.  Was set to 0 in my configuration.  Corrected line in your rsreportserver.confiog file should look like:

    <Add Key="SecureConnectionLevel" Value="3"/>

    Step 3:
    In rsreportserver.config, add the correct value to the <URLRoot> element (which already exists in the file.)  In my configuration, this value was blank.  The value should be the fully qualified path to your report server, with a hostname that is valid for your certificate.  For example, if my cert matches *.mydomain.local:

    <UrlRoot>
    https://myserver.mydomain.local/ReportServer
    </UrlRoot>

    Step 4:
    Ensure that your certificate exists in Trusted Root Certification Authorities in certmgr for the local machine.  I had the certificate installed as a Personal certificate for the local machine, which I still think was correct (the certificate wasn't actually the problem and worked correctly for Report Server, and the failure was caused by SSRS incorrectly making a https request to a localhost URL), but she had me remove the certificate from Personal and add it to Trusted Root Certificate Authorities.  That broke things and the cert was no longer listed as a cert I could bind to, so we then copied it so it existed in both Personal and Trusted Root Certificate Authorities.  This is how I left it, not sure if that was necessary.

    Step 5:
    This was the fix that finally got things to work. In rsreportserver.config, add the same value to the <ReportServerUrl> element (which also already exists in the file) that you added in step 3.  In my configuration, this value was also blank. The corrected value should be the same as in step 3, for example:

    <ReportServerUrl>
    https://myserver.mydomain.local/ReportServer
    </ReportServerUrl>

    Then restart your report server (stop & then start in Report Server Configuration Manager), and the problem should go away.  At least it did for me.

    Good luck!

    • Proposed as answer by Joe Toomey Thursday, March 28, 2013 6:46 PM
    • Marked as answer by Fanny Liu Wednesday, April 3, 2013 2:35 AM
    Thursday, March 28, 2013 6:46 PM

All replies

  • Hi Hrvoje,

    The issue may be caused when the certificate isn't installed correctly in the trusted root for the local computer.To verify and install the certificate, Please refer to the blog blow:
    SSL configuration and Reporting Services

    Regards,
    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Tuesday, March 12, 2013 4:48 AM
  • Hi,

    certificate is installed, tested and it works, all tests passes also.

    certificate is wildcard, so *.mydomain.com,  and SSRS url is https://reports.mydomain.com , it is fine and works.  Problem is that SSRS inside (in background), which you can see in logs that i have put here, uses https://localhost address, and server hostname (but not full FQDN), so https://myserver/ .

    One solution is to allow parallel http requests, and then it works (becouse it uses http://localhost) , but I do not want that, i want only secure http.


    -- Hrvoje Kusulja

    Tuesday, March 12, 2013 8:18 AM
  • Hi Hrvoje,

    Did you add multiple configurations under the "Multiple SSL Identities for the Report Server Web Service" in Reporting services Configuration Manager? If so, please try to remove the SSL identities that no need.

    What’s more, you can check the URLReservations in rsreportserver.config.

    Regards,

    Fanny Liu

    If you have any feedback on our support, please click here.


    Fanny Liu
    TechNet Community Support

    Wednesday, March 13, 2013 2:42 AM
  • Hi,

    yes I do have multiple SSL URL-s since I need them on IPv4 and IPv6, and I need all of them specified in rsreportserver.config

    Since i use wildcard certificate it also shows some URLs like: <UrlString>https://+:443</UrlString>

    Kind regards


    -- Hrvoje Kusulja

    Wednesday, March 13, 2013 9:17 PM
  • Is there any solution to this? I have the exact same issue, and the only 'workaround' I've found is to disable SSL for the report server.  This isn't an acceptable option though.
    Monday, March 18, 2013 9:50 AM
  • I am struggling with this same issue.  I have been through many different proposed solutions from numerous forums and none of them address the issue discussed here.  When an SSRS server is configured for SSL access only (port 80 access removed) and SSL is correctly configured with a wildcard SSL certificate, the Report Manage URL is no longer accessible.  The failure is caused by the internal use of improper URLs by SSRS.  

    When I try to go to the Report Manager URL in a browser, I enter the correct fully qualified hostname which matches my wildcard certificate.  The exception that is recorded in the log file shows that SSRS is making a request to https://localhost/ReportServer/ReportService2010.asmx.  This request will obviously fail, and should instead be using the fully qualified hostname in the requests to the report server web services.  It's noteworthy that SSRS does not allow me to explicitly specify the Report Server Web Service URL -- when configuring with a wildcard certificate it automatically fills this value in as "https://+:443/ReportServer".

    Again, I can confirm that the certificate is installed correctly, as I am able to use the report server itself (i.e. https://myreportserverfqdn/ReportServer/Pages/ReportViewer.aspx?MyReports works just fine.)  It's only requests to https://myreportserverfqdn/Reports/Pages/Folder.aspx that fail, and they fail b/c SSRS itself is internally making web service requests to the wrong URL.

    Here's the exception I see in the log file when I try to go to https://myreportserverfqdn/Reports/Pages/Folder.aspx (this is the same that is seen by OP).

    appdomainmanager!ReportManager_0-4!1724!03/27/2013-15:20:36:: e ERROR: Remote certificate error RemoteCertificateNameMismatch encountered for url https://localhost/ReportServer/ReportService2010.asmx.
    ui!ReportManager_0-4!1724!03/27/2013-15:20:36:: e ERROR: System.Net.WebException: The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. ---> System.Security.Authentication.AuthenticationException: The remote certificate is invalid according to the validation procedure.

    I'd appreciate any help you can provide, even if it's just a workaround.  Thanks,

    --Joe

    Wednesday, March 27, 2013 7:49 PM
  • Yes, i confirm, with same problem, maybe we should put bug report on https://connect.microsoft.com/ ?

    -- Hrvoje Kusulja

    Thursday, March 28, 2013 5:26 PM
  • I spent one of my 4 free support incidents with Microsoft (part of MSDN subscription) this year to get this investigated.  The tech support person helped me through several issues but had to leave to attend some training, and I got past the last hurdle before she called me back.  Here are the steps that resolved this issue for me.  I know for sure that step 5 was necessary.  Step 1 may not apply to you, and steps 2-4 may or may not have been necessary (they didn't immediately fix the issue, but I didn't roll them back either so they may have been necessary.)

    Step 1:
    Ensure you are editing the correct rsreportserver.config file.  I had been making changes to a file that was installed in C:\Program Files\Common Files\microsoft shared\Web Server Extensions\14\WebServices\Reporting, but that was a rsreportserver.config file for some sharepoint integration that I'm not using.  The correct path on my system was E:\MSRS11.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config, but yours may vary. If you can't figure it out, look in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSRS11.MSSQLSERVER\Setup in the key named SQLPath, and then go to the ReportServer subdirectory of that path.

    Step 2: 

    In rsreportserver.config, ensure that SecureConnectionLevel is set to the value 3.  Was set to 0 in my configuration.  Corrected line in your rsreportserver.confiog file should look like:

    <Add Key="SecureConnectionLevel" Value="3"/>

    Step 3:
    In rsreportserver.config, add the correct value to the <URLRoot> element (which already exists in the file.)  In my configuration, this value was blank.  The value should be the fully qualified path to your report server, with a hostname that is valid for your certificate.  For example, if my cert matches *.mydomain.local:

    <UrlRoot>
    https://myserver.mydomain.local/ReportServer
    </UrlRoot>

    Step 4:
    Ensure that your certificate exists in Trusted Root Certification Authorities in certmgr for the local machine.  I had the certificate installed as a Personal certificate for the local machine, which I still think was correct (the certificate wasn't actually the problem and worked correctly for Report Server, and the failure was caused by SSRS incorrectly making a https request to a localhost URL), but she had me remove the certificate from Personal and add it to Trusted Root Certificate Authorities.  That broke things and the cert was no longer listed as a cert I could bind to, so we then copied it so it existed in both Personal and Trusted Root Certificate Authorities.  This is how I left it, not sure if that was necessary.

    Step 5:
    This was the fix that finally got things to work. In rsreportserver.config, add the same value to the <ReportServerUrl> element (which also already exists in the file) that you added in step 3.  In my configuration, this value was also blank. The corrected value should be the same as in step 3, for example:

    <ReportServerUrl>
    https://myserver.mydomain.local/ReportServer
    </ReportServerUrl>

    Then restart your report server (stop & then start in Report Server Configuration Manager), and the problem should go away.  At least it did for me.

    Good luck!

    • Proposed as answer by Joe Toomey Thursday, March 28, 2013 6:46 PM
    • Marked as answer by Fanny Liu Wednesday, April 3, 2013 2:35 AM
    Thursday, March 28, 2013 6:46 PM
  • Steps 2,3 and 5 did it for me. Nice work!
    Tuesday, February 11, 2014 6:22 PM
  • Yes... create forum post, solved it for me step 2,3 and 5.
    Tuesday, June 24, 2014 7:14 AM
  • Thanks Joe for this post, after a half day of dead ends, this almost solved  my problem but, I had to add some other tweaks to get it to work. I followed all your first five steps and could run SSRS config wizard without errors, but could not get it to display my wildcard cert. Not sure if my Windows version is part of the cause or not, but I'm listing here for other. My server is Server 2016 Standard, SQL was in clean install of SQL 2016.  I made these changes in this order to get things working.

    Step 1: Complete your five steps.

    a. To confirm something you mentioned about accessing the wildcard cert in SSRS wizard. You are correct, not only do you have to add the cert to Trusted Root Certificate Authorities for the computer, but also to the Personal store. Without it in the personal store, it does not show up in the wizard dropdown list.

    Step 2:

    After completing the wizard without any cert, to complete the process without errors, I confirmed URLs for web services and apps ran, and they did. 

    Step 3:

    Add a second IP address to the network interface, using the Advanced Setting in the Network Config wizard, this is so I could bind a URL to a specific IP rather than using the All IP settings in the wizard.

    Step 4:

    I add four new HTTPS URLs in the SSRS configuration wizard, two to the Web Service and two to the Web App. I had read an MS post that stated you needed matching URLs in both the web service and the app for a URL to work. The first URL was using a cert that comes from my CA which has the NETBIOS name of the server in it. This is in the form of netbiosname.domain.com. On my 2nd HTTPS URL I used my wildcard certificate which was now accessible in the wizard. That name was in the form of dnsHostArecord.domain.com.  I ran the wizard to completion and got no red X's but it failed to bind the wildcard cert to a URL. 

    Step 5:

    Went into rsreportserver.config and added <Add Key="SecureConnectionLevel" Value="3"/>. I then changed the URLs for my wildcard cert to match dnsHostArecord.domain.com. Also verified that the the Host A record I had created would ping to the proper IP address. I then went through your five steps again to confirm changes.  Rebooted the server and tested.

    Step 6:

    I then test the default URL created by the wizard and the URL which had my CA cert associated with the server name, both worked as expected. IMPORT NOTE, the URL bound to my CA cert did not prompt me for a login, I think this is because my credentials were cached in the browser. If they had not been it would have prompted me for a login, this is verified in the next step.

    Step 7:

    For the URL with my wildcard cert, i.e. dnsHostArecord.domain.com I could browse to the site and I was prompted for a login which failed. I tried domainName\UserAcct and UserAcct@domain.com as I had enable Kerberos and NTLM authentication, but each failed without giving errors. I just kept getting prompted to login.

    Step 8:

    I checked the event logs and found this warning in System Log, Source: LSA (LsaSrv):

    The program ReportingServicesService.exe, with the assigned process ID xxxx, could not authenticate locally by using the target name HTTP/dnsHostArecord.domain.com. The target name used is not valid. A target name should refer to one of the local computer names, for example, the DNS host name.
    Try a different target name.

    Step 9:

    A good deal of trial an error went on after this, and I made several changes, I'm not sure which of them worked so I am only rolling two of them back, as mentioned below, even though they are causing no other problem nor generating any events.

    Step 10:

    I had added my dnsHostArecord.domain.com name to the LMHosts file, and added a new DNS suffix in the network connection wizard for dnsHostArecord. Rebooted and tested. This did not fix the problem.

    Step 11:

    a. To the registry I added the following key. 
      HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0 as a Multi-String Value.
      Named the key BackConnectionHostNames, and then press ENTER.
      Right-click BackConnectionHostNames, and then click Modify.
      In the Value data box, typed the URL dnsHostArecord.domain.com and then clicked OK.

    b. Navigated to HKLM\system\CurrentControlSet\Services\Lanmanserver\parameters

    Added new DWORD32 named DisableStrictNameChecking: and set value to =1
    Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
    Added new DWORD32 named DisableLoopbackCheck: and set value =1

    Now reboot the server

    Step 12:

    I repeated test on the first two URL that worked previously to ensure they still worked. I then tried my custom URL dnsHostArecord.domain.com and as before I was prompted to login. This time when I entered just my UserName and Password, no prefix with a domain, I was logged in and had access to both the web service page and the SSRS app.

    I think the trick for me were the registry changes, but not wanting to test troubled waters, I plan on leaving everything in place except for changes to LMHOST, DNS suffix changes. I will also change the  rsreportserver.config <Add Key="SecureConnectionLevel" Value="2"/> value back to "3" as I am certain the NTLM authentication is working and I don't want credentials passed in clear text.

    Hope this helps others.




    Saturday, September 14, 2019 2:29 AM