This discussion is archived
9 Replies Latest reply: Mar 26, 2013 3:41 AM by kgronau RSS

ORA-28545 ORA-02063

820625 Newbie
Currently Being Moderated
Hi friends,

I'm installing DG4MSQL to connect to SQL server database. I installed the Oracle Gateway 11.2 on a server(server name - aris) where SQL Server 2005 is also running. Below are the config files for the gateway
Listener.ora

# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=aris.hna.com)(PORT=1521))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4msql)
         (ORACLE_HOME=E:\product\11.2.0\tg_1)
         (PROGRAM=dg4msql)
      )
  )

E:\product\11.2.0\tg_1\BIN>lsnrctl stat

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 16-DEC-2010 17:00
:53

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Start Date                16-DEC-2010 17:00:41
Uptime                    0 days 0 hr. 0 min. 12 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\product\11.2.0\tg_1\network\admin\listener.ora
Listener Log File         e:\product\11.2.0\tg_1\diag\tnslsnr\ARIS\listener\ale
rt\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ARIS.hna.com)(PORT=1521)))
The listener supports no services
The command completed successfully


initdg4msql.ora:

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[aris]/aris/sqldb
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=aris\user1     
HS_FDS_RECOVERY_PWD=user1pwd


tnsnames.ora from the Oracle database server:

dg4msql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=aris.hna.com)(PORT=1521))
    (CONNECT_DATA=(SID=dg4msql))
    (HS=OK)
  ) 
Created DBLINK from Oracle database:

create public database link to_sqldb connect to "aris\user1" identified by "user1pwd"
using 'dg4msql';

SQL> select * from dual@to_sqldb;
select * from dual@to_sqldb
*
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from TO_SQLDB

Any help please.Thanks

Edited by: 817622 on Dec 16, 2010 2:58 PM
  • 1. Re: ORA-28545 ORA-02063
    kgronau Guru
    Currently Being Moderated
    root cause of the 28545 is your listener - it does not support any service see the message: The listener supports no services

    A listener configured for the SID dg4msq commonly states:
    Service "dg4msql" has 1 instance(s).
    Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...


    => Are you sure the listener file you've posted is located in the directory: E:\product\11.2.0\tg_1\network\admin\listener.ora? For me it looks like you've edited the sample listener.ora file from E:\product\11.2.0\tg_1\dg4msql\admin...

    By default the SQL+Net listener is reading the listener file from E:\product\11.2.0\tg_1\network\admin, so please make sure you've edited this file. Then STOP and START the listener again.
  • 2. Re: ORA-28545 ORA-02063
    820625 Newbie
    Currently Being Moderated
    You are right. I moved the listener.ora to Oracle Gateway home/network/admin and it worked. Thanks so much. But I get a different error now when I execute the select..


    initdg4msql.ora:

    # This is a customized agent init file that contains the HS parameters
    # that are needed for the Database Gateway for Microsoft SQL Server

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO=[aris]/aris/sqldb
    HS_FDS_TRACE_LEVEL=OFF
    HS_FDS_RECOVERY_ACCOUNT=aris\user1     
    HS_FDS_RECOVERY_PWD=user1pwd


    create public database link to_sqldb connect to "aris\user1" identified by "user1pwd"
    using 'dg4msql';

    SQL> select * from dual@to_sqldb;
    select * from dual@to_sqldb
    *

    ERROR at line 1:
    ORA-01017: invalid username/password; logon denied
    [Oracle][ODBC SQL Server Driver][SQL Server]Login failed for user 'aris\user1'.
    {28000,NativeErr = 18456}[Oracle][ODBC SQL Server Driver]Invalid connection
    string attribute {01S00}
    ORA-02063: preceding 2 lines from TO_SQLDB

    aris\user1 is the administrative account we use and the server name is aris.. Please help..

    Thanks again
  • 3. Re: ORA-28545 ORA-02063
    kgronau Guru
    Currently Being Moderated
    The username/password you specified 'aris\user1' sounds like you're using Windows authentication to connect to the SQL Server. Unfortunately you can't use Windows authentication with DG4MSQL/DG4ODBC - you need to use the user authentication option of SQL Server. For example use the "sa" user or any other SQL Server user.
  • 4. Re: ORA-28545 ORA-02063
    820625 Newbie
    Currently Being Moderated
    Thanks.. Our applications use windows authentication to connect to SQL Server databases.. Do we need to start using SQL Server authentication to use DG4MSQL? Or just create a sql server user for DG4MSQL and we can keep our windows authentication for the applications.. sorry I'm confused..

    Thanks much..
  • 5. Re: ORA-28545 ORA-02063
    kgronau Guru
    Currently Being Moderated
    No you don't. You can use the MS SQL Server "Mixed Mode Authetication" mechanism which allows you to connect as OS user and as a normal SQL Server authenticated user.
  • 6. Re: ORA-28545 ORA-02063
    820625 Newbie
    Currently Being Moderated
    Thanks so much. You have been really helpful..

    I'm using sql server authentication and get this error..

    initdg4msql.ora in E:\product\11.2.0\tg_1\dg4msql\admin folder:

    # This is a customized agent init file that contains the HS parameters
    # that are needed for the Database Gateway for Microsoft SQL Server
    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO=[aris]/aris/sqldb
    HS_FDS_TRACE_LEVEL=OFF
    HS_FDS_RECOVERY_ACCOUNT=sqluser     
    HS_FDS_RECOVERY_PWD=sqluser
    create public database link to_sqldb connect to "sqluser" identified by "sqluser"
    using 'dg4msql';

    SQL> select * from dual@to_sqldb;
    select * from dual@to_sqldb
    *

    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Oracle][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access
    denied. {08001,NativeErr = 17}[Oracle][ODBC SQL Server
    Driver][DBMSLPCN]ConnectionOpen (Connect()). {01000,NativeErr =
    53}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
    ORA-02063: preceding 2 lines from TO_SQLDB

    'sqluser' has the db owner permissions in SQLDB database.. Please help

    Thanks again
  • 7. Re: ORA-28545 ORA-02063
    kgronau Guru
    Currently Being Moderated
    The error message is reported by the SQL Server.

    So you should first check with any other utility if sqluser/sqluser is able to connect to the SQL Server and query some tables.
    If this works, please have a look at the connect string:
    HS_FDS_CONNECT_INFO=[aris]/aris/sqldb
    Are you sure the instance name is really aris? Instead of using a named instance you could use the port of the SQL Server:
    HS_FDS_CONNECT_INFO=[aris]<port number>://sqldb
  • 8. Re: ORA-28545 ORA-02063
    820625 Newbie
    Currently Being Moderated
    I removed instance name from HS_FDS_CONNECT_INFO=[aris]/aris/sqldb and it works now.

    Thanks so much for all your help!!!!
  • 9. Re: ORA-28545 ORA-02063
    kgronau Guru
    Currently Being Moderated
    thread answer removed

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points