Microsoft SQL Server connector

Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet).

Supported platforms	Supported versions
Unix Linux Windows AWS RDS Azure SQL Azure Managed Instance Azure SQL Data Warehouse Google Cloud SQL	2012 2014 2016 2017 2019 2022

Supported features

TLS connector setup for Microsoft SQL Servers

A TLS connection with SQL Server depends on correctly setting JDBC driver properties which correspond to your specific SQL Server instance configuration. These properties are not Delphix specific and there are a large number of possible combinations of properties related to TLS. Below is only an example which may work in some very simple configurations.

Before you begin

Obtain the following from your DBA:

• The standard connection properties (port, hostname, schema, authentication method, credentials etc.).

• JDBC driver properties required for your TLS enabled SQL Server instance.

• If your DBA has provided JDBC driver properties that include the value key pair trustServerCertificate = false, also obtain the public CA certificates used by the TLS listener as PEM encoded files.

• If you obtained PEM encoded CA certificates, import these into the Delphix appliance truststore.

  1. The truststore is managed by the Delphix appliance sysadmin user in the Setup UI. Note that if you are given multiple PEM files instead of one big one (one for the root and additional for each intermediate certificate), you can paste the contents all together into the same import box. See TrustStore settings for more details.

  2. Restart the Delphix appliance to force a reload of the truststore.

Creating the Connector

1. Using the JDBC driver properties provided by your DBA, create a plain text file containing unquoted value keypairs. You will not need parameters like schema, hostname, server name, database name etc. These are provided directly into Connector fields in step 2. Below is an example of a very simple properties file for connecting to an instance with no server authentication or additional TLS related parameters:

   Copy

   encrypt = true
   
   trustServerCertificate = true

2. Create a basic Microsoft SQL Server connector in Continuous Compliance with the standard connection properties.

3. When prompted to upload a properties file, provide the file created in step 1.

4. Validate the connectivity with the Test Connection button. If the connection works Save the connector. If not, you may need to work with your DBA to determine what configuration changes are required.

Google Cloud SQL IAM Authorization setup

To authorize connections from Continuous Compliance to a Google Cloud SQL Microsoft SQL Server instance, do the following:

Before you begin

1. Provision a Google Compute Engine running Continuous Compliance. In the compute engine’s settings, enable Cloud SQL in the Identity and API access section.

2. Collect the required JDBC and Properties details.

Creating the Connector

1. Using the JDBC driver properties collected above, create a plain text file containing unquoted value keypairs.

   Example of a Properties File:

   Copy

   socketFactoryClass=com.google.cloud.sql.sqlserver.SocketFactory 
   
   socketFactoryConstructorArg=<connection name of the SQL Server instance from the 
   
   Google Cloud web console>

2. Create an Advanced Microsoft SQL Server connector in Continuous Compliance with the following JDBC URL:

   Copy

   jdbc:sqlserver://localhost;databaseName=<your-database>;encrypt=false

3. When prompted to upload a properties file, provide the file created in step 1.

4. Validate the connectivity with the Test Connection button.

   • If the connection works Save the connector.

   • If not, you may need to work with your DBA to determine what configuration changes are required.