Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

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

Feedback

HOW TO: Configure DataDirect ODBC on UNIX to connect to a SQL Server Database using SSL
Solution

You can connect from UNIX to a SQL Server Database with SSL using the DataDirect SQL Server Native ODBC driver (DWsqlsxx.so).

To configure DataDirect ODBC on UNIX to connect to a SQL Server Database using SSL, do the following:

  1. Configure the DataDirect ODBC driver on the machine hosting the Informatica Services.

  2. In the odbc.ini file add an entry for the SQL Server ODBC data source under the [ODBC Data Sources] section using the DWsqlsxx.so driver.
    This driver is supported starting with PowerCenter 9.1.0 HotFix 2 and later releases.

    For complete details on configuring the DataDirect New SQL Server ODBC driver refer to HOW TO: Configure PowerCenter to connect to a Microsoft SQL Server ODBC from UNIX (KB 15373)

  3. Enter the configuration for the DSN with the following options set:

    • EncryptionMethod=1
    • PacketSize=32
    • (OPTIONAL) TrustStore= <location_of_truststore_file>
    • (OPTIONAL) TrustStorePassword=<password used to access the truststore file>
    • ValidateServerCertificate=0 or 1
Note

If SQL Server is NOT using a CA certificate, make sure to set ValidateServerCertificate=0.


If you are using certificates to validate the connection you must have the following set:

  • ValidateServerCertificate=1
  • TrustStore= <location_of_truststore_file>
  • TrustStorePassword=<password used to access the truststore file>

Example

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

More Information
In certain cases, the hostname is not resolved correct and FQDN or FQ hostname has to be used.
Reference
Informatica PowerCenter Installation and Configuration Guide > "Appendix B Connecting to Databases from UNIX" > "Connecting to Microsoft SQL Server from UNIX" > "Configuring ODBC Connectivity"


Applies To
Product: PowerCenter
Problem Type: Connectivity; Configuration
User Type: Developer
Project Phase: Configure; Implement
Product Version: PowerCenter
Database: Microsoft SQL Server
Operating System: AIX; HP-UX; Linux; Redhat Linux; SUSE Linux ES; Solaris; UNIX
Other Software:
Attachments
​​
Last Modified Date:9/11/2019 11:37 PMID:123240
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)