If you can tnsping successfully then it is not a firewall issue! I
would try reconfiguring your ODBC connection. I use MS Access as a
front end too and sometimes I have to reconfigure ODBC connections. Or
try ODBC from another computer.
sybrandb wrote:
> Christoph Ebner von Eschenbach wrote:
> > Hello,
> >
> > after 2 days of fruitless searching and trying, I'm nearly desperate now,
> > so I'm posting my problem here with hope that someone can help...
> >
> > I'm trying to connect to an Oracle 9.2 database via TCP from Microsoft
> > Access 2000 SP3.
> > I'm using the ODBC-Drivers from Microsoft.
> >
> > I keep getting the following error:
> > "[Microsoft][ODBC driver for Oracle]ORA-12535: TNS:operation timed out
> > (#12535) [Microsoft][ODBC Driver Manager] Fehler bei SQLSetConnectAttr für
> > Treiber (#0)"
> >
> >
> > Here are the details:
> >
> > CLIENT-machine:
> > Operating system: Microsoft Windows 2000 SP4
> > ODBC-Driver: Microsoft ODBC for Oracle V2.575.1062.00
> > Client-Database: Microsoft Access 2000 SP3
> > Oracle: Oracle 9.2.0.1.0
> >
> > SERVER-machine:
> > Operating system: Microsoft Windows Server 2003 SP1
> > Oracle: Oracle 9.2.0.1.0
> >
> > --------------------------------------------------------------
> > tnsnames.ora (CLIENT)
> > ---------------------
> > ORACLE.EMBITBIZ =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = TCP)(HOST = 217.69.83.130)(PORT = 1521))
> > )
> > (CONNECT_DATA =
> > (SERVER = DEDICATED)
> > (SERVICE_NAME = MBDaten)
> > )
> > )
> > --------------------------------------------------------------
> > listener.ora (SERVER)
> > ---------------------
> > LISTENER =
> > (DESCRIPTION_LIST =
> > (DESCRIPTION =
> > (ADDRESS_LIST =
> > (ADDRESS = (PROTOCOL = TCP)(HOST = embit)(PORT = 1521))
> > )
> > )
> > )
> >
> > SID_LIST_LISTENER =
> > (SID_LIST =
> > (SID_DESC =
> > (SID_NAME = PLSExtProc)
> > (ORACLE_HOME = c:\oracle\ora92)
> > (PROGRAM = extproc)
> > )
> > (SID_DESC =
> > (GLOBAL_DBNAME = mbdaten)
> > (ORACLE_HOME = c:\oracle\ora92)
> > (SID_NAME = mbdaten)
> > )
> > )
> >
> > --------------------------------------------------------------
> > sqlnet.ora (SERVER)
> > ---------------------
> >
> > NAMES.DEFAULT_DOMAIN = biz
> >
> > SQLNET.AUTHENTICATION_SERVICES= (NTS)
> >
> > #NAMES.DIRECTORY_PATH= (TNSNAMES)
> > NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
> > --------------------------------------------------------------
> >
> >
> > - I have installed Microsoft MDAC 2.8 SP1 on the client-PC
> >
> > - I have changed registry settings, as suggested by many websites to the
> > following values:
> >
> > [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
> > "OracleXaLib"="oraclient9.dll"
> > "OracleSqlLib"="ORASQL9.DLL"
> > "OracleOciLib"="oci.dll"
> >
> > - "tnsping ORACLE.EMBITBIZ" on the client-machine reports success:
> >
> > Adapter TNSNAMES zur Auflösung des Alias benutzt
> > Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL
> > = TCP)
> > (HOST = 217.69.83.130)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED)
> > (SERV
> > ICE_NAME = MBDaten)))
> > OK (40 ms)
> >
> > - The listener.log on the SERVER shows that the listener configuration
> > should be OK, as the connection seems to be established correctly:
> > 18-AUG-2006 13:04:18 *
> > (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MBDaten)(CID=(PROGRAM=C:\WINNT\system32\odbcad32.exe)(HOST=MBINTERN)(USER=Administrator
> > ))) * (ADDRESS=(PROTOCOL=tcp)(HOST=212.202.134.67)(PORT=58954)) *
> > establish * MBDaten * 0
> >
> > - The user I'm using for Login is able to connect to the database (as
> > tested with SQL+ Worksheet on the SERVER-machine)
> >
> >
> > I suppose the problem to be connected with the ODBC-driver on the client
> > machine.
> >
> > I don't know anything else to do or try to solve this problem.
> > Can anybody help me ?! Any suggestions or tips?
>
>
> Search Google (which you obviously didn't do). ora-12535 is usually
> caused by firewalls.
> You can identify this by tracing the client connection and see the
> redirect to another tcp/ip port fail.
> You can see the port is actually redirected in the listener.log.
>
> How to enable tracing is in the documentation and I've also posted it
> here many, many times.
>
> If it is firewall, I've posted the methods to address it posted here
> many, many times.
>
> --
> Sybrand Bakker
> Senior Oracle DBA
Received on Mon Aug 21 2006 - 16:54:41 CDT
