SAS® 9.4M9 (TS1M9) fails to establish ODBC connections to PostgreSQL and/or MySQL with "CLI error trying to establish connection"


SAS 9.4M9 fails to establish ODBC connections to PostgreSQL and/or MySQL with the following error:

ERROR: CLI error trying to establish connection:
[unixODBC][Driver Manager] Data source name not found and no default driver specified

ERROR: Error in the LIBNAME statement.

This issue occurs after migrating to a new Red Hat Enterprise Linux 10.1 environment. ODBC connections that previously worked are no longer able to connect through SAS.

This issue might affect PostgreSQL and MySQL ODBC connections and can occur when SAS is unable to locate the required ODBC driver, DSN definition, or associated ODBC configuration files.

Cause

This issue was determined to be related to ODBC configuration and driver availability outside of SAS rather than the SAS LIBNAME statement itself.

Investigation identified two separate ODBC-related issues:

PostgreSQL

Testing returned the following error:

[unixODBC][Driver Manager] Can't open lib '/opt/sas/sasbin/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/psqlodbcw.so' : file not found

This error indicates that the PostgreSQL ODBC driver library referenced by the DSN configuration could not be found at the configured location or was not accessible by the ODBC Driver Manager.

MySQL

Testing returned the following error:

[unixODBC][MySQL][ODBC 9.6(w) Driver] Unknown character set: 'utf8mb4'

This error indicates a potential compatibility issue between the installed MySQL ODBC driver and the target MySQL or MariaDB database environment.

Additional Findings

The ODBC user data source location changed between the original and new environments:

Old Environment:

/home/LFM.LAN/sasoa_produser/.odbc.ini

New Environment:

/root/.odbc.ini

This change could affect how SAS locates DSN definitions and ODBC configuration information.

Workaround

To work around this issue, complete the following steps:

1. Verify ODBC Driver availability.

For PostgreSQL connections, confirm that the configured ODBC driver exists and is accessible.

Here is an example:

ls -l /opt/sas/sasbin/SASODBCDriversfortheWebInfrastructurePlatformDataServer/9.4/Driver/psqlodbcw.so

If the driver library does not exist or is inaccessible, update the ODBC configuration to reference the correct driver location or re-install the required driver.

 

2. Verify ODBC configuration files.

Review the following configuration files:


Then, confirm the following:

 

3. Verify unixODBC configuration.

Collect the following information to verify the ODBC environment:


These commands identify the following:

 

4. Verify environment variables.

Confirm that SAS uses the expected ODBC configuration:

echo $ODBCINI
echo $ODBCSYSINI
echo $LD_LIBRARY_PATH

Review these values to determine whether SAS and the operating system reference the same ODBC configuration files and driver libraries.

 

5. Test the DSN outside of SAS.

Validate the DSN directly through unixODBC.

Here are two examples:

and/or


Testing outside of SAS helps determine whether the issue exists at the following:

Additional Information

When troubleshooting ODBC connectivity errors such as Data source name not found and no default driver specified, validate the ODBC driver registration, DSN definitions, driver library paths, environment variables, and connectivity outside of SAS. Testing with isql can help determine whether the issue originates in the ODBC layer or within SAS. See the Resource Links section for SAS documentation and SAS Note 57650 for additional troubleshooting guidance.

Diagnostic Indicators

The following PostgreSQL error typically indicates that the configured ODBC driver library cannot be located:

[unixODBC][Driver Manager] Can't open lib '...psqlodbcw.so' : file not found

The following MySQL error might indicate driver and database character-set compatibility issues:

[unixODBC][MySQL][ODBC 9.6(w) Driver] Unknown character set: 'utf8mb4'

Both errors occur outside of SAS and can help isolate whether the problem originates in the ODBC environment rather than within SAS software.

Useful Troubleshooting Steps

  1. Verify that the driver library exists.
  2. Verify that the DSN definition is correct.
  3. Verify the driver registration in odbcinst.ini.
  4. Verify environment variables used by the SAS process.
  5. Test the DSN using isql.
  6. Compare ODBC configuration between the old and new environments.
  7. Review any operating system, driver, or database changes made during migration.

Resource Links

SAS/ACCESS® Interface to ODBC Documentation

ODBC Troubleshooting

Related Topics

Applicable Products