PRB: Win2k3srv/SQL2kSP3 --OLE DB-->Ora92

I have a problem connecting to Oracle from SQL2000sp3/linkedservers
on Win2003srv using any OLE DB provider.
[QUOTE]
Machines configuration
=======================
Machine: IVAN (Development workstation)
Intel PC P4/1.5, 512MB
WinXP Pro SP1
SQL2000 SP3, Office XP Pro
ADO 2.71.9030, ODBC 3.520.9030/9041
Oracle Provider for OLE DB 9.2.0.4.0
Oracle ODBC Driver 9.2.0.5.4
Oracle Services for MSTSC 9.2.0.4.0
Machine: GRSK03 (Development server)
Siemens P2/400 Server, 512MB
Win2003 Server Std - RRAS + Kerio WinRoute
SQL2000 SP3, Office XP Pro
ADO 2.80.1022, ODBC 3.525.1022
Oracle Provider for OLE DB 9.2.0.4.0
Oracle ODBC Driver 9.2.0.5.4
Oracle Services for MSTSC 9.2.0.4.0
Machine: GRSK02 (Production server)
Dell Server P3/1.3, 1GB
Win2000 Server Std SP4 - DC
Oracle Database 9.2.0.1.0
Network scheme
==============
|--LAN 1--| |--LAN
2--|
dsl-gw__ws1__ws2
isdn-gw/rras/fw___switch_______dc/db__pc1/20
IVAN <--/--ISDN--/--> GRSK03 <--100BaseT--> GRSK02
Oracle client configuration
===========================
TNSNAMES.ORA & SQLNET.ORA are same on both IVAN and GRSK03
# SQLNET.ORA Network Configuration File:
C:\oracle\ora92\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, HOSTNAME)
# TNSNAMES.ORA Network Configuration File:
C:\oracle\ora92\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = grsk02.grskoviceva.local)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)
SQL2000 Linked server configuration
===================================
On both IVAN and GRSK03
EXEC sp_addlinkedserver
@.server = 'Point',
@.srvproduct = 'Oracle',
@.provider = 'MSDAORA',
@.datasrc = 'ORCL'
GO
EXEC sp_addlinkedsrvlogin 'Point', 'false', NULL, '*****', '******'
PROBLEM
=======
For example following expression:
SELECT TOP 10 * FROM OpenQuery(Point,'SELECT * FROM FK350')
returns resultset on IVAN (works fine) while on GRSK03 returns following
error?
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error. The provider did not give any
information about the error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: The provider did not give any information about the
error.].
The same error on GRSK03 is returned if I try making a linked server using
MSDASQL (ODBC) provider.
I've configured (in registry) Oracle provider for OLE DB (OraOLEDB.dll).
Again on IVAN it works fine, on GRSK03 runs infinitely (no error message).
ODBC DSN on Oracle works fine (for example linking view FK350 in Access) on
both machines.
[QUOTE]
Someone knows why? I suspect security features of Win2003 or RRAS maybe
but for what I've checked all ports are opened and if there is a problem
with router/firewall
it would be on IVAN not GRSK03 which is on LAN side-by-side with GRSK02.
Regarding security I've tried logging using 'sa' but with no results.
I've also tried disabling the Kerio firewall on GRSK03 but it didn't help.
If someone is familiar with the issue, please tell me your opinion.
I'm suspecting mssqlsrv installation although it's been recently installed
and
configured like any other of 6 SQL Servers on other LANs.
Ivan Nikolic
SAIL Company Croatia Ltd.
DB developer
ivan@.sail.hrI would start with this article:
280106 HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL
Server
http://support.microsoft.com/?id=280106
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.