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.

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.
Use Secret Vault
Stores sensitive credentials for this connection in the secrets manager that is configured for your organization.
This property appears only if secrets manager is set up for your organization.
When you enable the secret vault in the connection, you can select the credentials that the Secure Agent retrieves from the secrets manager. If you don't enable this option, the credentials are stored in the repository or on a local Secure Agent, depending on how your organization is configured.
Note:
If you’re using this connection to apply data access policies through pushdown or proxy services, you cannot use the Secret Vault configuration option.
For information about how to configure and use a secrets manager, see Secrets manager configuration.
Runtime Environment
The name of the runtime environment where you want to run the tasks.
SQL Server Version
This property is no longer used. If you select a version, it is ignored.

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.

Advanced settings

The following table describes the advanced connection properties:
Property
Description
Encryption Method
Crypto Protocol Version
Validate Server Certificate
Trust Store
Trust Store Password
Host Name in 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.

Configure SSL with the serverless runtime environment

You can use the serverless runtime environment with Microsoft SQL Server Connector to connect to an SSL-enabled Microsoft SQL Server database.
Before you configure a secure Microsoft SQL Server connection using the serverless runtime environment, complete the following prerequisite tasks to add the SSL certificates to the serverless runtime location:
  1. 1Create the following structure for the serverless agent configuration on AWS or Azure: <Supplementary file location>/serverless_agent_config
  2. 2Add the truststore certificate in the Amazon S3 bucket or Azure container in the following location in your AWS or Azure account: <Supplementary file location>/serverless_agent_config/SSL
  3. 3Copy the following code snippet to a text editor:
    4. version: 1
    agent:
    agentAutoApply:
    general:
    sslStore:
    - fileCopy:
    sourcePath: SSL/<TrustStore_filename>

    where the source path is the directory path of the certificate file on AWS or Azure.
  4. 4Ensure that the syntax and indentations are valid, and then save the file as serverlessUserAgentConfig.yml in the following AWS or Azure location: <Supplementary file location>/serverless_agent_config
    5. When the .yml file runs, the SSL certificate is copied from the AWS or Azure location to the serverless agent directory.
  5. 5In the Microsoft SQL Server connection properties, specify the following certificate path in the serverless agent directory in the Trust Store field: /home/cldagnt/SystemAgent/serverless/configurations/ssl_store/<cert_filename>

Using special characters in the password

When you use SQL Server authentication to connect to Microsoft SQL Server, you can use the following special characters in the password for the database user name:
To use these special characters in the password, you need to set the EnableMSSQLPasswordSpecialChars custom property in the Secure Agent properties.
To configure the custom property in the Secure Agent, perform the following steps:
  1. 1Log in to Informatica® Intelligent Data Management Cloud™ (IDMC).
  2. 2Select Administrator > Runtime Environments.
  3. 3On the Runtime Environments page, select the Secure Agent.
  4. 4Click Edit.
  5. 5In the Custom Configuration Details section, select Data Integration Server as the service and DTM as the type.
  6. 6Enter EnableMSSQLPasswordSpecialChars in the Name field and set the value to true.
  7. 7Click Save.