Informatica, [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified error when connecting to SQL Server database
I am trying to connect to a SQL Server database from Informatica. I want to Enable the Application intent to 'READONLY'. I have tried creating USER DSNs/SYSTEM DSNs (Both 32 & 64 bit) but still getting the below error:
Severity Timestamp Node Thread Message Code Message ERROR 7/27/2022 8:39:36 AM NODE01_SEIMDBINFDEV2 READER_1_1_1 RR_4036 Error connecting to database [ [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified SQLSTATE: IM002
Database driver error... Function Name: Connect Database driver error... Function Name : Connect Database Error: Failed to connect to database using user [user_name] and connection string [ODBC System DSN Name].].
I am able to test the connection in ODBC Data Source Administrator. Please find the attachment for Informatica Relational Connection that I have created in Workflow Manager.
Please note you need to setup this connection in informatica server as well.
If your infa server is windows, you have to setup ODBC system DSN properly. give it a name without spaces like my_sql_serv_conn.
Then use this connection name in place of connection string.
If your infa server is UNIX, we need to set two odbc related parameters in unix/linux users bash profile.
INFA_HOME=/opt/infa
ODBCINI= $INFA_HOME/ODBC7.1/odbc.ini
ODBCINST=$INFA_HOME/ODBC7.1/odbcinst.ini
Now, i feel like, your unix/linux user is not able to reach this location or they arent getting exported properly.
Login using the unix/linux user thats running informatica.
Then use more $ODBCINI to see if it has read permission.
Do same for ODBCINST.
Use ssgodbc tool to check connectivity to your mssql server DB.