4

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?

hotfix
  • 3,376
  • 20
  • 36
James Whitehead
  • 101
  • 1
  • 11

1 Answers1

5

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

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • 1
    I've marked this as accepted because it did answer the question and is the correct connection string, but it returns a different error: `pyodbc.OperationalError: ('08004', '[08004] [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified\n (12154) (SQLDriverConnect)')` Looking at other Stackoverflow answers suggests that this is because of a 32bit/64bit difference, even though the OraClient driver and database are both 64bit. – James Whitehead Aug 30 '18 at 12:54
  • Is your python also 64-bit? Perhaps you have to remove the curling brackets `{}` around the database name. – Wernfried Domscheit Aug 30 '18 at 13:03
  • Python is a 64-bit install of version 3.6.5. Removing either set of curly brackets makes no difference. – James Whitehead Aug 30 '18 at 13:19
  • I also made some test, looks like for ODBC you must use an alias. Full qualified names seems to be not supported neither by Oracle nor the Microsoft driver. – Wernfried Domscheit Aug 30 '18 at 13:35
  • For the host? I've tried using the IP address and hostname without the domain and it still results in the 12154 error. – James Whitehead Aug 30 '18 at 14:05
  • 1
    This problem was fixed by adding values for TNS_ADMIN and TNS_HOME as environment variables and `HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE` that point to the `\NETWORK\ADMIN` directory of the Oracle installation. This meant I could use tnsnames.ora and simply use `Driver={Oracle in OraClient11g_home1};Dbq=tnsnames_entry;Uid=username;Pwd=password;` as the connection string. So it seems that this isn't possible without using tnsnames.ora – James Whitehead Aug 30 '18 at 14:42