Amazon has really made it quite simple to pull down the ODBC/JDBC drivers needed to connect to Redshift. Today, however, I attempted to test connectivity with isql and got the error “Could not SQLConnect”

The Error

[oracle@obiee-server ~]$ isql "Redshift ODBC"                                                                                                                                              
[ISQL]ERROR: Could not SQLConnect

Digging In

Locate the primary driver config

[oracle@obiee-server ~]$ odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /opt/oracle/Middleware/user_projects/domains/bi/config/fmwconfig/bienv/core/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Add debugging

vim /etc/odbcinst.ini

[ODBC]
Trace = yes
TraceFile = /tmp/odbc.log

Trigger and log

Run isql again, but this time look at the TraceFile. This is what stood out for me:

[ODBC][11397][1509634325.964621][SQLConnect.c][3700]
                Entry:
                        Connection = 0x631050
                        Server Name = [Redshift ODBC][length = 20 (SQL_NTS)]
                        User Name = [NULL]
                        Authentication = [NULL]
                UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE 'UCS-2LE'

                DIAG [S1000] [Amazon][ODBC] (11560) Unable to locate SQLGetPrivateProfileString function.

[ODBC][11397][1509634325.980633][SQLConnect.c][4067]
                Exit:[SQL_ERROR]

Solution

Note: My system is RHEL7, 64-bit.

It seems the issue here is the Redshift driver not knowing the location of the ODBCInstLib.

vim /opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini

Replace ODBCInstLib=libiodbcinst.so with ODBCInstLib=/usr/lib64/libodbcinst.so