Installing an SSL certificate on SQL Server should be a straightforward process, but it can sometimes be challenging. In this article, we will discuss the steps to configure SSL certificates on SQL Server and address some common issues that may arise.

Obtaining the SSL Certificate

Before we begin, it’s important to note that this article does not cover how to obtain an SSL certificate or the cost associated with it. There are plenty of resources available online that provide detailed information on these topics.

For testing purposes, you can download the SSL Toolkit from Microsoft. Make sure to choose the version appropriate for your release of Internet Information Service (IIS). If you are using a current release of XP or Server 2003, you will need version six.

It’s worth mentioning that the SSL Toolkit has a bug that only allows a certificate to be installed on the first site in your IIS server. If your web server is not the first site, you won’t be able to use the SSL Toolkit to install a test certificate.

Installing the Certificate

In previous versions of SQL Server, you could install the certificate into IIS before running the setup program. However, in the latest release, this approach causes the setup program to fail. To avoid this issue, it is recommended to complete the SQL Server installation first and then add the certificate to the IIS server.

Configuring SQL Server

After successfully installing SQL Server, you need to make some configuration changes to enable SSL. Open the file named “rsreportserver.config” and locate the “SecureConnectionLevel” setting. Change the value from the default of zero to two. Additionally, search for the “UrlRoot” tag and update it with the fully qualified name of your reporting server, including the port number if it’s not the default port 80.

Next, open the file named “rswebapplication.config” and update the “ReportServerUrl” with the URL of your report server, without the port number.

Finally, open Internet Manager (inetmgr.exe) and configure the settings to make the page only available with encryption. Select “Require SSL” and “128-bit encryption”. Restart both reporting services and IIS to apply the changes.

Testing and Using the SSL Certificate

If you had previously bookmarked the Report Server and Report Manager URLs, make sure to update them to use “https” instead of “http”. If you forgot to restart IIS, the old links will continue to work until the machine or IIS is restarted.

When you navigate to the URLs for the first time, you may receive a prompt about the certificate from a non-trusted Certificate Authority (CA). Since this is a test certificate, you can view the certificate and install it to avoid further prompts.

After configuring SSL, make sure to check that the little padlock icon is displayed on the status bar of your browser. This indicates that the connection is secure.

It’s worth noting that there may be a bug in the software that prevents certain screens from displaying correctly over HTTPS. If you encounter this issue, manually change the address in the browser’s address bar from “http” to “https” to access the screen.

Conclusion

Configuring SSL certificates on SQL Server can be a complex process, but with the right steps, it can be accomplished successfully. While there may be some challenges along the way, resources like SQL Server Central can provide valuable insights and solutions.

Remember to always configure your help settings to search the internet first for the most up-to-date information. Enjoy exploring the new possibilities that SSL certificates bring to your SQL Server environment!