1 2 Previous Next 17 Replies Latest reply: Apr 26, 2011 11:32 PM by Kgronau-Oracle RSS

    DBLink from Oracle to Sql Server

    631262
      http://download-west.oracle.com/docs/cd/A8...admin.htm#15089
      Getting a db-link between Oracle and SQL Server is not hard:
      1- Install ODBC
      2- Setup tnsnames.ora for odbc connectivity (authentication=none)
      3- Create db-link
      1 and 3 are easy.

      2 is a mystery. I'll have to do this with both sqlserver 2000 and sqlserver 2005 if that makes a difference.

      Let's suppose my sql server machine name is "DAVID".
      My Sql Server instance name is "DAVID\MYSQLSERVER".
      The Sql Server catalog I want to connect to is "MYCATALOG".
      The user id and password are "MYUSER" and "MYPASSWORD" respectively.

      My oracle tns listener port is 1521.
      My sql server port is 1433.

      Could some kind soul show me an example tnsnames.ora and listener.ora and (if needed) sqlnet.ora entry to make this connection. I've already worked 70 hours this week and I don't want to spend my weekend guessing!

      Thanks
        • 1. Re: DBLink from Oracle to Sql Server
          ebangma-Oracle
          I do not know if you are running Oracle on Windows or Unix, but on MetaLink there are some good documents on howto setup an Generic Connectivity (aka HSODBC) connection from Oracle to SQL Server.

          Basically you need 4 configuration files:
          1. listener.ora - define the entry point for the HSODBC Agent
          2. tnsnames.ora - define the SQL*Net alias to point to the HSODBC Agent
          this tnsnames.ora file needs to be accessible by the Oracle database in which the database link to the HSODBC Agent is defined
          3. init<hsodbc>.ora - define the connection to the remote database
          this is the HSODBC Agent configuration file
          4. odbc.ini - define the ODBC setup to the remote database
          On Windows: Data Sources (ODBC): System DSN
               
          To start with I advice you to obtain the following MetaLink document if your running on Windows:
          DocID: 109730.1 - How to setup generic connectivity (HSODBC) for 32 bit Windows (Windows NT, Windows 2000, Windows XP, Windows 2003)
                              
          Oh, I assume you are using Oracle 10g, for 11g HSODBC has been replaced by DG4ODBC:
          DocID: 466225.1 - How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit

          If your Oracle database is not running on Windows, let me know so I can point you to some other MetaLink documents.

          Regards,
          Ed
          • 2. Re: DBLink from Oracle to Sql Server
            631262
            Thanks for the info. I didn't have metalink access when I posted here.

            I am running on windows for both servers.

            I've tried setting up the tnsnames.ora, listener.ora, and inithsodbc.ora files.

            You post mentioned an odbc.ini file, but the metalink doc never mentioned it.

            I'm getting an "ORA-12154: TNS:could not resolve the connect identifier specified", so my guesses are apparently wrong.

            Given the info in my first post, what should the values be in these files?

            Thanks!
            • 3. Re: DBLink from Oracle to Sql Server
              631262
              Let's suppose my sql server machine name is "DAVID".
              My Sql Server instance name is "DAVID\MYSQLSERVER".
              The Sql Server catalog I want to connect to is "MYCATALOG".
              The user id and password are "MYUSER" and "MYPASSWORD" respectively.

              My oracle tns listener port is 1521.
              My sql server port is 1433.

              My sqlserver odbc connection name is 'MYODBC'.
              When I run the test of the connection, it works fine.


              Here's the tnsnames.ora stuff I added:

              hsodbc =
              (DESCRIPTION=
              (ADDRESS = (PROTOCOL=tcp)
              (HOST = LOCALHOST)
              (PORT = 1521)
              (CONNECT_DATA = ( SID = hsodbc )
              )
              (HS = OK)
              )

              Here's the listener.ora stuff I added to the end of the SID_LIST_LISTENER

              (SID_DESC =
              (SID_NAME = hsodbc)
              (ORACLE_HOME = C:\oracle\product\10.2.0\db_2)
              (PROGRAM = hsodbc)
              )

              Here's the contents of my inithsodbc.ora file:

              # This is a sample agent init file that contains the HS parameters that are
              # needed for an ODBC Agent.

              #
              # HS init parameters
              #
              HS_FDS_CONNECT_INFO = MYODBC
              HS_FDS_TRACE_LEVEL = off

              #
              # Environment variables required for the non-Oracle system
              #
              #set <envvar>=<value>

              Here's my database link command:

              CREATE PUBLIC DATABASE LINK MYODBC using 'hsodbc';

              Here's my query:

              select * from test@MYODBC;
              • 4. Re: DBLink from Oracle to Sql Server
                ebangma-Oracle
                Thanks for providing the listener.ora & tnsnames.ora .

                odbc.ini is used on unix based platforms. On Windows the "Control Panel -> Administrative Tools -> Data Sources (ODBC)" is used to create a System DSN to point to the correct SQL Server database.

                THe ORA-12154 means that the SQL*Net setup (listener.ora, sqlnet.ora & tnsnames.ora) is incorrect/incomplete.

                Looking closer at the provided tnsnames.ora entry I see a mistake:
                hsodbc =
                (DESCRIPTION=
                (ADDRESS = (PROTOCOL=tcp)
                (HOST = LOCALHOST)
                (PORT = 1521)
                (CONNECT_DATA = ( SID = hsodbc )
                )
                (HS = OK)
                )

                The CONNECT_DATA is at the wrong 'level', the corrected entry has to look like:
                hsodbc =
                (DESCRIPTION=
                (ADDRESS = (PROTOCOL=tcp)
                (HOST = LOCALHOST)
                (PORT = 1521)
                )
                (CONNECT_DATA = ( SID = hsodbc )) <<== do not forget extra closing bracket!
                (HS = OK)
                )

                Please remember that the Oracle database must use this tnsnames.ora file. One other question: what is the content of the sqlnet.ora file (when present)?

                Regards,
                Ed
                • 5. Re: DBLink from Oracle to Sql Server
                  631262
                  Bless you! That did the trick!

                  Here's my sqlnet.ora file, just for others who might have this task to do.

                  # sqlnet.ora Network Configuration File: C:\oracle\product\10.2.0\db_2\network\admin\sqlnet.ora
                  # Generated by Oracle configuration tools.

                  # This file is actually generated by netca. But if customers choose to
                  # install "Software Only", this file wont exist and without the native
                  # authentication, they will not be able to connect to the database on NT.

                  SQLNET.AUTHENTICATION_SERVICES= (NTS)

                  NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
                  • 6. Re: DBLink from Oracle to Sql Server
                    ebangma-Oracle
                    Good to hear that you now have a working connection!

                    The reason why I asked for the sqlnet.ora was to check for a NAMES.DEFAULT_DOMAIN parameter value. But it is not set, hence the "hsodbc = (DESCRI... " is ok.

                    If for example sqlnet.ora parameter NAMES.DEFAULT_DOMAIN is set to WORLD, your tnsnames.ora entry has to be: "hsodbc.WORLD = DESCRI... "

                    Have fun with HSODBC,
                    Ed
                    • 7. Re: DBLink from Oracle to Sql Server
                      393479
                      There's a good tutorial on HSODBC here:

                      http://www.nixblog.org/post/2008/05/18/Installing-and-configuring-Oracle-Heterogenous-Services-for-SQLServer

                      Regards
                      • 8. Re: DBLink from Oracle to Sql Server
                        763305
                        Thanks Dear
                        i got a lot of help and finally succeeded in connecting a link between Oracle and SQL Server.

                        Really good and informative document.
                        • 9. Re: DBLink from Oracle to Sql Server
                          user497841
                          Hello,

                          I know this post was from a while ago but you've mentioned you could refer to some docs on metalink for oracle on unix to access sqlserver.

                          my oracle versiion 9i on unix platform.

                          Any help is much appreciated.

                          Thanks!
                          • 10. Re: DBLink from Oracle to Sql Server
                            Mkirtley-Oracle
                            Hi,
                            First, I'm sure you are aware that Oracle v9 products have been desupported for a long time. Also, since this thread was opened all the v10 gateways are now desupported and have been replaced by the 11g Database Gateways.
                            These are certified with Oracle 9i RDBMS as long as it is at 9.2.0.8 and which has had patch 5965763 applied.
                            The good news is that the 11g Database Gateway for SQL*Server (DG4MSQL) is now available to run standalone on Unix platforms, so you you could install that on the same machine as your RDBMS in a separate ORACLE_HOME and use that to access SQL*Server.
                            You could also use the 11g Database Gateway for ODBC (DG4ODBC) which needs a third party ODBC driver but can also be used standalone. DG4MSQL needs a license but Dg4ODBC is included in your RDBMS license.
                            Of course, you can also install either of these standalone on a Windows machine and access from your 9i RDBMS on Unix.
                            See the following notes on My Oracle Support for details -

                            Note.466228.1 How to Setup DG4ODBC on Linux x86 32bit
                            Note.561033.1 How to Setup DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX)
                            Note.466225.1 How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit

                            Note.437374.1 How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) Release 11 on Linux
                            Note.466267.1 How to Setup DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit
                            Note.1086365.1 How to Setup DG4MSQL (Database Gateway for MS SQL Server) on a 64bit Windows
                            Note.562509.1 How to Setup DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX)

                            Regards,
                            Mike

                            Edited by: mkirtley on Jun 30, 2010 9:35 AM
                            • 11. Re: DBLink from Oracle to Sql Server
                              846947
                              Hi,

                              I have created an System ODBC Connection named
                              'sunilserver'

                              Then I have created initdg4odbc.ora having below data.
                              HS_FDS_CONNECT_INFO = sunilserver
                              HS_FDS_TRACE_LEVEL = 0

                              In Listener.ora file I have added below data

                              (SID_DESC=
                              (SID_NAME=dg4odbc)
                              (ORACLE_HOME=C:\product\11.2.0\dbhome_1)
                              (PROGRAM=dg4odbc)
                              )


                              In tnsnames.ora I have added these

                              dg4odbc =
                              (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = XENO--PC\SQLEXPRESS)(PORT = 1521))
                              (CONNECT_DATA = (SID=dg4odbc))
                              (HS=OK)
                              )

                              When I make this statement lsnrctl reload and lsnrctl stat everything looks ok.

                              After when I have tried below

                              create public database link sunilsqlserver connect to sa identified by Welcome1 using 'sunilserver';

                              select * from dbo.emp@sunilsqlserver;

                              CREATE PUBLIC DATABASE LINK sunilserver using 'dg4odbc';
                              select * from dbo.emp@sunilserver;

                              I am getting this error.
                              ORA-12154: TNS:could not resolve the connect identifier specified
                              12154. 00000 - "TNS:could not resolve the connect identifier specified"

                              Which step I have missed please help me.

                              Thanks,
                              Sunil Jena
                              • 12. Re: DBLink from Oracle to Sql Server
                                Kgronau-Oracle
                                12154 means the alias in the using cluase of your database link statement isn't found in the tnsnames.ora file.

                                In which tnsnames.ora file (the one of the Oracle database or the one in the gateway home) did you add the tns alias dg4odbc?
                                What is the content of the sqlnet.ora file?

                                BTW, for a database link to a MS SQl Server you also need to use a username and a password like: CREATE PUBLIC DATABASE LINK sunilserver connect to "sa" identified by " " using 'dg4odbc';
                                • 13. Re: DBLink from Oracle to Sql Server
                                  846947
                                  Hi,

                                  In tnsnames.ora I have added below

                                  dg4odbc =
                                  (DESCRIPTION =
                                  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
                                  (CONNECT_DATA = (SID=dg4odbc)) (HS=OK)
                                  )

                                  my sqlnet file is like this
                                  SQLNET.AUTHENTICATION_SERVICES= (NTS)

                                  NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

                                  when I am pinging tnsping dg4odbc it is pinging.

                                  But for this
                                  CREATE PUBLIC DATABASE LINK sunilserver connect to "sa" identified by "Welcome1" using 'dg4odbc';
                                  select * from emp@sunilserver;

                                  I am getting below error:
                                  ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
                                  ORA-28541: Error in HS init file on line 7.
                                  ORA-02063: preceding 2 lines from SUNILSERVER
                                  28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
                                  *Cause:    The cause is explained in the forwarded message.
                                  *Action:   See the non-Oracle system's documentation of the forwarded
                                  message.
                                  Error at Line: 14 Column: 18

                                  Thanks,
                                  Sunil Jena
                                  • 14. Re: DBLink from Oracle to Sql Server
                                    Kgronau-Oracle
                                    Please post the whole initdg4odbc.ora file - the file you've posted earlier contains only 2 lines.
                                    1 2 Previous Next