none
SSRS and ssl certificates

    Question

  • (This is a cross post from the MSDN forums, as no has repsonded after 5 days.)

    I have a problem with creating and using a self-signed ssl certificate for SSRS 2008 and there doesn't seem to be any clear instructions on the net.

    Setup - server1 (windows server 2008 w SP1) hosts a website. server2 (also windows server 2008 w SP1) hosts SQL reporting services. (SQL is standard 2008 x64 w SP1 CU1). Both servers are on the same lan & domain.

    Base setup:
    Configure the website on server1 and SSRS on server2 to use http. User connects to the website on server1 and can access the reports on server2 with no problem.

    Secure setup:
    Purchase and install a certificate for server1. The clients now connect to the website on server1 using https no problem. server2 is still configured to use http. SSRS rejects the communication from server1.

    My options are :
    a) Use a self signed cert on server2.
    b) buy another ssl cert for server2.

    The current method of creating a self-signed certificate without installing IIS 7 on server2 is:
    http://msdn.microsoft.com/en-us/library/ms186362.aspx

    When I run the makcert command:
    makecert -r -pe -n CN="server1.mycompany.com" -eku 1.3.6.1.5.5.7.3.1 -ss personal -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

    I get the error:
    Error: Save encoded certificate to store failed => 0x5 (5)
    Failed

    If anyone has any insight on the following, it will be greatly apreciated.
    a) Will a self-signed cert be enough for server to server communication?
    b) Is the "-eku" and the "-sp" options correct for SSRS?
    c) Any ideas on using makecert command to avoid the error above
    Wednesday, October 28, 2009 8:30 AM

Answers

  • Hi Chris,

    1.If all the servers are in the same domain, and the domain has one CA server, we can use the server cert to secure the communication.
    Self-signed cert is used to test. It is not enough for server to server communication.

    2. For SQL Server, an SSL certificate that is valid for server authentication that has an OID of 1.3.6.1.5.5.7.3.1 (szOID_PKIX_KP_SERVER_AUTH) is required.
    It is correct for SQL Server Reporting Services.

    For certificates created for SQL Server, this can be set to Microsoft RSA SChannel Cryptographic Provider.
    It is correct for SQL Server Reporting Services.

    3.To avoid the error, could you please try using the following command:
    makecert -r -pe -n CN="server1" -eku 1.3.6.1.5.5.7.3.1 -ss personal -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    Thursday, October 29, 2009 8:42 AM

All replies

  • I have a problem with creating and using a self-signed ssl certificate for SSRS 2008 and there doesn't seem to be any clear instructions on the net.

    Setup - server1 (windows server 2008 w SP1) hosts a website. server2 (also windows server 2008 w SP1) hosts SQL reporting services. (SQL is standard 2008 x64 w SP1 CU1). Both servers are on the same lan & domain.

    Base setup:
    Configure the website on server1 and SSRS on server2 to use http. User connects to the website on server1 and can access the reports on server2 with no problem.

    Secure setup:
    Purchase and install a certificate for server1. The clients now connect to the website on server1 using https no problem. server2 is still configured to use http. SSRS rejects the communication from server1.

    My options are :
    a) Use a self signed cert on server2.
    b) buy another ssl cert for server2.

    The current method of creating a self-signed certificate without installing IIS 7 on server2 is:
    http://msdn.microsoft.com/en-us/library/ms186362.aspx

    When I run the makcert command:
    makecert -r -pe -n CN="server1.mycompany.com" -eku 1.3.6.1.5.5.7.3.1 -ss personal -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

    I get the error:
    Error: Save encoded certificate to store failed => 0x5 (5)
    Failed

    If anyone has any insight on the following, it will be greatly apreciated.
    a) Will a self-signed cert be enough for server to server communication?
    b) Is the "-eku" and the "-sp" options correct for SSRS?
    c) Any ideas on using makecert command to avoid the error above? 

    Friday, October 23, 2009 5:57 AM
  • Hi Chris,

    1.If all the servers are in the same domain, and the domain has one CA server, we can use the server cert to secure the communication.
    Self-signed cert is used to test. It is not enough for server to server communication.

    2. For SQL Server, an SSL certificate that is valid for server authentication that has an OID of 1.3.6.1.5.5.7.3.1 (szOID_PKIX_KP_SERVER_AUTH) is required.
    It is correct for SQL Server Reporting Services.

    For certificates created for SQL Server, this can be set to Microsoft RSA SChannel Cryptographic Provider.
    It is correct for SQL Server Reporting Services.

    3.To avoid the error, could you please try using the following command:
    makecert -r -pe -n CN="server1" -eku 1.3.6.1.5.5.7.3.1 -ss personal -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    Thursday, October 29, 2009 8:42 AM

  • Self-signed cert is used to test. It is not enough for server to server communication.

    Thanks,
    Jin Chen
    Jin Chen - MSFT

    Hi Jin,

    If the self-signed cert is not suitable to use, how do I generate a CSR on the report server without installing IIS to purchase a 3rd party certificate?

    Cheers,
    Chris.
    Tuesday, November 24, 2009 10:57 AM
  • A nice tip about the "Error: Save encoded certificate to store failed => 0x5 (5) Failed" is to change the Subject's certificate store location (-sr option) to CURRENTUSER.

    Found it on http://sqlblogcasts.com/blogs/martinbell/archive/2011/02/16/Signing-Powershell-scripts.aspx

    Sunday, April 22, 2012 6:35 PM
  • Along the lines of using CURRENTUSER, you can usually get around this issue by running as administrator.

    I had the same problem creating a cert to test with and resolved it by running as administrator. Even though I'm an admin on the machine I'm trying to do this on, I still had to right-click Run As Administrator on cmd.exe to get it to work.
    • Edited by WhickedDev Monday, June 25, 2012 8:02 PM typo
    • Proposed as answer by firedog067 Tuesday, October 30, 2012 2:56 PM
    Monday, June 25, 2012 8:01 PM
  • Run the command prompt with elevated privileges (Run as Administrator).
    Monday, September 09, 2013 6:51 PM