9 Replies Latest reply: Mar 26, 2013 5:41 AM by Kgronau-Oracle RSS

    ORA-28545 ORA-02063

    820625
      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-Oracle
          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
            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-Oracle
              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
                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-Oracle
                  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
                    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-Oracle
                      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
                        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-Oracle
                          thread answer removed