Using Secure Sockets Layer (SSL) for SQL Server 2000 Reporting Services
Boost Data Ltd.
William (Bill) Vaughn
Beta V Corporation
Microsoft® SQL Server™ 2000 Reporting Services
Microsoft® Windows Server System™ 2003
Microsoft® Windows® 2000 Server
Summary: Experiment with the security solutions presented here using SSL for SQL Server 2000 Reporting Services before implementing security on your network. (37 printed pages)
What Is SSL and Why Is It Important?
SSL and Reporting Services
Getting an SSL Certificate
Obtaining a Web Server SSL Certificate
Caveat About Stand-alone Certificate Authority
Installing Reporting Services
While writing our new book on Microsoft SQL Server Reporting Services, Hitchhiker's Guide to Microsoft Reporting Services for SQL Server 2000 (Addison Wesley) (see http://www.sqlreportingservices.net), we decided not to include some of the results of our research. However, we felt that it would make a good candidate for MSDN, so the text of that research comprises the bulk this MSDN article.
As we continue writing our book, one of our primary concerns is security. It seems to be on everyone's mind nowadays. Security can mean a variety of things, such as the ability to keep confidential information confidential. This is an expansive topic that could range from physical server security, to network security, to restricting access to login names and passwords, and beyond. All the locks in the Pentagon won't protect your data if you're not vigilant about how the new Microsoft SQL Server 200 Reporting Services is installed—and that's the reason for this article.
Security can be a very large and daunting topic. It's often difficult to understand and more difficult to get right. In our experience, developers often feel that they're out of their area of expertise when people start discussing Public Key Infrastructure (PKI), Root Certificate Authorities, Trusts, Certificates, Secure Sockets Layer (SSL), and the like. What we want to do here is give you some easily digestible information that can go a long way toward increasing your understanding of security-related issues and tools available to you. We provide several experiments that you can try in your development environments before possibly implementing the code or configuration on production environments. This enables you to secure the Web site where you intend to install Reporting Services.
In particular, we'll show you how you can install a Root Certificate Authority and how you can request Certificates to secure a Web site. We'll also discuss where it is appropriate (if not just convenient) to spend $500 (USD) a year for the benefit of a Web server Certificate from a publicly trusted Certificate Authority.
We'll discuss securing an IIS server Web site—so you'll need administrative rights to the IIS server. In addition, if you're going to install and then issue certificates from your own domain "Enterprise" Root Certificate Authority, you'll need local admininstrator permissions, enterprise administrator permissions, and root domain administrator permissions in the forest. This means that if these server and network rights elude you, go and talk to those within your organization that manage your company domain and scare them silly. Tell them that you're planning to tear down the production network and shred the Active Directory to implement a PKI solution. They'll politely thank you for the warning, and help you clear your desk as the security guards manhandle you to the exit or nearest window. Seriously though, in a production environment, your organization may already have a PKI plan (and if it doesn't, it probably would benefit from one). You'll probably want to be part of that, rather than going it alone. Network Administrators are people that have the ultimate power to stop you by removing your network access—so don't get on their wrong side in a production environment when you're experimenting with the configurations we're going to show you here.
SSL is the acronym for "Secure Sockets Layer". You use it every time you go to a secure Web site to order memory, books, or an airline ticket.
When the URL starts with https://, you're using SSL. In Internet Explorer, when you are viewing https:// Web pages that are protected by SSL, you'll see the little lock symbol on the Status bar as in Figure 1.
Figure 1. The lock symbol in the Internet Explorer browser Status bar
Basically, the Web server encrypts the raw https data being moved over the network (the World Wide Web) so that "evil doers" out there can't sniff the wire and extract important information. Https:// pages use keys embedded in the Web server's SSL certificate and encrypt the network traffic between browser and server. Http:// pages don't—they leave everything in plain text while in transit on the network. This makes the data about as hard to read as this article. In addition to encryption, an SSL Web Server Certificate also provides validation and verification of the remote server identity to the browser client.
When you install Reporting Services on a system, one of the first things the Setup dialog asks (Figure 2) is whether it should use SSL to gate access to the Reporting Services and encrypt the data and credentials being moved over the wire. Your answer had better be "Yes". If it isn't, you might have a lot of explaining to do to your boss or the stockholders when important company information is compromised.
Figure 2. The default SSL check box in the Reporting Services Installation wizard
The Setup program even warns you if you clear the check box. We encourage all of our readers and customers to enable SSL in virtually all cases. Sure, it means that the reports run a bit slower, as the Web server must decrypt and encrypt data being sent to and from the browser, but this is a small price to pay for better data security.
Figure 3. If you don't select the "Use SSL" option in the Setup dialog, you will get a final warning.
To enable SSL on your Reporting Services Web site, you simply need to install an SSL "Web Site Certificate". This certificate contains information about your Web site, such as who owns it and when it is valid. Figure 4 shows the "Certificate" we created to the Web site where we installed Reporting Services to use SSL within the closed world of our development Active Directory domain, "betavlocal".
Figure 4. A completed SSL Certificate created for our Reporting Services Web site
How Does Reporting Services Use SSL?
Reporting Services executes queries against specified data sources as it generates the reports. This means it's going to need login credentials (Login ID and Password). There are several ways these credentials can be referenced, including:
- Specifying the credentials explicitly within a report itself
- Storing the credentials within a shared data source
- Using the credentials of the user running the report (SSPI)
- Querying the user for appropriate credentials
- Passing credentials to use in the URL
- Extracting credentials via the POST verb of an http:// command
The last three credential references are particular risky, because if the credentials are passed from the browser over http:// (effectively in plain text), they might be intercepted and harvested with relative ease. (Querying the user for appropriate credentials doesn't send the credentials in plain text unless 'basic authentication' is also set on the Web site.) To raise the security bar, you'll want to be able to ensure that login credentials should only be transmitted while accessing the Report Server over an https:// connection, where the Web server takes steps to encrypt the traffic.
The Report Server exposes Reporting Services' SOAP endpoint, and Visual Studio® .NET deploys the Report by simply making calls to public SOAP methods. At some point you might want to create client software to utilize the SOAP Web methods on the Report Server other than using the URL-based interface. The SOAP interface is the primary interface to Reporting Services—these Web methods expose everything Reporting Services can do. A custom application written against these methods could create and launch reports or lots of other custom operations, such as create and manage report schedules, adjust management group permissions, and more. Within the RSReportServer.config file you'll find in "\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer", there is a key attribute, SecureConnectionLevel, and this effectively governs which SOAP methods can be called over http and which can be called over https. We discuss appropriate values for this attribute at the end of this article, but we call to your attention the ListSecureMethods() method on the SOAP interface that identifies those methods that must (depending upon the SecureConnectionLevel setting) be called over https. If you need to know more about this, look up "ListSecureMethods" in Reporting Services Books Online—or buy our book.
The Report Manager provides a cool interface for managing reports, and it is possible to use the Report Manager to configure shared data sources and reports that have embedded credentials. If you are accessing the Report Manager over http://, then those credentials are easily harvested as they are being passed back to the Report Server, so you'll really want to be accessing the Report Manager over https://. We tell you the changes to make at the end of this article.
Hopefully, you've been convinced that you need an SSL Certificate, but now you will want to know how to get one. There are two approaches; use your own Certificate Authority by installing Microsoft Certificate Services, or use a public root Certificate Authority such as Verisign or Thawte. The route you choose probably depends on who you want to trust your Web site.
When browsing to a Web site that has an SSL Web Server Certificate that has been provided by an untrusted Certificate Authority, Figure 5 shows the dialog that greets, or more likely scares away, your users.
Figure 5. A Security Alert dialog exposed by the Internet Explorer Web browser when an https site is not trusted
Trust is at the heart of the issue here, as certificates are issued from Certificate Authorities and the operating system has to make a decision—whether or not to trust that a particular certificate has been cryptographically signed by who the certificate says it is signed by. By default, all Microsoft® Windows® operating systems maintain an internal database (which is updated by the Windows Update Service) of all public root certificate authorities whose (cryptographic) certificate signatures will be trusted by all Windows computers, and are kept current via Windows Update.
Windows computers that are members of a particular Active Directory domain will also by default trust any certificates that are issued by any Enterprise Certificate Authorities installed and registered within that particular Active Directory domain.
Obtaining a Web server SSL certificate from a public root certification authority can cost $500 per year. In addition, the organization that sells you the certificate will most probably take steps to ensure that you are who you say you are and that you have the legal right to have the certificate for which you are applying. They are the well-paid guardians of security.
While you are learning and testing, you are unlikely to want to fork out $500 just for a certificate that you're going to throw away and not use in production. We suggest that you might like to follow the steps we discuss here to set up your own Certificate Authority—even if only temporarily during the development phase.
Installing Your Own Certificate Authority
Generally, there are two configurations for the out-of-the-box Windows Server Certificate Authority that you can set up. One is integrated within an Active Directory (an Enterprise Root Certificate Authority), and the other is a stand-alone Certificate Authority. For each of those configurations you can determine if the Certificate Authority is a Root Certificate Authority (a Certificate Authority that is not signed by another Certificate Authority) or as a Subordinate Certificate Authority that needs to obtain a certificate itself from another Certificate Authority before it can start to issue any certificates.
We're going to talk about installing a Root Certificate Authority—one that's not signed by another Certificate Authority. On the Windows 2000 Server or Windows Server™ 2003 Control Panel, click the Add/New Programs icon, click Add/Remove Windows Components, and then choose to add Certificate Services. You'll see a dialog box similar to that shown in Figure 6 (in which we are using Windows Server 2003; there are a few minor cosmetic differences from Windows 2000 Server).
Figure 6: The Add Windows Components dialog box
Once you have installed Certificate Services, the installation wizard warns you that you will be unable to rename the computer, or join or leave a domain.
The next step of the installation asks you to choose which type of Certificate Authority (CA) you wish to install, as shown in Figure 7. If you have an Active Directory domain, you can select an Enterprise Certificate Authority. If this is going to be the first CA in your domain, then you'll want to choose the Enterprise Root CA. If you don't have an Active Directory domain, you can choose to install a stand-alone CA, and if it's to be the first Certificate Authority, then you'd choose the stand-alone root CA. (Be advised that if you install a stand-alone CA that, by default, no one at all will trust any of its certificates—not even machines within the Active Directory domain—than you are going to have to take additional steps at each machine that you want to be able to trust its certificates.) On Windows 2000 Servers, the main difference (as we'll see later) comes when asking the Server for a Certificate—stand-alone Certificate Authorities on Windows 2000 Servers cannot be configured to automatically issue a certificate in response to a request. It requires the intervention of a domain administrator to explicitly issue certificates requested, but we'll see that in just a minute.
Figure 7. Choosing a Certification Authority type
On Windows Server 2003, the Certificate Authority Identifying Information collected by the wizard is pretty tersely restricted to the just the Common name and Validity Period (Figure 8). However, on Windows 2000 (Figure 9), there are a few more identifying fields, and this information will be presented to the user when it comes time for the user to trust certificates generated by the CA.
Figure 8. Identifying the name and validity period of a Windows Server 2003 CA
Figure 9. Identifying the name and validity period of a Windows 2000 CA
And that is all. You can click through to the end of the wizard. The wizard will stop IIS server and install a certificate services ISAPI to enable you to ask for certificates via a Web interface. (If you are on Windows Server 2003 and you have not enabled Active Server Pages, then the wizard will prompt you about that, and yes, you need them.) That interface will be on http://<server name>/certsrv.
By the way, make sure that if you installed onto Windows 2000 Server, when accessing the Certificate Services Web interface, that you always use lower case for certsrv. There is a little quirk that means if you don't, it doesn't work properly. Thankfully, that quirk has been fixed in the Windows Server 2003 version.
Now that we have a Certificate Authority installed, let's get a Certificate for our default Web site that can then enable us to install Reporting Services with SSL checked. We practice safe computing in our enterprises and we advocate to developers that they should do the same. One of the things we ensure is that, while our own personal accounts have administrative rights over our own particular development domain workstation, our own personal user accounts don't have administrative rights per se on the domain. We mention this because when it comes to requesting a certificate from an Enterprise Certificate Authority integrated with the Active Directory, the ordinary policy is only to accept requests and issue certificates for Web server SSL certificates from accounts that have domain administrative privileges. This means you can either log in as a domain administrator, or you can use Run as..., as shown in Figures 10 and 11, and run the IIS MMC snap-in under the credentials of a domain administrator.
Figure 10. Starting the IIS MMC snap-in using "Run as..."
Figure 11. Providing domain administrator credentials to "Run as..."
Once the IIS MMC snap-in is launched, navigate to the default Web site and open its properties, as shown in Figure 12.
Figure 12. Navigating to the Web site properties page dialog box
On the Directory Security tab, press the Server Certificate button, as shown in Figure 13.
Figure 13. The Web Site Properties page with the Directory Security tab selected
This launches the Web Server Certificate Wizard, as shown in Figure 14.
Figure 14. The Welcome page of the Web Certificate Wizard
We're creating a new Certificate, so we select Create a new Certificate, as shown in Figure 15.
Figure 15. Creating a new certificate in IIS Certificate Wizard
At this point we might have an opportunity to Send the request immediately to an online certificate authority, as shown in Figure 16. What this really means is that Active Directory has noticed that there is a Certificate Authority in the Active Directory domain that can be a source for a new certificate. If you didn't install an Enterprise Certificate Authority, this option will be grayed out. If you wish to request a certificate from a public certificate authority, select to Prepare the request now, but send later. This will prepare a request file for you. For our example, we're going to Send the request immediately to an online certification authority, but keep reading, as the next few steps are the same regardless of which route you take.
Figure 16. Sending the request immediately in IIS Certificate Wizard
Now you need to give the certificate a name, as shown in Figure 17. This is not very important, as it is only a memory aid to enable you to identify the certificate. What is important is the bit length. The larger the bit length the more difficult it is for a malevolent person to decrypt any intercepted network traffic. However, the longer the bit length, the more load you put on the server encrypting and decrypting. It's a compromise.
Figure 17. Naming the site and setting the encryption bit length in IIS Certificate Wizard
The next page of the wizard, as shown in Figure 18, asks for other identifying information of the Organization and the Organizational Unit. Again, these are not terribly important to the functionality; they're just identifying information that will get embedded into the certificate.
Figure 18. Naming the organization in the IIS Certificate wizard
Probably the most important part of the certificate request to get right is in naming the site. This name gets embedded in the certificate's Common name property. By default, the wizard picks out the machine's NetBIOS name. This is all very well if the site is only going to be used with https on an internal network where NetBIOS names might be resolvable. Reporting Services version 1 was not really designed for Internet deployment scenarios, but even in intranet situations you may find that NetBIOS names are not always resolvable. In fact, if you've got the SSL checked during install, it is the NetBIOS name that is embedded into the configuration files irrespective of what you choose here.
If you decide to use a NetBIOS name as the common name, as shown in Figure 19, we've found that putting the name in lowercase leads to fewer problems later on. We chose to use a full DNS name, "d1.internal.boost.net", which identifies a machine on our internal intranet networks, and means that when addressing that machine over https, we will need to do so in the form https://d1.internal.boost.net. We advise folks to use a name that is resolvable on all parts of their interconnected networks. Because we are requesting a non-NetBIOS name into the common name on the certificate, we are going to have to make changes to the Reporting Services .config files, so be sure to read the section below on the .config files.
Figure 19. IIS Naming the site in the Certificate Wizard
Next, the Wizard collects more identifying information, as shown in Figure 20, that's not crucial but will get embedded into the certificate issued.
Figure 20. Providing geographical information in IIS Certificate Wizard
If we have an Enterprise Certificate Authority in the Active Directory domain, the wizard lets us choose which Certificate Authority within the Active Directory domain is to source the certificate, as shown in Figure 21.
Figure 21. Identifying a certificate authority in IIS Certificate Wizard
Or, if we wish to request a certificate from a stand-alone certificate server or from a public root certificate authority, then at the point shown in Figure 16, we'd have selected Prepare the request now, but send it later. Instead of getting a certificate authority to choose from, we'd have the option to choose where to save the request file, as shown in Figure 22.
Figure 22: Providing a filename to save the certificate request in IIS Certificate Wizard
Accordingly, before completing the wizard, you'll see either of these confirmation dialogs shown in Figures 23 or 24:
Figure 23. Confirming the online Enterprise Certificate Authority to request a certificate from within IIS Certificate Wizard
Figure 24. Providing a filename to save the certificate request in IIS Certificate Wizard
And if you were requesting a certificate from an online Active Directory-integrated certificate server, then hopefully you saw the confirmation screen shown in Figure 25 and your certificate installation is complete. (You can skip ahead to the testing section.)
Figure 25. Completing the IIS Certificate Wizard
If you used a request file, you need to take the request file created, as shown Figure 26, and submit it manually to a certificate server.
Figure 26. Confirming certificate request file creation in IIS Certificate Wizard
If you installed a stand-alone certificate server, you should be able to access that server's Web interface via http://<server>/certsrv. (The certsrv Web interface is also installed for the enterprise-integrated certificate server, although we didn't need to use it, as shown earlier, because we used the Send request to an online certificate authority option.) On the first page of the Web interface, select the Request a certificate link, as shown in Figure 27. (Note: If you are accessing a certificate server on Windows 2000 Server, then the interface is a slightly different. Select the advanced certificate reqeust radio button, and then click Next.)
Figure 27. Accessing the CA Certificate Service via http
Submit an advanced certificate request as shown in Figure 28. (On Windows 2000 Server, choose the "Web server SSL certificates" radio button, and then click Next.)
Figure 28. Submitting an advanced certificate request
Choose to submit a certificate request using a file as shown in Figure 29. (In Windows 2000 Server, choose the radio button option to submit a request using a file, and then click Next.)
Figure 29. Submitting a certificate request
Use Notepad to open the request file certreq.txt that the wizard created earlier. Copy the contents including the "-----BEGIN NEW CERTIFICATE REQUEST-----" and "-----END NEW CERTIFICATE REQUEST-----" lines to the clipboard to make them ready to paste into the Web Form, as shown in Figure 30.
Figure 30. Copy the new certificate request to the clipboard.
Paste it into the Web Form as shown in Figure 31, choose the Web Server Certificate template (if you are using Windows Servers 2003), and press Submit.
Figure 31. Paste the new certificate request into the Base64-Encoded Request dialog box.
If you are accessing a Windows Server 2003 certificate server, then you as long as you logged in to the website with a suitable administrator account, then the certificate will normally be automatically issued to you, and you'll be presented with a screen to download the certificate, as shown in Figure 32. (There are policy property settings on the Windows Server 2003 certificate server that permit certificates to be automatically issued to certain users.)
Figure 32. Certificate is issued and ready to download (Windows Server 2003 Certificate Services)
On a Windows 2000 certificate server, however, you'll need to do a little more work, as the Certificate won't be automatically issued. The request has to be manually approved by an administrator on the server, so Figure 33 is the page you'll see when working with a Windows 2000 certificate server.
Figure 33. Certificate pending approval (Windows 2000)
Next you need to get onto the server and launch the Certificate Authority MMC snap-in from the Administrative Tools menu and look up the pending requests. When you find the request for the Web server certificate, right-click, and on the context menu, select to Issue the certificate, as shown in Figure 34.
Figure 34. Certificate Authority MMC snap-in
You can then return browser and retrieve the certificate by checking on a pending request, as shown in Figure 35.
Figure 35. Getting the status of the pending certificate (Windows 2000)
The Certificate Authority returns the saved Certificate request, as shown in Figure 36.
Figure 36. Selecting the certificate to query (Windows 2000)
This returns the status and provides the ability to download, as shown in Figure 37. (Note that this Web Form does have a slight error in that it indicates that it is the CA certificate that you'd be downloading, but in fact it is the Web Server Certificate, not the CA certificate.)
Figure 37. Response from CA (Windows 2000)
Selecting Download CA Certificate will download a certnew.cer file that contains the Web Server Certificate that you requested. This download is a file that will be "expected" by the IIS Web Certificate Wizard, as we'll see in a moment. However, if the IIS machine doesn't already trust certificates from the certificate server, then it is probably a good idea to download the CA certification path that includes the SSL Web certificate and the public certificate of the Certificate Authority in a certnew.p7b file.
If you're acquiring your SSL Web server certificate from a public root certificate authority for a certificate, you'll need to follow its specific instructions to send the Certificate Request file, certreq.txt, and it will provide you with your certificate in a file.
Make your choice, and download the file ready to go back to the IIS MMC snap-in. Next, select the default Web site's Properties dialog, navigate to the Directory Security tab, and launch the Web Server Certificate Wizard again, as shown in Figure 38. Notice this time that when you launch the Certificate Wizard it knows that it has an outstanding request.
Figure 38. Re-launching the Certificate Wizard
Unless you want to delete the request, you should process the request, as shown in Figure 39.
Figure 39. Processing the pending request
This gives you the opportunity to locate the downloaded certificate file—either the certnew.cer or the certnew.p7b files, as shown in Figure 40.
Figure 40. Providing the filename containing the CA response
This returns a confirmation dialog verifying that things are as you want them, as shown in Figure 41.
Figure 41. The Certificate summary
At long last, you'll get the completion page, as shown in Figure 42.
Figure 42. Providing the filename containing the CA response
Phew! Now you should have a certificate installed. You should test that your certificate is installed by checking that you can access pages on the default Web site over https, using the NetBIOS name or full DNS name you chose for the common name in the certificate.
If you installed a stand-alone Certificate Authority, no machine will by default trust certificates it issues. This means that you're going to have a potential deployment issue to address. You'll have to figure out how to enable each and every client machine to trust the certificates. Yes, there are mechanisms and contortions that you can go through. For example you can navigate from each machine back to the http://<server>/certsrv interface and select to retrieve the CA certificate from the first certsrv Web page. You'll then need to select the link to install that Certification path, and to install that certificate to be trusted on each client machine, you'll need local administrator permissions on the client machine to install the CA certificate in the local store.
There are also other mechanisms to permit a stand-alone root certification authority to be trusted, such as using SMS server, for example. However, it's all a BIG hassle isn't it? It's so much easier to pay $500 and get a certificate from a public root certificate authority—especially if you need to sign a Web site with a certificate that can be trusted outside of an active directory domain.
Now you'll be able to complete the installation of Reporting Services and with the Use SSL check box selected, which is the reason for this article in the first place.
Reporting Services Configuration Files
If you used anything other than the NetBIOS name in the common name for the Web Server Certificate, then you'll need to make several changes to some configuration files to reflect the non-NetBIOS name in the URLs. Also, if you've used the NetBIOS name, you might find it helpful to change the embedded http:// URLs to your server over to https:// URLs. The following paragraphs detail these changes.
In RSReportServer.config, which you will find in \Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer, you'll need to locate the <URLRoot> element and update the NetBIOS name to the DNS name, and also the http:// to https://. Take care to use the same case for the letters as you used in the certificate:
So, in our example here we needed to edit the <URLRoot> to be:
You'll find RSWebApplication.config in C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager. The change needed here is to update <ReportServerUrl> to include the name used in the certificate.
Finally, the following line in RSReportServer.config governs the master control over SSL:
<Add Key="SecureConnectionLevel" Value="2"/>
Acceptable values are:
- 3 Most secure—Use SSL for absolutely everything.
- 2 Secure—Use SSL for rendering and methods that pass credentials but don't insist on it for all SOAP calls.
- 1 Basic Security—Accepts http but rejects any calls that might be involved in the passing of credentials.
- 0 Least Secure—Don't use SSL at all.
2 is the value that the installation wizard will input if you install with the Use SSL check box selected, but we prefer to use 3 and ensure that the Report Manager is also using SSL.
That's all, folks! Don't forget if you are editing and deploying reports in Visual Studio .NET 2003, you need to put your https:// URL into the TargetServerURL once you have SSL installed, as shown in Figure 43.
Figure 43. Setting the TargetServerURL in the Reports Property Page