Connectors and Connections > Data Ingestion and Replication connection properties > Microsoft SQL Server connection properties
  

Microsoft SQL Server connection properties

Create a Microsoft SQL Server connection to read from or write data to Microsoft SQL Server.

Prepare for authentication

You can configure Database or Kerberos authentication method to connect to Microsoft SQL Server.
Before you configure the connection properties, you need to keep the authentication details handy based on the authentication type that you want to use. For Kerberos authentication, you need to configure certain prerequisites.

Prepare for Kerberos authentication

You can use Kerberos authentication to connect to Microsoft SQL Server databases by placing the required configuration files on the Secure Agent machine. You can also use Kerberos authentication to connect to SSL-enabled Microsoft SQL Server databases.
Note: Data Ingestion and Replication does not support Kerberos authentication.
When you configure Kerberos authentication to connect to Microsoft SQL Server, consider the following guidelines:

Configuring Kerberos authentication

Before you use Kerberos authentication to connect to Microsoft SQL Server on Linux or Windows, the organization administrator needs to perform the prerequisite tasks.
  1. 1To configure the Java Authentication and Authorization Service configuration file (JAAS), perform the following tasks:
    1. aCreate a JAAS configuration file on the Secure Agent machine.
    2. bAdd the following entries to the JAAS configuration file:
      3. JDBC_DRIVER_01 {
      com.sun.security.auth.module.Krb5LoginModule required useTicketCache=true;
      };
  2. 2To configure the krb5.conf file, perform the following tasks:
    1. aCreate a krb5.conf file on the Secure Agent machine.
    2. bAdd the details of the Key Distribution Center (KDC) and admin server to the krb5.conf file in the following format:
      3. [libdefaults]
      default_realm = <Realm name>
      forwardable = true
      ticket_lifetime = 24h

      [realms]
      <REALM NAME> = {
      kdc = <Location where KDC is installed>
      admin_server = <Location where KDC is installed>
      }
      [domain_realm]
      <domain name or host name> = <Domain name or host name of Kerberos>
      <domain name or host name> = <Domain name or host name of Kerberos>
  3. 3Set the following environment variables on the Secure Agent machine.
    4. For more information about the required environment variables, see Setting environment variables.
  4. 4Restart the Secure Agent.
  5. 5To generate the credential cache file on the Secure Agent machine and use Kerberos authentication to connect to Microsoft SQL Server, perform the following tasks:
    1. aOn the Secure Agent machine, run the following command and specify the Microsoft SQL Server user name and realm name:
      2. Kinit <user name>@<realm_name>
    2. bWhen prompted, enter the password for the Kerberos principal user.

Setting environment variables

To use Kerberos authentication to connect to Microsoft SQL Server, you need to set the required environment variables on the Secure Agent machine.
Set the following environment variables:
After you set the environmental variables, you need to restart the Secure Agent.
Alternatively, you can add the environment variables when you create a Microsoft SQL Server connection.
To add the environment variables when you configure a connection and use Kerberos authentication, you need to add the KRB5_CONFIG, KRB5CCNAME, and JAASCONFIG properties in the Metadata Advanced Connection Properties field in a Microsoft SQL Server connection.
For example, add the properties in the following format:
KRB5_CONFIG=<Absolute path of the Kerberos configuration file>\krb5.conf;KRB5CCNAME=<Absolute path of the credential cache file>/<File name>;JAASCONFIG=<Absolute path of the JAAS config file>\<File name>.conf
Note: Ensure that you separate each key-value pair with a semicolon.

Connect to Microsoft SQL Server

Let's configure the Microsoft SQL Server connection properties to connect to Microsoft SQL Server databases.

Before you begin

Before you get started, you'll need to get information from your SQL Server DB account based on the authentication method and the type of SQL server DB to which you want to connect.
Check out Prepare for authentication to learn more about the authentication prerequisites.

Connection details

The following table describes the Microsoft SQL Server connection properties:
Property
Description
Connection Name
Name of the connection.
Each connection name must be unique within the organization. Connection names can contain alphanumeric characters, spaces, and the following special characters: _ . + -,
Maximum length is 255 characters.
Description
Description of the connection. Maximum length is 4000 characters.
Type
Type of connection.
Select SQL Server from the list.
Runtime Environment
The name of the runtime environment where you want to run the tasks.
You cannot run a database ingestion task on a Hosted Agent or serverless runtime environment.
SQL Server Version
This property is not applicable for Microsoft SQL Server Connector.

Authentication modes

You can configure one of the following authentication modes to connect to Microsoft SQL Server databases:
Select the required authentication mode and then configure the authentication-specific parameters.
Default is SQL Server Authentication.

Advanced settings

The following table describes the advanced connection properties:
Property
Description
Encryption Method
The method that the Secure Agent uses to encrypt the data sent between the driver and the database server. You can use the encryption method to connect to Microsoft Azure SQL Database.
Default is None.
Crypto Protocol Version
Cryptographic protocols to use when you enable SSL encryption.
Validate Server Certificate
When set to True, Secure Agent validates the certificate that is sent by the database server.
If you specify the HostNameInCertificate parameter, Secure Agent also validates the host name in the certificate.
When set to false, the Secure Agent doesn't validate the certificate that is sent by the database server.
Trust Store
The location and name of the truststore file. The truststore file contains a list of Certificate Authorities (CAs) that the driver uses for SSL server authentication.
Trust Store Password
The password to access the contents of the truststore file.
Host Name in Certificate
Host name of the machine that hosts the secure database. If you specify a host name, the Secure Agent validates the host name included in the connection with the host name in the SSL certificate.
Metadata Advanced Connection Properties
Additional properties for the JDBC driver to fetch the metadata.
Enter properties in the following format:
<parameter name>=<parameter value>
If you enter more than one property, separate each key-value pair with a semicolon.
For example, enter the following property to configure the connection timeout when you test a connection:
LoginTimeout=<value_in_seconds>
Note: The default connection timeout is 270 seconds.
Runtime Advanced Connection Properties
Additional properties for the ODBC driver required at run time.
If you specify more than one property, separate each key-value pair with a semicolon.