“ORA-12560: TNS:protocol adapter error” when bypassing tnsnames.ora using full connection string and EZCONNECT

Question

Attempting to use either connection string to bypass tnsnames.ora (full connection string and EZCONNECT) returns the following error when trying to connect to the database:

pyodbc.Error: ('HY00', '[HY00] [Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error\n (12560) (SQLDriverConnect);[Oracle][ODBC][Ora]ORA-12560: TNS:protocol adapter error\n (12560)')

I've tried using the following connection strings from https://www.connectionstrings.com/oracle/:

Driver={Oracle in OraClient11g_home1}; Data Source=uid/pwd@//host:port/service_name;

Driver={Oracle in OraClient11g_home1};
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))) 
                         (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)));
User Id=uid;Password=pwd;'

I can connect to the database using sqlplus and EZCONNECT:

PS: C:\>sqlplus uid/pwd@//host:port/service_name

SQL*PLUS: Release 11.2.0.1.0 Production on Thu Aug 30 11:56:22 2018

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

SQL>

I doubt it's a driver issue, because using a different driver returns a different error:

pyodbc.InterfaceError: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

Why does the connection string work for sqlplus but not for ODBC?

Wernfried Domscheit Wernfried Domscheit · Accepted Answer · 2018-08-30T12:34:28

When you use the Oracle ODBC driver then the connection string should be like this:

Driver={Oracle in OraClient11g_home1};
DBQ={(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))) 
                         (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)))};
Uid=uid;Pwd=pwd;

See Oracle in OraClient11g_home1 connection strings

For the deprecated Microsoft driver it would be

Driver={Microsoft ODBC for Oracle};
Server={(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=port))) 
                         (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=service_name)))};
Uid=uid;Pwd=pwd;

See Microsoft ODBC for Oracle connection strings

Please note, the Microsoft ODBC for Oracle is available only for 32-Bit

“ORA-12560: TNS:protocol adapter error” when bypassing tnsnames.ora using full connection string and EZCONNECT

1 Answers