Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Create MS Sql Server DSN in Linux for IICS Odbc connection
Solution
In order to create a SQL server DSN following things are needed:
  • SQL server driver, we ship Data Direct driver with the Informatica Cloud Secure Agent. One can use either our driver or there own to create DSN and then use it in Odbc connection.
    Location: [infaagent_dir]/drivers/odbc/datadirect/r27/linux/DWsqls27.so 
  • Isql to test the connectivity.
Steps to create SQL Server DSN, are as follows:

1. Login to a Linux server where Secure Agent is installed. 
2. Create an odbc.ini file. Essential entries for Data Direct driver:

[ODBC Data Sources]
SQL Server Wire Protocol=DataDirect 8.0 SQL Server Wire Protocol Driver

[DSN_NAME]
Driver=<Driver's absolute path>
Database=<Database_Name>   
HostName=<Host_Name>
PortNumber=1433

Example

[SQLSERVER_ODBC]
QEWSD=2458308
Driver=/root/infaagent/drivers/odbc/datadirect/r27/linux/DWsqls27.so
Description=DataDirect 8.0 SQL Server Wire Protocol
AlternateServers=
AlwaysReportTriggerResults=0
AnsiNPW=1
ApplicationName=
ApplicationUsingThreads=1
AuthenticationMethod=1
BulkBinaryThreshold=32
BulkCharacterThreshold=-1
BulkLoadBatchSize=1024
BulkLoadFieldDelimiter=
BulkLoadOptions=2
BulkLoadRecordDelimiter=
ConnectionReset=0
ConnectionRetryCount=5
ConnectionRetryDelay=5
Database=<Database_Name>
EnableBulkLoad=0
EnableQuotedIdentifiers=0
EncryptionMethod=1
FailoverGranularity=0
FailoverMode=0
FailoverPreconnect=0
FetchTSWTZasTimestamp=0
FetchTWFSasTime=1
GSSClient=native
HostName=<Host_Name>
HostNameInCertificate=
InitializationString=
Language=
LoadBalanceTimeout=0
LoadBalancing=0
LoginTimeout=300
LogonID=c02395767
MaxPoolSize=100
MinPoolSize=0
PacketSize=0
Password=<Password>
Pooling=0
PortNumber=1433
QueryTimeout=0
ReportCodePageConversionErrors=0
SnapshotSerializable=0
SeedBeforeConnect=1
TrustStore=
TrustStorePassword=
ValidateServerCertificate=1
WorkStationID=
XMLDescribeType=-10


3. Once the file is ready, one need to set the environment variables. 

    • LD_LIBRARY_PATH  -  To the location where driver exists.
    • ODBCINI -  Absolute path of the odbc.ini file
    • ODBCHOME - Location where odbc.ini file exists.

Example

export LD_LIBRARY_PATH=/root/infaagent/drivers/odbc/datadirect/r27/linux/:$LD_LIBRARY_PATH
export ODBCINI=/root/Desktop/odbc.ini
export ODBCHOME=/root/Desktop/

4. DSN is created now, test the connectivity with ISQL : 

Command: isql <DSN_name> username password .

Example

[root@VCAaapVm001 Desktop]# isql SQLSERVER c0548 infaXXXX
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
 
5. Now one can proceed further to create SQL server Odbc connection in IICS Org.
More Information
Reference
Applies To
Product: Cloud Data Integration
Problem Type: Configuration
User Type: Architect; Developer
Project Phase: Configure
Product Version:
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:11/11/2019 5:43 AMID:533879
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)