Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(16 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Configure PowerCenter to connect to Microsoft SQL Server from UNIX
Solution
Do as follows to configure PowerCenter to connect to Microsoft SQL Server from UNIX:

Note

 

Only DataDirect ODBC drivers are supported when connecting to a Microsoft SQL Server database from a UNIX machine.

PowerCenter Integration Service (UNIX)

On the UNIX machine hosting the Informatica Services DataDirect ODBC must be configured as follows:

  1. Install and configure the DataDirect ODBC drivers on the machine hosting the PowerCenter Services (for complete ODBC installation directions refer to HOW TO: Configure DataDirect ODBC on UNIX with PowerCenter (KB 14684)

  2. Edit the existing odbc.ini file (in the $ODBCHOME directory) or copy the odbc.ini file to the home directory and edit it.

    cp $ODBCHOME/odbc.ini $HOME/.odbc.ini

  3. Add an entry for the ODBC data source under the [ODBC Data Sources] section and configure the data source.
    Syntax:
    The following options listed in red are mandatory to configure.
    The rest are optional.

    [ODBC Data Sources]
    SQL Server Wire Protocol=DataDirect 7.1 SQL Server Wire Protocol

    [SQL Server Wire Protocol]
    Driver=<INFA_HOME>/ODBC7.1/lib/DWsqls27.so
    Description=DataDirect 7.1 SQL Server Wire Protocol
    AlternateServers=
    AlwaysReportTriggerResults=0
    AnsiNPW=1
    ApplicationName=
    ApplicationUsingThreads=1
    AuthenticationMethod=1
    BulkBinaryThreshold=32
    BulkCharacterThreshold=-1
    BulkLoadBatchSize=1024
    BulkLoadOptions=2
    ConnectionReset=0
    ConnectionRetryCount=0
    ConnectionRetryDelay=3
    Database=<database_name>
    EnableBulkLoad=0
    EnableQuotedIdentifiers=0
    EncryptionMethod=0
    FailoverGranularity=0
    FailoverMode=0
    FailoverPreconnect=0
    FetchTSWTZasTimestamp=0
    FetchTWFSasTime=1
    GSSClient=native
    HostName=<SQL_Server_host> or FQDN
    HostNameInCertificate=
    InitializationString=
    Language=
    LoadBalanceTimeout=0
    LoadBalancing=0
    LoginTimeout=15
    LogonID=
    MaxPoolSize=100
    MinPoolSize=0
    PacketSize=-1
    Password=
    Pooling=0
    PortNumber=<SQL_Server_server_port>
    QueryTimeout=0
    ReportCodePageConversionErrors=0
    SnapshotSerializable=0
    TrustStore=
    TrustStorePassword=
    ValidateServerCertificate=1
    WorkStationID=
    XML Describe Type=-10

    Note
  4. Verify that the last entry in the odbc.ini is InstallDir and set it to the ODBC installation directory ($ODBCHOME).
    Example:

    InstallDir=/apps/Informatica/10.2.0/ODBC7.1

  5. Test the ODBC connection using SSGODBC (refer to article 15339: HOW TO: Test an ODBC connection using SSGODBC (KB 15339).

PowerCenter Client (Windows)

On the Windows machine hosting the PowerCenter client do the following:

  1. In Workflow Manager add a relational connection.
  2. Make the connection type Microsoft SQL Server.
  3. Set the connection properties as follows:
    • Connect String: MY_MSSQLSERVER_ODBC_SOURCE
    • User Name: <SQL Server Database User Name>
    • Password: <SQL Server Database User Password>

     
    TYPE : Microsoft SQL Server​  as ODBC is not supported any more.

  4. Use this relational connection in sessions connecting to this database.
​​​
More Information

SQL Server Legacy ODBC Driver

The syntax for DataDirect SQL legacy driver is as follows:
[ODBC Data Sources]
MY_MSSQLSERVER_ODBC_SOURCE=<Driver name or Data source description>
[MY_SQLSERVER_ODBC_SOURCE]
Driver=<Path_To_SQLServer_ODBC_Driver>/DWmsssxx.so
Description=DataDirect 7.1 SQL Server Wire Protocol
Database=<SQLServer_database_name>
LogonID=<username>
Password=<password>
Address=[<TCP/IP_Address>,<Port_Number>][<SQL_server_host_name>\<instance_name>]
QuoteId=No
AnsiNPW=No
ApplicationsUsingThreads=1

Examples

SQL Server Host Name and Port

If the SQL Server database server is sql_server_1 running on the (default) port of 1433 enter the following:

[ODBC Data Sources]
SQLSERVER_1=DataDirect 7.1 SQL Server Wire Protocol

[SQLSERVER_1]
Driver=/apps/Informatica/10.2.0/ODBC6.1/lib/DWmsss27.so
Description=DataDirect 7.1 SQL Server Wire Protocol
Database=Master
LogonId=target_user
Password=target_pw
Address=sql_server_1,1433
QuotedId=No
AnsiNPW=No

SQL Server Host Name and Named Instance

Use the syntax below for Address to connect to a Microsoft SQL Server Named Instance (for dynamic ports):

Address=<SQL_server_host_name>\<instance_name>

If the instance name is INFASQL2K5 and is on the server tswin2003 enter the following for Address:

[ODBC Data Sources]
SQLSERVER_1=DataDirect 7.1 SQL Server Wire Protocol

[SQLSERVER_1]
Driver=/apps/Informatica/10.2.0/ODBC7.1/lib/DWmsss27.so
Description=DataDirect 7.1 SQL Server Wire Protocol
Database=Master
LogonId=target_user
Password=target_pw
Address=tswin2003\INFASQL2K5
QuotedId=No
AnsiNPW=No

 In certain cases,  the hostname is not resolved correct and FQDN or FQ hostname has to be used.
Reference
For more information, see PowerCenter Configuration Guide > "Connecting to Databases from UNIX" > "Connecting to an ODBC Data Source"
Applies To
Product: PowerCenter
Problem Type: Configuration
User Type: Administrator; Developer
Project Phase: Configure
Product Version: PowerCenter
Database: Microsoft SQL Server; ODBC
Operating System: AIX; HP-UX; Linux; Solaris
Other Software:
Attachments
Last Modified Date:1/9/2020 9:33 PMID:15373
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)