Administrator Guide

10.5
- 10.5.5
- 10.5.3
- 10.5.2
- 10.5.1
- 10.4.1
- 10.4.0

Back Next

Microsoft SQL Server Connections

You can create a Microsoft SQL Server connection in Test Data Manager to perform data discovery, data subset, data generation, and data masking operations.

Use a Microsoft SQL Server connection to create connections to Microsoft SQL Server and Microsoft Azure SQL databases. To use a Microsoft Azure SQL Data Warehouse database for data masking tasks, use the AzureDWv3 connection type.

The following table describes the database connection properties for a Microsoft SQL Server connection:

Property	Description
Name	Required. Name of the connection. The name is not case sensitive and must be unique within the domain. It cannot exceed 128 characters, start with a number, contain spaces, or contain the following special characters: ~ ` ! $ % ^ & * ( ) - + = { [ } ] \| \ : ; " ' < , > . ? /
Connection Type	Required. The connection type. Select Microsoft SQL Server.
Description	The description of the connection. The description cannot exceed 255 characters.
Use Kerberos Authentication	Enables Kerberos Authentication. You cannot enter a user name and password if you choose Kerberos authentication.
User Name	Required. The database user name.
Use Parameter in Password	Indicates the password for the database user name is a session parameter. $Param Name . Define the password in the workflow or session parameter file, and encrypt it using the pmpasswd CRYPT_DATA option. Used for data subset and data masking operations. Default is disabled.
Password	Required. The password for the database user name.
Owner	The owner of the connection. Default is the user who creates the connection. You can change the owner of the connection.
Metadata Connection String	Required. The JDBC connection URL used to access metadata from the database. Enter jdbc:informatica:sqlserver://<hostname>:1433;SelectMethod=cursor;databaseName=<dbname>. Used for data discovery operations.
JDBC Login Password	Required if Use Parameter in Password is selected. The password for the JDBC user. Used for import from source and data discovery operations.
Code Page	Code page the Integration Service uses to read from a source database or write to a target database or file. Used for all operations.
Data Access Connection String	The connection string used to access data from the database. Enter <servername@dbname>. Used for all operations.
Environment SQL	SQL commands to set the database environment when you connect to the database. The PowerCenter Integration Service runs the connection environment SQL each time it connects to the database. Used for all operations.
Transaction SQL	SQL commands to set the database environment when you connect to the database. The PowerCenter Integration Service runs the transaction environment SQL at the beginning of each transaction. Used for all operations.
Connection Retry Period	Number of seconds the Integration Service attempts to reconnect to the database if the connection fails. If the Integration Service cannot connect to the database in the retry period, the operation fails. Used for all operations. Default is 0.
Domain Name	The name of the domain. Used for all operations.
Owner Name	The name of the owner of the schema.
Schema Name	The name of the schema in the database. Required in the following cases: For the profiling warehouse and staging database if the schema name is different than the database user name When you want to create and run a column profile Used for all operations.
Pass Through Security Enabled	Enables pass-through security for the connection. When you enable pass-through security for a connection, the domain uses the client user name and password to log into the corresponding database, instead of the credentials defined in the connection object. Used for data discovery operations. Default is disabled.
Use Trusted Connection	The Data Integration Service uses Windows authentication to access the Microsoft SQL Server database. The user name that starts the Data Integration Service must be a valid Windows user with access to the Microsoft SQL Server database.
Database Name	Required. Name of the database. If you do not enter a database name, connection-related messages do not show a database name when the default database is used. Used for all operations.
Server Name	Required. Database server name. Used for data subset and data masking operations.
Packet Size	Packet size for the transmission of data. Used to optimize the native Microsoft SQL Server drivers. Used for all operations. Default is 0.

Rules and Guidelines for a Kerberos Environment

Read the following rules and guidelines before you create the connection in a Kerberos environment:

Add the JDBC driver details in the

org.eclipse.virgo.kernel.authentication.config

file. This file is stored in the following location:

<INFA_HOME>/TDM/configuration

The following code represents sample information required:

JDBC_DRIVER_01 {
          com.sun.security.auth.module.Krb5LoginModule required
           doNotPrompt=<Enter true or false>
           useKeyTab=<Enter true or false>
           keyTab="file:<path to the user keytab file>"
           principal="<The user name that must be used as the principal.>"
           storeKey=<Enter true or false>
           debug=<Enter true or false>;
};

If you create the connection in a multi-realm Kerberos environment, the

default_realm

property in the

krb5.conf

file must be the name of the realm in which you install the database.

The

krb5.conf

file contains configuration information for Kerberos. This file is stored in the following location:

<INFA_HOME>/TDM/datadirect

Restart the Test Data Manager Service after you update the files.

Connections

Download Guide