Adding a Server Certificate to Civis for a Database Sync

Overview

If your database uses a server certificate to validate server authenticity for SSL connections, then you will need to add that server certificate to Platform so that Civis can validate its connections to your database. In this document, you will learn how to add your server certificate as a credential.

Steps

In order to add a server certificate for your database sync, you will need to perform the following steps.

Prerequisite Actions

In order to add a server certificate, you will need to have a copy of the certificate as a PEM file. The method for acquiring such a file varies depending on which database management system is in use, as well as whether the database is self-hosted or cloud-provided. Because these options are too manifold to fully describe, please consult with your IT team to determine the best way to get a PEM file; the rest of this guide will assume that you have one. 

Adding the Certificate to Platform

1. Navigate to your “Credentials” page in the Platform UI, under the “Admin” dropdown.
   
   
   
   The Location of the “Credentials” page in the Civis Platform UI, under the “Admin” dropdown in the top-right corner of the page.
   
   
2. Click on the “New Credential” button in the top right corner.
3. For the type of the new Credential, choose “Certificate”.
4. The username field is unused for this type, so place any value there.
5. Place the contents of your PEM file in the “password” field. The value should start with a line that says “-----BEGIN CERTIFICATE-----”, and end with a line that says “-----END CERTIFICATE-----”. There may be multiple entries in the server certificate, as indicated by multiple blocks of text between pairs of the above delimiting lines. Please ensure that all values are present. If the certificate is not included in its entirety, Civis Platform may fail to connect to your database.
6. Ensure that the credential is connected to your database by selecting its name from the “Remote Host” dropdown. If the certificate is not linked to the database, then it will not be used in connections from Civis Platform.

 When completed, your credential form should look something like this:

Completed credential form for an SSL Certificate in Civis Platform.

Adding SSL to Your Database URL

Now that the certificate is present in Civis Platform, future attempts to connect to the database will detect that the certificate is present and make it available when Civis attempts to connect. However, you will also need to ensure that your database knows to attempt server verification when establishing an SSL connection. To do this, you will need to add additional parameters to your database URL in Civis Platform. This is, again, dependent on your database management system and the version of your database management system:

• For MySQL databases: add either “sslMode=VERIFY_CA” or “sslMode=VERIFY_IDENTITY” to your database URL. "useSSL” and “verifyServerCertificate” are now DEPRECATED for the version of the Driver used in Platform. Database URLs that were created in Platform before April 2020 with the old settings have been automatically migrated to the new settings. For more info, see The MySQL documentation.
• For SQL Server databases: add “encrypt=true” and “trustServerCertificate=false”. The “hostNameInCertificate” may also need to be specified. For more info, see The Microsoft SQL Server documentation.
• For PostgreSQL: add “ssl=true”, “sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory” and “sslmode=verify-full” or “sslmode=verify-ca” to your database URL. For more info, see The PostgreSQL documentation.
• For Redshift: add “ssl=true” and “sslmode=verify-full” or “sslMode=verify-ca” to your database URL. For more info, see The Redshift documentation.
• For Oracle: make sure your URL is following Transparent Network Substrate (TNS) formatting with “protocol=tcps”. Example: jdbc:oracle:thin:@(description=(address=(protocol=tcps)(host=your.host.com)(port=port))(connect_data=(service_name=yourDbName)). For more info, see The Oracle documentation.