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: TNSperation 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=MBDa ten)(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?

Christoph Ebner von Eschenbach schreef:

> 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: TNSperation 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)
> )
> )

Try SID=mbdaten, instead of service_name. Service_name requires the
complete
service name, as used while installing the database. It is usually
SID.domain.company
of some sort.

> --------------------------------------------------------------
> 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)
> )
> )
>

Is host mbit the same as 217.69.83.130?
>
> - "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=MBDa ten)(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)
>
>

Try with SQL*Plus on your client!

And time outs are usually connected with firewalls - any firewalls
active between
your client and the server? Think "personal firewalls" on the client,
too!

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: TNSperation 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=MBDa ten)(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

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: TNSperation 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=MBDa ten)(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

On 21 Aug 2006 14:54:41 -0700, sirajka@hotmail.com wrote:

>If you can tnsping successfully then it is not a firewall issue!

Incorrect. If you can tnsping the only thing you know for certain is
there is a listener running on that port.
Sqlnet by default uses 2 (*two*) ports, one fixed and one random.

--
Sybrand Bakker, Senior Oracle DBA

Am Tue, 22 Aug 2006 00:01:37 +0200 schrieb Sybrand Bakker
<postbus@sybrandb.demon.nl>:

> On 21 Aug 2006 14:54:41 -0700, sirajka@hotmail.com wrote:
>
>> If you can tnsping successfully then it is not a firewall issue!
>
> Incorrect. If you can tnsping the only thing you know for certain is
> there is a listener running on that port.
> Sqlnet by default uses 2 (*two*) ports, one fixed and one random.
>
> --
> Sybrand Bakker, Senior Oracle DBA


Thanks for your answer, exactly that was the problem in my case.

There was a firewall on the server that blocked all ports except port 1521.
But communication with the listener will only be initiated through port
1521. Afterwards communication is switched to other not-predictable ports,
so all answer-attempts were blocked by the firewall and therefore there
was never ever any reaction from the oracle server till the timeout
occured.

This can be solved by adding a registry entry on the server:
Under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0, an entry USE_SHARED_SOCKET
(Type Reg_SZ) has to be added and set to TRUE.
Then all inside/outside connections will be made only via the predefined
listener port (1521, if not changed otherwise).

Afterwards communication operated flawlessly.

On Mon, 28 Aug 2006 11:29:16 +0200, "Christoph Ebner von Eschenbach"
<C.EvEschenbach@gmx.de> wrote:

>This can be solved by adding a registry entry on the server:
>Under HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0, an entry USE_SHARED_SOCKET
>(Type Reg_SZ) has to be added and set to TRUE.
>Then all inside/outside connections will be made only via the predefined
>listener port (1521, if not changed otherwise).
>
>Afterwards communication operated flawlessly.

However be aware, if you stop the listener, all sessions will be
disconnected now.

--
Sybrand Bakker, Senior Oracle DBA