Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSL Certificate for SSRS Expand / Collapse
Author
Message
Posted Wednesday, March 26, 2014 11:36 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 PM
Points: 299, Visits: 922
Hi, I need some help on getting an SSL certificate for SQL Server Reporting Services. I've been trying to get it working but I find that the certificates that I import into the certificate store do not have a private key and therefor will not bind in Reporting Services. I have scoured the Internet in every which way and to my surprise, there seems to be no up to date directions for requesting and loading a certificate for an encrypted connection in the modern (post IIS) SSRS. I have been using the below process to create a csr which the security team uses to create a certificate.

Is the below process missing a step? If so what, if not, how do I bring the certificate that is issued to me into the certificate store so that it includes a private key?

I just can't seem to grap the process in any of the available documentation. Appreciate any help.


1. Open the management console (click Start > Search programs and files > mmc.msc).
2. Open your Local Computer certificates (click File > Add/Remove Snap-in... > Certificates > Computer account > Next > Local computer > Finish).
3. Select Certificates (Local Computer) > Personal > Certificates.
4. With the Certificates folder for the Local Computer highlighted, click Action > All Tasks > Advanced Operations > Create Custom Request. This will open the Certificate Enrollment wizard.
5. In the Certificate Enrollment wizard:
6. On the Select Enrollment Policy screen, under Custom Request, select Proceed without enrollment policy and click Next.
7. On the Custom request screen, for template, choose "(No template) Legacy key" from the drop-down menu, and leave "Suppress default extensions" unchecked.
8. For Request format, select PKCS#10.
9. Click Next.
10. On the Certificate Enrollment screen, click Details, then click Properties. This will open a Certificate Properties dialog box.
11. On the General tab, enter a Friendly name and Description for your certificate.
12. On the Subject tab
13. Select Common Name from the Type menu, enter your fully qualified DNS name for the value, and click "Add >".
14. Optionally, add Subject Alternative name values, if you need them.
15. On the Extensions tab
16. Expand "Key usage" and add "Digital signature" to the selected options, and verify that "Make these key usages critical" is checked.
17. Expand "Extended Key Usage (application policies)" and add "Server Authentication" and "Client Authentication" to the selected options, and verify that "Make the Extended Key Usage critical" is checked.
18. On the Private Key tab
19. Expand Cryptographic Service provider and uncheck "Microsoft Strong Cryptographic Provider (Signature)".
20. Check box for "Microsoft RSA Schannel Cryptographic Provider"
21. Expand Key Options and select "2048" from the Key size drop-down menu.
22. PK use: Exchange
23. If you will need to export the certificate to use on another host, select "Make private key exportable".
24. Click OK.
25. On the Certificate Enrollment screen, click Next.
26. Enter a file name for your CSR (e.g. certname.req), click Enter
27. Select "Base 64" as the File Format.
28. Click Finish.
Post #1555109
Posted Wednesday, April 16, 2014 9:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:30 AM
Points: 1,049, Visits: 1,117
I can't help with your specific Certificate Enrollment process, but if we assume you've managed to generate a valid certificate, which includes the private key, then:

The certificate needs to be imported to:
Local Computer > Personal > Certificates

Also need to check the root trust for the certificate; if this is not correct, then check with your security team. If you use MMC and open Certificates, you should be able to browse to the Personal cert.store and see the certificate (with a nice little padlock indicating the private key is attached).
Post #1562365
Posted Thursday, April 17, 2014 3:24 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:02 AM
Points: 709, Visits: 1,422
Did you get a certificate response from the CA that you are getting the cert issued through? Normally (at least in the org's that I have worked with) you submit the cert request, they take that, generate a cert response and you take that response (in my case it was always an encrypted cert saved as a .cer file) and finish the cert request on the same computer you started the new request on. I believe it is this step that is needed in order to get the private key. You'll know because once the cert request is finished and the certs are imported into the Personal store the cert icon will have the little key symbol in it indicating the cert has an associated private key. Once that is done RS Config Mgr should be able to see and use the cert for SSL.

Joie Andrew
"Since 1982"
Post #1562565
Posted Thursday, April 17, 2014 4:19 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:30 AM
Points: 1,049, Visits: 1,117
Sorry, I meant to say a nice key symbol, not a padlock! Thanks Joie.

The process you've followed seems correct. Unfortunately the Enrollment Wizard is a strange mix of additional windows, drop-down lists, radio buttons, etc.... what a mess!

But I think your steps are right; the end result should be a text file (you can open it in Notepad); the first line should read:
-----BEGIN NEW CERTIFICATE REQUEST-----

And, as Joie says, you need to submit this file to your Security Team, who will then issue the certificate (with private key attached). The cert might have the extension .cer or .crt or .pfx.
Post #1562580
Posted Thursday, April 17, 2014 10:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 PM
Points: 299, Visits: 922
Hi thanks for the responses. I generated a new certificate request file and sent it to our certificate authority. The certificate that was returned did generate a private key properly when installed. I don't know for sure what was different between the first and second requests. The first time, I may not have checked "make private key exportable" which may or may not have made a difference.

Since a valid certificate for SSRS was generated, it seems the original process is valid for anyone looking to do the same thing.

In the case of this certificate, I put the cname as the common name and the actual server name as a subject alternative name.

ps: what I received back was a .p7b file that included the new certificate and the certificate authority root chain.
Post #1562759
Posted Friday, April 18, 2014 1:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 4, 2014 3:30 AM
Points: 1,049, Visits: 1,117
Good to hear the certificate request finally worked for you.

I think the steps you defined in your first post are a good guide to follow, and will hopefully help others find their way through the ugly certificate request wizard!
Post #1562935
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse