1 2 Previous Next 17 Replies Latest reply on Aug 25, 2008 11:45 AM by jan.matusiewicz

    MS SQL Server to Oracle

    37961
      I am going to write all of the steps here that I have used trying to connect Oracle to MS SQL Server, so that others may benefit. I spent a few days using the following document, but in the end it turned out to be fairly incomplete, and I was frustrated by the amount of time that I had wasted.

      "Using Heterogeneous Services"
      http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96544/admin.htm#169356

      This document could be substantially improved with just three additions:
      1. A clear distinction between tasks which are to be performed on the MS SQL Server hardware vs. the Oracle server,
      2. More meaningful naming conventions in the examples instead of "Sybase_sales" and "SalesDB", that give you an idea of whether they refer to an Oracle Instance, a SQL Server Database, or something else,
      3. Some mention of Oracle Transparent Gateway, and the fact that it needs to be installed for any of this to work.

      I fully recognize the fact that I am still somewhat in the dark because I havn't gotten it to work yet; however, I wanted to mention that I began working for Oracle Consulting in 1990, and have been an independent Oracle Consultant since 1995. Therefore; the documentation for Heterogeneous Connectivity has not been adequate for a somewhat experienced Oracle developer.

      Below are the steps that I have performed:



        • 1. Re: MS SQL Server to Oracle
          37961
          1. Installed Oracle Transparent Gateway - This was done on the machine where the SQL Server Database resides:
          a. Selected "Custom" Installation and installed 10g 10.1.0.2.0:
          i. Oracle Database 10g 10.1.0.2.0
          ii. Oracle Net Services 10.1.0.2.0
          iii. Oracle Enterprise Manager 10g Database Control
          iv. Oracle Transparent Gateways 10.1.0.2.0
          Oracle Transparent Gateway for Microsoft SQL Server
          v. Oracle Windows Interfaces
          Oracle Services for Microsoft Transaction Server 10.1.0.2.0
          Oracle Objects for OLE 10.1.0.5.0
          Oracle ODBC Driver 10.1.0.2.0
          Oracle Provider for OLE DB 10.1.0.2.0
          Oracle Data Provider for .NET

          2. Reviewed the tg4msql.ora file in the following directory, and it appeared to be consistent with the document above:

          c:\oracle\product\10.1.0\db_1\tg4msql\admin

          It was interesting that is this same directory that there were three additional files. (listener.ora.sample, tg4msql_tx.sql, tnsnames.ora.sample). It is interesting that the tg4msql_tx.sql file states that this files is required to execute in the SQL Server Database to create a transaction table, so that it can speak to Oracle. However; this information was not mentioned in the documentation above, so I have not performed this step.

          • 2. Re: MS SQL Server to Oracle
            37961
            3. Created the following entries on the Server running MS SQL Server (i.e., CSISQL):

            tnsnames.ora
            tg4msql=
                 (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = TCP)
                      (HOST = CSISQL)
                      (PORT = 1521)
                           )
                 (CONNECT_DATA =     (SID=tg4msql)
                 )
                 (HS=OK)
            )

            listener.ora
            SID_LIST_LISTENER =
            (SID_LIST =
            (SID_DESC =
            (SID_NAME = PLSExtProc)
            (ORACLE_HOME = C:\oracle\product\10.1.0\db_1)
            (PROGRAM = extproc)
            )
            (SID_DESC=
            (SID_NAME=tg4msql)
            (ORACLE_HOME = C:\oracle\product\10.1.0\db_1)
            (PROGRAM=tg4msql)
            )
            )

            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
            )
            (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = CSISQL.CSINT.com)(PORT = 1521))
            )
            )
            )

            inittg4msql.ora
            HS_FDS_CONNECT_INFO=csisql.csi
            HS_FDS_TRACE_LEVEL=DEBUG
            HS_FDS_RECOVERY_ACCOUNT=RECOVER
            HS_FDS_RECOVERY_PWD=RECOVER
            • 3. Re: MS SQL Server to Oracle
              37961
              I'm happy to report that I was able to connect. The bad news is that perfomance is terrible. Hopefully, I can figure that out too.

              Just in case there is anyone else that is disappointed by the dearth on information and assistance with "Heterogenous Connectivity" I will provide the file files and their location below. However; as a side note I wanted to mentioned that it would have been useful if they had referred to the SID on the SQL Server side, as a pseudo-SID, because there is NO actual oracle instance on the gateway. I incorporated the following, and I am able to connect from Oracle to SQL Server through a database link:

              ##########GATEWAY TNSNAMES.ORA##########
              tg4msql=
                   (DESCRIPTION =
                             (ADDRESS =      (PROTOCOL = TCP)
                                       (HOST = CSISQL)
                                       (PORT = 1521)
                             )
                   (CONNECT_DATA =     (SID=tg4msql)
                   )
                   (HS=OK)
              )

              ##########GATEWAY LISTENER.ORA##########
              LISTENER=
              (ADDRESS=
                   (PROTOCOL=TCP)
                   (HOST=CSISQL)
                   (PORT=1521)
              )

              SID_LIST_LISTENER=
              (SID_LIST=
                   (SID_DESC=
                        (SID_NAME=tg4msql)
                        (ORACLE_HOME=C:\oracle\product\10.1.0\db_1)
                        (PROGRAM=tg4msql)
                   )
              )

              ##########GATEWAY TG4MSQL.ORA##########
              HS_FDS_CONNECT_INFO=csisql.csi
              HS_FDS_TRACE_LEVEL=DEBUG
              HS_FDS_RECOVERY_ACCOUNT=RECOVER
              HS_FDS_RECOVERY_PWD=RECOVER

              ##########ORACLE DATABASE SERVER TNSNAMES.ORA##########
              tg4msql= (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = TCP)
                             (HOST = CSISQL)
                             (PORT = 1521)
                        )
                        (CONNECT_DATA = (SID=tg4msql)
                        )
                        (HS=OK)
                   )

              ##########ORACLE DATABASE SERVER SQLNET.ORA##########
              NAMES.DEFAULT_DOMAIN = world
              NAMES.DEFAULT_ZONE = world
              SQLNET.AUTHENTICATION_SERVICES= (NTS)
              NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, ONAMES, HOSTNAME)
              DEFAULT_SDU_SIZE = 8761

              Just one last note, if you create the database link without using the "CONNECT TO username IDENTIFIED BY password", then it appears to attempt to connect to the SQL Server database as the same Oracle user that you are currently logged in as. Therefore, I would reccomend using the "CONNECT ..." clause.
              • 4. Re: MS SQL Server to Oracle
                37961
                I failed to mention that in my case the Oracle Transparent Gateway software comes on the 10g Database CD. You simply select "Custom" as the install type, and then select the desired gateway.
                • 5. Re: MS SQL Server to Oracle
                  37961
                  Lastly, in the files above:

                  CSISQL = Hostname where the MS SQL Server Database resides
                  CSI = The actual MS SQL Server Database name
                  • 6. Re: MS SQL Server to Oracle
                    165379
                    As you mentioned that we have to write host name and database name.

                    In mssql2000 You can install more than one instances of mssql in one computer and you could have similar databases name in each database.

                    What should we do in this case?


                    What changes should I make if I have following

                    host =sing
                    instance=singh\NetSdK
                    database=pubs
                    • 7. Re: MS SQL Server to Oracle
                      309833
                      How to use TG4MSQL with MS SQL Server Instances
                      -----------------------------------------------

                      Microsoft introduced with MS SQL Server 2000 the concept of "instances".
                      All older releases (6.5 or 7.0) running on the same machine as a
                      SQL Server 2k act as a DEFAULT instance. The same is true for the first
                      installation of a MS SQL SERVER 2k on a PC.

                      All further installations of MS SQL Server 2k on the same machine are
                      named instances; this means a name must be provided during the
                      MS installation routine for this instance.


                      How to configure TG4MSQL in detail is described in
                      Note 231458.1 named HOW TO SETUP TG4MSQL

                      If there is only a default instance of MS SQL Server running, the parameter
                      HS_FDS_CONNECT_INFO is configured as:

                      HS_FDS_CONNECT_INFO = <server name>.<database name>


                      Method I:
                      #########
                      This method is the fastest, but might cause problems if the MS SQL Server
                      is not listening on the default port. Give it a try, but if this method
                      causes troubles, please proceed to method 2.

                      The simplest way to work with instances is to specify the connect details in
                      the init.ora of the TG4MSQL.
                      The connect info contains the server name of the MS SQL Server, the instance
                      name and the database of the instance:
                      HS_FDS_CONNECT_INFO="<SERVER NAME>\\<INSTANCE NAME>.<DATABASE>"

                      If the server name is SQLSERVER and the instance name is instance2, the connect
                      string to the Northwind database looks like:
                      HS_FDS_CONNECT_INFO="SQLSERVER\\instance2.Northwind"

                      Similar to the description above is the connect information:
                      HS_FDS_CONNECT_INFO="SERVER=<SERVER NAME>\\<INSTANCE NAME>;DATABASE=<DATABASE>"
                      (HS_FDS_CONNECT_INFO="SERVER=SQLSERVER\\instance2; DATABASE=Northwind" )


                      Method II:
                      ##########
                      This method can be used also to connect to a MS SQL Server not listening
                      on the default port 1433.
                      It is based on using ALIASes:

                      If the MS SQL Server port or an alias is used instead of the servername,
                      the following registry key will be checked:

                      HKEY_LOCAL_MACHINE\
                      SOFTWARE\
                      Microsoft\
                      MSSQLServer\
                      ConnectTo

                      Microsoft provides a Client Configuration Tool to pass all parameters into the
                      registy (for example run PROFILER, click on options and execute the
                      Client Config Tool).
                      If there is no SQL Server client installed on the gateway machine, an idea
                      could be to configure it on the MS SQL Server, export the registry key and
                      import it again on the TG4MSQL machine. Please contact Microsoft about details.


                      This registry key is responsible for the connection details to the MS SQL Server.
                      While dealing with INSTANCEs or changing the MS SQL Server to listen on a
                      networking port other then 1433 (MS default port for SQL Server) the SUBKEY
                      ConnectTo is used to verify the connect details.


                      The Micrososft Client Configuration Utility configures ALIASes.
                      To work with a second instance of the MS SQL Server, an ALIAS for this
                      instance must be added.
                      This tool asks to choose a Serveralias (a name you can define on your own)
                      and to specify the Server name.
                      Normally the server name is equal to the IP address or to the hostname of
                      the MSQ SQL Server machine. But while working with instances the instance name
                      must be specified as well. The instance name is separated by TWO back slashes
                      from the server name.

                      For a better explanation the value for for the Server alias is "instance2alias" and
                      for the server name it is MSSQLSERVER. The sample name of the second instance is
                      INSTANCE2.

                      In the config tool type instance2alias into the entry filed of the server alias
                      and type MSSQLSERVER\\INSTANCE2 as server name.

                      Furthermore the default option is to figure out the MS SQL Server Ports dynamically.
                      If you do not want to detect them automatically, add the port of your SQL
                      Server is listening on; default is 1433. For this example port 1868 is used.


                      After applying this the ALIAS is saved into the registry:

                      [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]

                      and it contains the following connect information:
                      "instance2alias"="DBMSSOCN,MSSQLSERVER\\INSTANCE2,1868"

                      The first parameter is the SQL Server alias, the second is a MS key word and
                      the next value is the hostname delimited by two back slashes from the instance
                      name.

                      Comment: Default instances do not have an entry here.
                      The last parameter is the port if it is not detected automatically.

                      If you have any questions about creating SQL Server aliases or what to
                      fill into the according columns, please contact Microsoft Support!
                      There is also a good note in the MS knowledge base:
                      Microsoft Knowledge Base Article - 265808
                      INF: How to Connect to an SQL Server 2000 Named Instance
                      with the Previous Version's Client Tools



                      Back to the Gateway configuration:
                      Oracle Transparent Gateway is able to work with MS SQL Server instances;
                      instead of specifying the <servername> at HS_FDS_CONNECT_INFO, the
                      <alias> must be specified.

                      For this example the parameter looks like:
                      HS_FDS_CONNECT_INFO = instance2alias.master
                      • 8. Re: MS SQL Server to Oracle
                        450238
                        Thanks for the great post David. The documentation was okay if sqlserver and oracle were running on the same machine, but in the real world they are usually on different boxes. Your outline worked perfectly for me.

                        Thanks,
                        Scott Nadeau
                        • 9. Re: MS SQL Server to Oracle
                          427643
                          Hi David

                          Do you know if the functionality for Heterogeneous Services is provided free in 9i or 10g ?

                          Thanks
                          Kezie
                          • 10. Re: MS SQL Server to Oracle
                            450397
                            I don't find Oracle Transparent Gateways 10.1.0.2 in the Oracle Installation CD.
                            Somebody can tell me.

                            Message was edited by:
                            user447394
                            • 11. Re: MS SQL Server to Oracle
                              422218
                              Kezie,

                              My understanding is that the license for Generic Connectivity (ODBC/OLEDB) is part of your database license (ie no additional charge), but the other Transparent Gateways are licensed seperately. Please check with your account manager.


                              Tak
                              • 12. Re: MS SQL Server to Oracle
                                user512793
                                You write: The bad news is that perfomance is terrible. Hopefully, I can figure that out too.

                                I have similar experience with the gateway performance; can you, please, tell me, what
                                steps did you take to make the performance better? Thanks.
                                • 13. Re: MS SQL Server to Oracle
                                  518415
                                  Hello DLB,
                                  I am also working on the same line.
                                  I have SqlServer2000 and oracle10g running on the same server.
                                  I have followed every step mentioned by you (and not going by the link above).
                                  But still I am getting errors after I try to view the table data of sql server using my dblink.
                                  Error is as follows
                                  SQL> create database link lnk connect to km identified by km using 'tg4msql';

                                  Database link created.

                                  SQL> select * from employees@lnk;
                                  select * from employees@lnk
                                  *
                                  ERROR at line 1:
                                  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                                  [Transparent gateway for MSSQL]DRV_InitTdp: errors.h (937): ; [Microsoft][ODBC
                                  SQL Server Driver][SQL Server]Login failed for user 'KM'. (SQL State: 00000;
                                  SQL Code: 18456)
                                  ORA-02063: preceding 2 lines from LNK


                                  Now I am going to give u the files I changed

                                  1.The gateway files are located in E:\oracle\tg4msql\admin
                                  Inittg4msql file looks like

                                  HS_FDS_CONNECT_INFO=stlap02174.TechMahindra.com.Northwind
                                  HS_FDS_TRACE_LEVEL=DEBUG
                                  HS_FDS_RECOVERY_ACCOUNT=RECOVER
                                  HS_FDS_RECOVERY_PWD=RECOVER

                                  ----------------------------------------------------------------------------------------------------------------

                                  2.Gateway listener file looks like
                                  SID_LIST_LISTENER =
                                  (SID_LIST =
                                  (SID_DESC =
                                  (PROGRAM = extproc)
                                  (SID_NAME = PLSExtProc)
                                  (ORACLE_HOME = E:\oracle)
                                  )
                                  (SID_DESC =
                                  (PROGRAM = tg4msql)
                                  (SID_NAME = tg4msql)
                                  (ORACLE_HOME = E:\Oracle)
                                  )
                                  )

                                  LISTENER =
                                  (DESCRIPTION_LIST =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT = 1521))
                                  )
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
                                  )
                                  )

                                  --------------------------------------------------------------------------------------------------
                                  3.Gateway tnsnames.ora file looks like

                                  TG4MSQL =
                                  (DESCRIPTION =
                                  (ADDRESS_LIST =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT = 1521))
                                  )
                                  (CONNECT_DATA =
                                  (SERVICE_NAME = tg4msql)
                                  )
                                  (HS = OK)
                                  )

                                  Database files are given in the very next post
                                  • 14. Re: MS SQL Server to Oracle
                                    518415
                                    3.Database listener.ora file in E:\oracle\network\admin looks like


                                    SID_LIST_LISTENER =
                                    (SID_LIST =
                                    (SID_DESC =
                                    (PROGRAM = extproc)
                                    (SID_NAME = PLSExtProc)
                                    (ORACLE_HOME = E:\oracle)
                                    )
                                    (SID_DESC =
                                    (PROGRAM = tg4msql)
                                    (SID_NAME = tg4msql)
                                    (ORACLE_HOME = E:\Oracle)
                                    )
                                    (SID_DESC =
                                    (GLOBAL_DBNAME = sudan)
                                    (ORACLE_HOME = e:\oracle)
                                    (SID_NAME = sudan)
                                    )
                                    )

                                    LISTENER =
                                    (DESCRIPTION_LIST =
                                    (DESCRIPTION =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT = 1521))
                                    )
                                    (DESCRIPTION =
                                    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
                                    )
                                    )
                                    =====================================================

                                    4.Database tnsnames.ora file in the same directory apart from other entries has an entry like

                                    TG4MSQL.WORLD =
                                    (DESCRIPTION =
                                    (ADDRESS_LIST =
                                    (ADDRESS = (PROTOCOL = TCP)(HOST = stlap02174.TechMahindra.com)(PORT = 1521))
                                    )
                                    (CONNECT_DATA =
                                    (SERVER = DEDICATED)
                                    (SERVICE_NAME = tg4msql)
                                    )
                                    (HS = OK)
                                    )

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

                                    5.Database server sqlnet.ora looks like

                                    NAMES.DEFAULT_DOMAIN = world

                                    NAMES.DEFAULT_ZONE = world

                                    SQLNET.AUTHENTICATION_SERVICES= (NTS)

                                    NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, ONAMES, HOSTNAME)

                                    DEFAULT_SDU_SIZE = 8761

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

                                    That s all I did and after which i created database link and tried to connect which returned the error like

                                    ERROR at line 1:
                                    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                                    [Transparent gateway for MSSQL]DRV_InitTdp: errors.h (937): ; [Microsoft][ODBC
                                    SQL Server Driver][SQL Server]Login failed for user 'KM'. (SQL State: 00000;
                                    SQL Code: 18456)
                                    ORA-02063: preceding 2 lines from LNK

                                    Please let me know if I missed out anything or anything else needs to be done .
                                    Please help me as I have been working on this for many days and its going nowhere.

                                    Thanks and Regards
                                    1 2 Previous Next