You cannot view some of your SSL Certificates in Reporting Services Configuration Manager


Symptoms


When you try to configure SSL on your Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2 Reporting Services instance by using the Reporting Services Configuration Management tool. you may notice that you are not able to select some of the certificates from the list, though they are valid.

To determine if your certificate is affected by this problem refer to the procedure in the More Information section below.

Note: This issue applies to a very small number of SSL certificates.

Cause


The issue occurs because Reporting services uses non-RFC 5280 compliant code to parse the SSL metadata attributes and that causes some of the certificates to be considered invalid. Microsoft has confirmed that this is a bug in the Microsoft products that are listed in the "Applies to" section. This is scheduled to be addressed in the next major release of the SQL Server product.

Resolution


You can workaround the problem using the following procedure:

NOTE: Once you implement the workaround discussed below, do not modify the URL or the IP settings in the Reporting Services Configuration Management tool. Doing so may overwrite the changes and causes the workaround to become invalid.

Please also note that installing a Cumulate Update or Service Pack for the SQL Server product may overwrite these changes and hence you need to repeat the procedure after installing either of them.

Step 1: Obtain the thumbprint or hash of the SSL certificate that you want to use for SSL configuration.
  1. Click Start, click Run, type mmc, and click OK.
  2. On the File menu, click Add/Remove Snap-in.
  3. In the Add or Remove Snap-ins dialog box, in the Available snap-ins list, click Certificates, and click Add.
  4. In the Certificates snap-in dialog box, click Computer account, and click Next.
    NOTE: If your certificate isn't installed in the local computer account store it is required to be there for SQL Reporting services to pick it up and use it. You will need to import it after opening this.
  5. In the Select Computer dialog box, click Local computer: (the computer this console is running on), and click Finish.
  6. In the Add or Remove Snap-ins dialog box, click OK.
  7. In the Certificates snap-in, in the console tree, expand Certificates (Local Computer), expand Personal, and navigate to the SSL certificate that you would like to use.
    Note: If you haven't imported your SSL Certificate here yet right click "Certificates->All Tasks->Import..." and follow the steps to import your certificate.
  8. Double-click the SSL certificate and click on the General tab and verify that it says "You have a private key that corresponds to this certificate" with a key icon next to it at the bottom of the window. If you do not see that message, your certificate will NOT work as the private keys are missing. You will need to locate a copy of your SSL Cert that contains the private keys before continuing.
  9. Click on the Details tab and select the Thumbprint field from the list. Copy its value to a text editor and remove all the spaces between the hexadecimal digits.
    For example, if the hexadecimal string value is f3 3a 61 c0 f5 3c fb 5b c4 5a 20 c6 cf 54 ed 15 5f ad 43 3f, the edited version in the text file will be f33a61c0f53cfb5bc45a20c6cf54ed155fad433f
  10. On the same tab, select the Subject field and copy its value to the text editor as well. For example the value may be of the form: CN = MyTestServer.com .
Step 2: Modify the RsReportServer.Config file to add URL Reservation and SSL Certificate information.
  1. Open the following folder:
    C:\Program Files\Microsoft SQL Server\MSRS10_50.<INSTANCENAME>\Reporting Services\ReportServer
  2. Back up the Rsreportserver.config file.
  3. Open the Rsreportserver.config file in any XML editing program or a text editor.
  4. Locate the Application section of the file.
    Note: The Application section starts with the <Application> element. 
  5. In the Application section, locate the URLsection. 
    Note: The URL section starts with the <URL> element. 
  6. Copy the URL section and paste the same immediately below the current URL section in the file.
    Example:
    Before:
      <Application>
       <Name>ReportServerWebService</Name>
       <VirtualDirectory>ReportServer</VirtualDirectory>
       <URLs>
        <URL>
         <UrlString>http://+:80</UrlString>
         <AccountSid>S-1-5-20</AccountSid>
         <AccountName>NT AUTHORITY\NETWORKSERVICE</AccountName>
        </URL>
       </URLs>
      </Application>
       
    After:
       <Application>
       <Name>ReportServerWebService</Name>
       <VirtualDirectory>ReportServer</VirtualDirectory>
       <URLs>
        <URL>
         <UrlString>http://+:80</UrlString>
         <AccountSid>S-1-5-20</AccountSid>
         <AccountName>NT AUTHORITY\NETWORKSERVICE</AccountName>
        </URL>
    <URL>
         <UrlString>http://+:80</UrlString>
         <AccountSid>S-1-5-20</AccountSid>
         <AccountName>NT AUTHORITY\NETWORKSERVICE</AccountName>
        </URL>
       </URLs>
      </Application>

  7. Modify the URL section you added as follows:
    • Replace http with https
    • Replace the + in the UrlString tag with the value of the Subject ((common name) that you copied from your certificate (Step 1: 10)
    • Change the port from 80 to 443
    Example:
       Before:
        <URL>
         <UrlString>http://+:80</UrlString>
         <AccountSid>S-1-5-20</AccountSid>
         <AccountName>NT AUTHORITY\NETWORKSERVICE</AccountName>
        </URL>

    After
    <URL>
    <UrlString>https://MyTestServer.com:443</UrlString>
         <AccountSid>S-1-5-20</AccountSid>
         <AccountName>NT AUTHORITY\NETWORKSERVICE</AccountName>
        </URL>
  8. Locate the <ReportManager> element in the Application section and repeat the actions in items 6 and 7 of this step (Step 2:6,7) for the ReportManager application as well.
  9. Locate the </Configuration> tag and place the following immediately above it.
    SSLCertificateConfiguration>
       <Bindings>
        <Binding>
         <ApplicationName>ReportManager</ApplicationName>
         <CertificateHash>VALUE_FOR_THUMBPRINT</CertificateHash>
         <IPAddress>0.0.0.0</IPAddress>
         <Port>443</Port>
        </Binding>
        <Binding>
         <ApplicationName>ReportServerWebService</ApplicationName>
    <CertificateHash>VALUE_FOR_THUMBPRINT</CertificateHash>
         <IPAddress>0.0.0.0</IPAddress>
         <Port>443</Port>
        </Binding>
       </Bindings>
     </SSLCertificateConfiguration>

    Note: Replace the "VALUE_FOR_THUMBPRINT" with the value you previously copied to a text file in Step 1.(Step 1: 9).
        Example:
     <CertificateHash>f33a61c0f53cfb5bc45a20c6cf54ed155fad433f</CertificateHash>
  10. Replace the value in the <IPAddress> tag with the desired IP address, if you want to bind the SSL Certificate to a specific IP Address on the server rather than all of them.
  11. Save the file.

Step 3: Create the URL Bindings:
  1. Open an Administrative command prompt and run the following command after replacing the VALUE_FOR_CERT_SUBJECT with the value of the Subject ((common name) that you copied from your certificate (Step 1: 10) and <DOMAIN\user> with the user in the <AccountName> tags in the Rsreportserver.config. Enclose the user= value in " if it contains a space.

    netsh http add urlacl  url=https://VALUE_FOR_CERT_SUBJECT:443/Reports/ user=DOMAIN\user listen=yes

    Example: netsh http add urlacl  url=https://MyTestServer.com:443/Reports/ user=DOMAIN\user listen=yes
  2. Repeat the above command (Step 3:1) for the Reporsterver URL by replacing the last part of the URL with /ReportServer/.
     Example: netsh http add urlacl  url=https://MyTestServer.com:443/ReportServer/ user="NT AUTHORITY\NETWORK SERVICE" listen=yes
  3. If you had configured your SSL binding to a specific IP address (Step2:10), then run the following command replacing 0.0.0.0 with that IP Address. If you configured SSL for all the IP addresses on your system run the command as is from an administrative command prompt:

    netsh http add sslcert  ipport=0.0.0.0:443 certhash=VALUE_FOR_THUMBPRINT appid={1d40ebc7-1983-4ac5-82aa-1e17a7ae9a0e}

    Example:  netsh http add sslcert  ipport=0.0.0.0:443 certhash=f33a61c0f53cfb5bc45a20c6cf54ed155fad433f appid={1d40ebc7-1983-4ac5-82aa-1e17a7ae9a0e}
Step 4: Completion:
  1. Start the SQL Server Reporting Service.
  2. Try to browse to https://MyTestServer.com/Reports and verify that it is functional.

More Information


Procedure to check if your certificate is affected by the problem discussed in this article:
  1. Click Start, click Run, type mmc, and click OK.
  2. On the File menu, click Add/Remove Snap-in.
  3. In the Add or Remove Snap-ins dialog box, in the Available snap-ins list, click Certificates, and click Add.
  4. In the Certificates snap-in dialog box, click Computer account, and click Next.
    NOTE: If your certificate isn't installed in the local computer account store it is required to be there for SQL Reporting services to pick it up and use it. You will need to import it after opening this.
  5. In the Select Computer dialog box, click Local computer: (the computer this console is running on), and click Finish.
  6. In the Add or Remove Snap-ins dialog box, click OK.
  7. In the Certificates snap-in, in the console tree, expand Certificates (Local Computer), expand Personal, and navigate to the SSL certificate that you would like to use.
    Note: If you haven't imported your SSL Certificate here yet right click "Certificates->All Tasks->Import..." and follow the steps to import your certificate.
  8. Double-click the SSL certificate and click on the Details tab.
  9. Locate the Subject field and verify that the value after the "CN=" line matches the URL of the SQL Reporting Services web site you are trying to configure SSL for. Also verify the values for other properties in this field and ensure that they are set to their correct values.
  10. Locate the Enhanced Key Usage field and verify that the value is either set to Server Authentication (1.3.6.1.5.5.7.3.1) or All.
If the values in Steps 9 and Steps 10 from the above procedure are set to the correct values and your certificate still does not show up in the Reporting Services Configuration Manager, then you may be experiencing the problem discussed in this article.

The following steps are optional and can be used for extra verification.
  1. Open IIS Manager.
  2. Select one of the web sites you may have configured on this server and try to assign the SSL Certificate to the site.
  3. If you are able to select and assign the certificate to that site, it can indicate that you may have a certificate that is affected by the problem discussed in this article.


Additional references: