6 Replies Latest reply: May 11, 2011 11:43 PM by Kgronau-Oracle RSS

    Can not connect to Oracle Gateway for SQL Server

    808882
      I have set up Oracle gateway 11.2.0.2 (Out-of-Place Upgrade) for SQL Server on host1.example.com. But I can not connect to the gateway server via database link.

      SQL> create public database link dblink1 connect to sa identified by change_on_install using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = host1.example.com)(PORT = 1521))) (CONNECT_DATA = (SID = dg4msql))(HS = OK))';

      Database link created.

      SQL> commit
      2 ;

      Commit complete.

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

      SQL> quit

      Why I got the error? How to solve the problem?

      Edited by: user9546759 on May 9, 2011 12:07 AM
        • 1. Re: Can not connect to Oracle Gateway for SQL Server
          Mkirtley-Oracle
          Hi,
          We would need to see the configuration files to find the problem cause.
          However, SQL*Server is case sensitive for names so create the database link as -

          create public database link dblink1 connect to "sa" identified by "change_on_install" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = tcp)(HOST = host1.example.com)(PORT = 1521))) (CONNECT_DATA = (SID = dg4msql))(HS = OK))';

          making sure the user and password in double quotes are in the same case as on SQL*Server itself.

          Regards,
          Mike
          • 2. Re: Can not connect to Oracle Gateway for SQL Server
            808882
            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=[10.182.114.242]/SQLEXPRESS/OracleSamples
            HS_FDS_TRACE_LEVEL=0
            HS_FDS_RECOVERY_ACCOUNT=RECOVER
            HS_FDS_RECOVERY_PWD=RECOVER

            ==========================================================

            listener.ora:

            # listener.ora Network Configuration File: C:\software\product\ORA_SQLSERVER_GTW_11202\network\admin\listener.ora
            # Generated by Oracle configuration tools.

            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = host1.example.com)(PORT = 1523))
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
            )
            )

            SID_LIST_LISTENER=
            (SID_LIST=
            (SID_DESC=
            (SID_NAME=dg4msql)
            (ORACLE_HOME=C:\software\product\ORA_SQLSERVER_GTW_11202)
            (PROGRAM=dg4msql)
            )
            )


            ADR_BASE_LISTENER = C:\software\product\ORA_SQLSERVER_GTW_11202


            =======================================================

            tnsnames.ora:

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

            dg4msql =
            (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1523))
            (CONNECT_DATA=(SID=dg4msql))
            (HS=OK)
            )
            • 3. Re: Can not connect to Oracle Gateway for SQL Server
              808882
              Hi Mike,

              The username and password are both correct.

              I created the database link following your suggestion but still got the same error:

              SQL> create database link jane_test connect to "sa" identified by "change_on_install" using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (Host = xxx.xxx.xxx.xxx) (PORT = 1523))) (CONNECT_DATA = (SID = dg4msql))(HS = OK))';

              Database link created.

              SQL> select * from dual@jane_test;
              select * from dual@jane_test
              *
              ERROR at line 1:
              ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
              [Oracle][ODBC SQL Server Native Wire Protocol driver]General error. select
              failed: 0 {HY000}[Oracle][ODBC SQL Server Native Wire Protocol driver]Invalid
              attribute in connection string: ArraySize. {01S00}
              ORA-02063: preceding 2 lines from JANE_TEST

              Thanks,
              David
              • 4. Re: Can not connect to Oracle Gateway for SQL Server
                Kgronau-Oracle
                Your connection fails with: General error

                Root cause might be the SQL Server Express and the gateway configuration: HS_FDS_CONNECT_INFO=[xxx.xxx.xxx.xxx]/SQLEXPRESS/OracleSamples

                Open on your SQl Server the SQl Server Configuration Manager => SQL Server Network Configuration =>Protocols for SQL Express => TCP/IP
                When the TCP/IP window opens, click on the IP Addresses
                => this window should display the ip address you've configured. Assigne a fixed port to this address, i.e. 1433 and then change your gateway connection to:
                HS_FDS_CONNECT_INFO=[xxx.xxx.xxx.xxx:1433]//OracleSamples
                • 5. Re: Can not connect to Oracle Gateway for SQL Server
                  808882
                  Thanks, it's solved. But HS_FDS_CONNECT_INFO should like this: HS_FDS_CONNECT_INFO=[xxx.xxx.xxx.xxx:1433]//OracleSamples

                  Edited by: user9546759 on 2011-5-11 下午9:35
                  • 6. Re: Can not connect to Oracle Gateway for SQL Server
                    Kgronau-Oracle
                    Please don't forget to mark this thread as answered.
                    Thx.