This discussion is archived
6 Replies Latest reply: May 11, 2011 9:43 PM by kgronau RSS

Can not connect to Oracle Gateway for SQL Server

808882 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Please don't forget to mark this thread as answered.
    Thx.

Legend

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