Microsoft® SQL Server® can use Secure Sockets Layer (SSL) to encrypt data that is transmitted across a network between an instance of SQL Server and a client application.
SSL can be used for server validation when a client connection requests encryption. If the instance of SQL Server is running on a computer that has been assigned a certificate from a public certification authority, identity of the computer and the instance of SQL Server is vouched for by the chain of certificates that lead to the trusted root authority. Such server validation requires that the computer on which the client application is running be configured to trust the root authority of the certificate that is used by the server.
For the purposes of this article, the client application that will be configured with an encrypted connection to the database is VMware® vRealize® Orchestrator™. I will show you how to configure vRealize Orchestrator Appliance™ to use an SSL connection when communicating with a Microsoft SQL Server database.
Enabling SSL on SQL Server
Before we can connect vRealize Orchestrator to a SQL Server database using SSL, we need to configure the SQL Server to accept SSL connections. Go to the SQL Server and start the SQL Server Configuration Manager.
Navigate to SQL Server Network Configuration and select the database instance for which you want to configure SSL Communication. Right click on Protocols for <Instance_name>, and click Properties. On the Flags tab, under Force Encryption, select Yes.
On the Certificates tab, select the certificate you want to use for SSL communication, and click OK.
Trusting the SSL Certificate Chain on vRealize Orchestrator
The following screenshots show the certificate that was used in the previous step to force encryption in the SQL Server Configuration Manager.
As you can see, the certificate is issued by a Certificate Authority (CA) called RootCA.
This is the certificate the server will present to any client application trying to establish a secure connection. The client application must be configured to trust the root authority of the certificate that is used by the server. In this example, the client application is vRealize Orchestrator and the RootCA certificate is the one that vRealize Orchestrator must trust.
In order for vRealize Orchestrator to trust the RootCA certificate, we need to import it to the Trusted Certificates store in vRealize Orchestrator.
Open vRealize Orchestrator Control Center and navigate to Certificates, Trusted Certificates.
Import the RootCA certificate.
In this example, the CA certificate chain consists of only RootCA. If you have an intermediate CA, you should import that too into the Trusted Certificates store in vRealize Orchestrator .
Enabling SSL for the vRealize Orchestrator Database Connection
Now let’s configure vRealize Orchestrator to use an encrypted connection to the database.
Open vRealize Orchestrator Control Center and navigate to the Configure Database tab.
Fill in the database information, select Use SSL, and click Save Changes.
Go to the Startup Options tab and restart the vRealize Orchestrator Server Service.
We have now successfully configured vRealize Orchestrator to use an encrypted SSL connection to a Microsoft SQL Server database.