SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSL Certificate for SSRS


SSL Certificate for SSRS

Author
Message
PHXHoward
PHXHoward
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 Visits: 1236
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.
Andy sql
Andy sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1601 Visits: 1315
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).
Joie Andrew
Joie Andrew
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2299 Visits: 2032
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"
Andy sql
Andy sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1601 Visits: 1315
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.
PHXHoward
PHXHoward
Right there with Babe
Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)Right there with Babe (731 reputation)

Group: General Forum Members
Points: 731 Visits: 1236
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.
Andy sql
Andy sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1601 Visits: 1315
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search