The objective was to configure SSL access to a SQL 2012 Reporting Services server in Native Mode. The Certificate Issuer was a Certificate Authority created in the domain. On SSRS 2005 this was a piece of cake, go into IIS (version 7), double click on the Server Certificates icon and then select the action on the right "Create Domain Certificate...". If you are used to creating CSR requests in IIS then this is familiar territory. For the Online Certification Authority you select your CA and that's about it. But SSRS on 2012 is something altogether different. Back on SQL 2008 Microsoft stopped using the IIS web server for SSRS and switched to using a web server packaged with SSRS. You can install IIS and do it the old way, I suppose, but then your Report Server becomes dependent on the IIS services, and I wanted to avoid that. I didn't find a good source through all my googling to help with setting up SSL this way so maybe this posting will help others that want to do the same thing and not burn through the better part of a day sorting out the ins and outs of how to get it done.
The main steps are these:
1. Create a Web Server Template Certificate on your CA server and give you SSRS server "enroll" permissions to it.
2. Create an SPN for your SSRS server with the DNS name you intend to use to access the report server.
3. Create a certificate on your SSRS server using the Certificates mmc snap-in configured for the Computer account.
4. Use the Reporting Services Configuration Manager to bind the new certificate to the server's IP address(es)/port.
Sounds easy, right? Ok, so here's the long winded, fairly detailed version.
1. For starters you have to have Certificate Services installed on a server in your domain already. I'm not detailing those steps since you can find instructions for that in technet. http://technet.microsoft.com/en-us/library/cc772393(v=ws.10).aspx. So if you have that done already you can start with step one here. On your CA server you open Certification Authority under Administrative Tools. Under Certificate Templates you'll see the list of certs that your CA server is already configured to issue. What you need to do is create a copy of the Web Server certificate template and then configure your CA server to issue that new template.
Right click on Certificate Templates and select Manage. On the Certificate Templates Console that opens right click on Web Server and select Duplicate Template. If your CA server(s) are all Server 2008 and above you can select to create a template for Windows server 2008 Enterprise, otherwise select Server 2003 Enterprise. The "Properties of New Template" window opens next and you can accept most of the defaults. You'll want to give the template a useful name like SSRS Web Server. And on the Security tab you need to give the server running reporting services "Enroll" permission. When adding the server be sure to remember to change the Object Types to include Computers. Your server's AD account should then show up (e.g. DBServer$).
With that template created you can now tell your CA server to issue those certificates. Close the Certificate Templates Console then right click on Certificate Templates again and select New -> Certificate Template to Issue. Select your new template and click OK. You should now see your new certificate in the list on the right. The Intended Purpose should say Server Authentication, like the Web Server template. Server Authentication is the requirement for a SSRS certificate. Specifically, OID of 1.3.6.1.5.5.7.3.1 (szOID_PKIX_KP_SERVER_AUTH) is required. I picked up that tidbit off of a forum, http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/a0738d87-1444-48b8-ab3f-5834de24f0b2/, thank you Jinchun Chen.
2. Next we have to create an SPN for the Report Server. The command line utility for doing this is setSPN.exe. Server 2008 R2 has this utility built in. I think Server 2008 does as well. Server 2003 didn't have it, you had to download the Support Tools. What you're doing with setSPN is creating a Service Principal Name for your Reporting Services server in Active Directory so that the alias for your website is a valid Active Directory name for your server. The syntax is setspn -F -S http/<alias> <servername> (e.g. setspn -F -S http/reports.mydomain.com DBServer). Be sure you add this alias to your internal DNS servers.
3. We are now ready to go to the Reporting Services server and create our certificate. To create the certificate we use the Certificates mmc snap-in configured for the Computer account. My Reporting Services service is configured to use the default account, NT Service\ReportServer$<instancename>. If you use a difference logon account for the server I think you would have to select Service Account instead of Computer Account when adding the Certificates snap-in. Regardless, here are the steps once you have the Certificates mmc loaded:
Right click the Personal folder and select All Tasks -> Request New Certificate...
On the Before You Begin page click Next.
On the Select Certificate Enrollment Policy page Active Directory Enrollment Policy should be selected so just click Next.
On the Request Certificates page you should see two certificate templates in the list, "Computer" and "SSRS Web Server" or whatever you named your template. The SSRS Web Server template should have a yellow icon below it with the message "More information is required to enroll for this certificate. Click here to configure settings." click on that line to open Certificate Properties. ***NOTE - if your certificate template is not displayed in the list you need to go back to your CA server and fix something. If it's not displayed, click the checkbox "Show all templates" to display all the Unavailable certificates and scroll down to your certificate template. If the message with it says something about not having permissions on the certificate template then you need to look at whether your server has Enroll permission for this template.***
On the Certificate Properties page, for Subject name – Type, choose Common name. For the Value use the SPN you created (e.g. reports.mydomain.com).
Still on the Certificate Properties page, for Alternative name – Type, choose DNS. Value is the same as before - the SPN you created.
Click on the General tab and give the certificate a Friendly name and click OK.
Now Back on the Request Certificates page check the SSRS Web Server line and select Enroll.
Under the Personal folder you should now have a Certificates folder with a certificate in it.
4. Now we're ready to go into the Reporting Services Configuration Manager to bind the new certificate to the server's IP address(es)/port. Launch the Reporting Services Configuration Manager app, connect to your instance, and select Web Service URL on the left. Under the SSL Certificate's drop down you should see the Friendly Name of the certificate you just created. The default SSL Port should be 443. Hit Apply if necessary. The new URL should be listed.
Select Report Manager URL on the left and click the Advanced button. In the Multiple SSL Identities for Report Manager section click Add. In the "Add a Report Manager SSL Binding" window that opens choose your certificate from the list and click OK and then OK again. You may have to hit Apply at this point but you should see your new URL in the URLs list (e.g. https://<alias>:443/Reports).
That should be all there is to it. If you open a browser and navigate to the new address with https you should successfully connect to your web server. If you get prompted to supply a username and password and you're using IE9 you might need to add the URL to the Local Intranet Zone. Thanks to this blogger for that tidbit: http://www.soheib.com/technical-knowledge/ssrs-prompt-for-username-and-password-in-ie/
If you get a warning about the website's security certificate you should first check what the certificate says and see if the "issued to" name matches the SPN you created in Active Directory.
I hope you found this blog entry to be helpful. If you're trying this yourself and run into problems feel free to leave a comment and I'll help if I can.
