Configure SSL for SQL Server Reporting Services 2012

Reporting Services

In this brief post I will show you how I created a self-signed certificate to be used when configuring SSRS 2012 over SSL port 443.

Procedure

First you’re going to need to get makecert.exe and pvk2pfx.exe. Both of these utilities can be downloaded from Microsoft as part of the Windows Driver Kit. I put these utilities on the server on which I was configuring Reporting Services.

Before beginning, you can use the following command (cmd window) to see if there are any SSL Certificate bindings already present:

netsh http show sslcert



If there aren’t any, you should see output like this:
netsh_initial

So, on the the Reporting Services server:

1. Open a command prompt, being sure to run as Administrator
2. Run makecert.exe as admin, specifying the certificate and private key filenames. 

makecert -r -pe -n "CN=" -eku 1.3.6.1.5.5.7.3.1 -sr localmachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12 -sv ".pvk"  ".cer"



You will need to specify a password for the Private Key:

pk_password
3. Create a PFX file using pvk2pfx.exe, again in the cmd window running as Administrator:

"pvk2pfx.exe" -pvk "<Private Key filename>.pvk" -spc "<Certificate filename>.cer" -pfx "<.pfx filename>.pfx" -po "<password used in the above step>"



4. Run MMC from the Start Menu -> Run
5. Go to File -> Add/Remove Snap-in
6. Choose “Computer Account” -> Next
7. Make sure “Local Computer: (the computer this console is running on)” is checked
8. Expand Trusted Root Certification Authorities on left
9. Right click Certificates -> All Tasks -> Import
10. Select PFX file created above
11. Enter password and check “mark this key as exportable.”
12. Be sure cert is going into the certificate store “Trusted Root Certification Authorities”
13. Repeat steps 8-12, using the “Personal” folder instead of “Trusted Root Certification Authorities”
14. Open up Reporting Services Configuration Manager and connect to the instance for which the SSL cert will be used
15. Navigate to the Web Service URL portion of the manager
16. Click on Advanced
17. Remove HTTP identities
18. Add the SSL Identity by clicking Add and selecting the certificate imported earlier
19. Choose the correct SSL port and IP Addresses and click Ok
20. Do steps 16-19 in the Report Manager URL

That’s it! Now you should be able to run netsh again and get output similar to this:
netsh_complete

Problems, Pitfalls, and Errors

I struggled with setting this up, no thanks to some of the errors I was getting. Here’s one of my favorites, thrown during the CreateSSLCertificateBinding method: “An unknown error has occurred in the WMI Provider. Error Code 80070520 —> System.Runtime.InteropServices.COMException (0x80070520): A specified logon session does not exist. It may already have been terminated. (Exception from HRESULT: 0x80070520)”

An unknown error has occurred in the WMI Provider. Error Code 80070520 ---> System.Runtime.InteropServices.COMException (0x80070520): A specified logon session does not exist. It may already have been terminated. (Exception from HRESULT: 0x80070520)


You get this error when your certificate isn’t quite set up correctly. This is a real pain, considering makecert will happily create a certificate that SSRS cannot subsequently use. So, even though you get a certificate that can be imported via the Certificates snap-in for MMC and that shows up in SSRS Configuration Manager, SSRS cannot actually bind the SSL certificate!