tg4msql Connection Problem

[Ronald Ferro via DBMonster.com]

Good morning,

Here is the message:

SQL> connect sys/****** as sysdba
Connected.
SQL> Drop database link tg4msql;
===========================
Database link dropped.

SQL> Create database link tg4msql connect to "WCSGuest"
2 identified by "WCSGuestpwd" using 'tg4msql';

Database link created.

SQL> select * from ***@tg4msql;
select * from ***@tg4msql
*
ERROR at line 1:
ORA-28500: connection from ORACLE to a non-Oracle system returned this
message:
[Transparent gateway for MSSQL][Microsoft][ODBC SQL Server Driver][DBNETLIB]
SQL
Server does not exist or access denied. (SQL State: 08001; SQL Code: 17)
ORA-02063: preceding 2 lines from TG4MSQL

=====================================
Here are my .ora files

inittg4msql.ora
===============
# This is a sample agent init file that contains the HS parameters that are
# needed for the Transparent Gateway for SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO="SERVER=140.76.155.159,DATABASE=CRM"
HS_FDS_TRACE_LEVEL=0
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

listener.ora
=============
# LISTENER.ORA Network Configuration File: C:\oracle\ora92\network\admin\
listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = D9X8Z571)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dbFerro)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = dbFerro)
)
(SID_DESC =
(GLOBAL_DBNAME = GLOBALdb)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = GLOBALdb)
)
(SID_DESC =
(SID_NAME = tg4msql)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = tg4msql)
)
)

CONNECT_TIMEOUT_LISTENER = 0


sqlnet.ORA
===========

# 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, ONAMES, HOSTNAME)


tnsnames.ora
=============
# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\
tnsnames.ora
# Generated by Oracle configuration tools.

ORA9TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ab868779.bpt.sikorsky.com)(PORT =
1521))
)
(CONNECT_DATA =
(SID = ora9test)
)
)

DBFERRO =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 2030))
)
(CONNECT_DATA =
(SID = dbFerro)
)
)

OP10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sussfd06.sea.sikorsky.com)(PORT =
1521))
)
(CONNECT_DATA =
(SERVICE_NAME = op10)
)
)

GLOBALDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = D9X8Z571)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = GLOBALdb)
)
)

SAMS_ELVIS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =elvis.sea.sikorsky.com )(PORT = 1521))

)
(CONNECT_DATA =
(SERVICE_NAME = sun30ora)
)
)

OT16 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sussfa06.sikorsky.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ot16)
)
)

OT10 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = sussfa06.sikorsky.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ot10)
)
)

INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = D9X8Z571)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)

#UPDATED_ELVIS

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)


tg4msql =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = tg4msql)
)
(HS=OK)
)


Any thought?

[Billy]

Post by Ronald Ferro via DBMonster.com
ORA-28500: connection from ORACLE to a non-Oracle system returned this
[Transparent gateway for MSSQL][Microsoft][ODBC SQL Server Driver][DBNETLIB]
SQL
Server does not exist or access denied. (SQL State: 08001; SQL Code: 17)
ORA-02063: preceding 2 lines from TG4MSQL

That would seem to be related to a configuration error on the Gateway,
or an invalid authentication string used for the database link. The
actual error is a dblib error - not an Oracle error. So the Gateway's
calls to dblib worked.. simply that those dblib calls returned errors
because they were not happy with the contents of the parameters.

The real juicy (or crunchy if that's your choice) data that you have
not posted is the trace files of the Gateway.

You can also turn on debug tracing on the Gateway - which is likely the
single most useful thing to do when dealing with problems like the
above. It shows step by step the calls made by the Gateway to the
foreign database - including the authentication
part.

--
Billy