1 2 Previous Next 22 Replies Latest reply on Jul 22, 2014 11:47 AM by FrAGarcher

    DB Link to SQL Server

    FrAGarcher

      Hi All

      I'm trying to understand how to configure a DB Link to MS SQL Server, I've learned a lot of links but still cannot see where I'm wrong.

      I tested various kinds of configuration:

       

      Oracle Database 11g  11.2.0.3.0 on AIX   --->   Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (64-bit) on Windows Server 2008R2 SP1  --->  MS SQL server 2008 R2 x64 SP1 on Windows Server 2008R2 SP1

      Oracle Database 11g  11.2.0.3.0   on AIX --->   Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2  --->  MS SQL server 2008 R2 x64 SP1 on Windows Server 2008R2 SP1

      Oracle Database 11g  11.2.0.3.0   on AIX --->   Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2  +  MS SQL server 2005 x32 SP4 on the same Windows Server.

       

       

      I followed instructions like

       

      Configuring Oracle Database Gateway for ODBC

       

      http://www.youtube.com/watch?v=rEZOiZrc708

       

      etc.

       

      But nothing works (((

       

      Help me please understand what is wrong?

       

      Let me show the latest configuration where the Oracle Database Gateway  and the MS SQL server 2005 x32 SP4  are installed on the same Windows Server 2003R2 x32 SP2.

       

      First I created a system DSN as it's described, for example, here Re: DB Link to SQL Server

       

      Windows Registry Editor Version 5.00
      
      [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\dg4msql]
      "Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
      "Description"="dg4msql"
      "Server"="MSVO-TG4MSQL"
      "LastUser"="RECOVER"
      
      

       

       

      then I configured .ora config files

      here are  my config files:

       

      initdg4msql.ora - on Oracle Database Gateway

       

      # 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.48.104.18]//AdventureWorks
      HS_FDS_TRACE_LEVEL=OFF
      HS_FDS_RECOVERY_ACCOUNT=RECOVER
      HS_FDS_RECOVERY_PWD=RECOVER
      HS_FDS_RESULTSET_SUPPORT=TRUE
      
      

       

       

      listener.ora  - on Oracle Database Gateway

       

       

      # listener.ora Network Configuration File: F:\oracle\network\admin\listener.ora
      # Generated by Oracle configuration tools.
      
      SID_LIST_LISTENER=
         (SID_LIST=
            (SID_DESC= 
               (SID_NAME=dg4msql)
               (ORACLE_HOME=F:\oracle)
               (PROGRAM=dg4msql)
            )
         )
      
      
      LISTENER =
        (DESCRIPTION_LIST =
          (DESCRIPTION =
            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521))
          )
        )
      
      ADR_BASE_LISTENER = F:\oracle
      
      

       

       

      tnsnames.ora - on Oracle Database Gateway

       

       

      # tnsnames.ora Network Configuration File: F:\oracle\network\admin\tnsnames.ora
      # Generated by Oracle configuration tools.
      
      dg4msql = 
       (DESCRIPTION=
        (ADDRESS = (PROTOCOL = TCP)(HOST=10.48.104.18)(PORT=1521))
        (CONNECT_DATA = (SID = dg4msql))
        (HS=OK)
       )
      
      
      
      EXTPROC_CONNECTION_DATA =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
          )
          (CONNECT_DATA =
            (SID = PLSExtProc)
            (PRESENTATION = RO)
          )
        )
      
      

       

      Create DB Link on Oracle Database server

       

       

      Create database link BTEST.WORLD connect to recover identified by “nnnn” using ‘(DESCRIPTION =
              (ADDRESS_LIST =
              (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))
              )
              (CONNECT_DATA =
              (SID = tg4msql))
              (HS = OK))’
      

       

      tnsnames.ora - on Oracle Database server

       

      1. SQLLAB.world=

      (DESCRIPTION =

              (ADDRESS_LIST =

              (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))

              )

              (CONNECT_DATA =

              (SID = tg4msql))

              (HS = OK))

        • 1. Re: DB Link to SQL Server
          mxallen-Oracle

          Hello,

           

          My first question is did you actually install Database Gareway for SQL Server (DG4MSQL) on two different machines (Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (64-bit) on Windows Server 2008R2 SP1 and Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2).?

           

          If so, did you use the 64-bit version on the 64-bit server and the 32-bit version on the 32-bit server?

           

          Also, DG4MSQL is licensed separately from the Oracle Database.  This is to simply let you know.

           

          As far as the information in the configuration files, the glaring error is with the tnsnames.ora file entry in the Oracle Database homes.

           

          You have this:

           

          SQLLAB.world=(DESCRIPTION =

                  (ADDRESS_LIST =

                  (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))

                  )

                  (CONNECT_DATA =

                  (SID = tg4msql))

                  (HS = OK))


          The gateway listener shows this:


           

          1. SID_LIST_LISTENER= 
          2.    (SID_LIST= 
          3.       (SID_DESC=  
          4.          (SID_NAME=dg4msql) 
          5.          (ORACLE_HOME=F:\oracle) 
          6.          (PROGRAM=dg4msql) 
          7.       ) 
          8.    ) 
          9. LISTENER = 
          10.   (DESCRIPTION_LIST = 
          11.     (DESCRIPTION = 
          12.       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521)) 
          13.     ) 
          14.   ) 
          15. ADR_BASE_LISTENER = F:\oracle 

           

          The SID values do not match nor the host values (although the ip address of msvo-tg4msql might be
          10.48.104.18)

           

          With that said, modify the tnsnames.ora entry in the Oracle DB home to read:

           

          SQLLAB.world=(DESCRIPTION =

                  (ADDRESS_LIST =

                  (ADDRESS = (PROTOCOL = TCP )(HOST =10.48.104.18)(PORT = 1521))

                  )

                  (CONNECT_DATA =

                  (SID = dg4msql))

                  (HS = OK))


          Test and see if this works.


          Regards,

          Matt

          • 2. Re: DB Link to SQL Server
            FrAGarcher

            mxallen wrote:

             

            Hello,

             

            My first question is did you actually install Database Gareway for SQL Server (DG4MSQL) on two different machines (Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (64-bit) on Windows Server 2008R2 SP1 and Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2).?

             

            If so, did you use the 64-bit version on the 64-bit server and the 32-bit version on the 32-bit server?

             

            .

             

            Yes I did so. During my test there were two separate installations.

            First, I installed all 64-bit versions: both Database Gareway for SQL Server and MS SQL Server 2008R2. Each application were instaled on its own dedicated Windows-server.

            Second I installed all 32-bit versions: both Database Gareway for SQL Server and MS SQL Server 2005 SP4. Each application were instaled on the same Windows-server.

            And yes, I know about licensing model.

             

            mxallen wrote:

             

             

            SQLLAB.world=(DESCRIPTION =

                    (ADDRESS_LIST =

                    (ADDRESS = (PROTOCOL = TCP )(HOST =10.48.104.18)(PORT = 1521))

                    )

                    (CONNECT_DATA =

                    (SID = dg4msql))

                    (HS = OK))


            Test and see if this works.


            Regards,

            Matt

            No, still not works...

            Actually we already have similar system installed in our production. But slightly different. If I'm not wrong: It is the  Transparent Gareway for SQL Server that was distributed with Oracle 10g database server. Gateway is installed alongside MS SQL Server 2005 SP4 x32 on the same Windows Server 2003R2 SP2 x32.

            Production complex is running normally. Initially I tried to copy .ora cofig files from the production server to the test server, but of course failed.

            • 3. Re: DB Link to SQL Server
              mxallen-Oracle

              What is the error you are seeing?

               

              - Matt

              • 4. Re: DB Link to SQL Server
                FrAGarcher

                When I try tor perform some SELECT command I get an error like this: [1]: (Error): ORA-28545: error diagnosed by Net8 when connecting to an agent Unable to retrieve text of NETWORK/NCR message 12564 ORA-02063: preceding 2 lines from ETWEBTEST

                 

                http://i58.tinypic.com/6qckyt.jpg

                • 5. Re: DB Link to SQL Server
                  Kgronau-Oracle

                  ORA-28545 is a configuration issue.

                  Could you please post:

                  1. Gateway listener,

                  2. lsnrctl status output for the gateway listener. Open a DOS command line Window and execute: F:\oracle\bin\lsnrctl status

                  3. from the same command line window: F:\oracle\bin\dg4msql

                  4. tnsnames.ora entry from the Oracle database

                  5. from the Oracle database machine execute: tnsping SQLLAB.world

                   

                  - Klaus

                  • 6. Re: DB Link to SQL Server
                    FrAGarcher

                     

                    1. Gateway listener - sorry, Ididn't quite undestand what should I do? Should I post Gateway listener config file? If so - I posted it at the start of the topic:

                     

                     

                    listener.ora  - on Oracle Database Gateway

                     

                     

                    1. # listener.ora Network Configuration File: F:\oracle\network\admin\listener.ora 
                    2. # Generated by Oracle configuration tools. 
                    3. SID_LIST_LISTENER= 
                    4.    (SID_LIST= 
                    5.       (SID_DESC=  
                    6.          (SID_NAME=dg4msql) 
                    7.          (ORACLE_HOME=F:\oracle) 
                    8.          (PROGRAM=dg4msql) 
                    9.       ) 
                    10.    ) 
                    11. LISTENER = 
                    12.   (DESCRIPTION_LIST = 
                    13.     (DESCRIPTION = 
                    14.       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521)) 
                    15.     ) 
                    16.   ) 
                    17. ADR_BASE_LISTENER = F:\oracle 

                     

                     

                    2. I have a problem with it: NLS_LANG is set to RUSSIAN_RUSSIA.CL8MSWIN1251 therefore I get a number of strange symbols instead of normal list. Ah.. I understood Since I have changed NLS_LANG  to AMERICAN_AMERICA.CL8MSWIN1251   I get readable rsponse

                     

                    Here it is:

                     

                     

                    C:\Documents and Settings\Administrator>F:\oracle\bin\lsnrctl status

                     

                    LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 18-JUL-2014 09:42:17

                     

                    Copyright (c) 1991, 2010, Oracle.  All rights reserved.

                     

                    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.48.104.18)(PORT=1521)))

                    STATUS of the LISTENER

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

                    Alias                     LISTENER

                    Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production

                    Start Date                18-JUL-2014 09:39:52

                    Uptime                    0 days 0 hr. 2 min. 25 sec

                    Trace Level               off

                    Security                  ON: Local OS Authentication

                    SNMP                      OFF

                    Listener Parameter File   F:\oracle\network\admin\listener.ora

                    Listener Log File         f:\oracle\diag\tnslsnr\msvo-tg4msql\listener\alert\log.xml

                    Listening Endpoints Summary...

                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.48.104.18)(PORT=1521)))

                    Services Summary...

                    Service "dg4msql" has 1 instance(s).

                      Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...

                    The command completed successfully

                     

                    C:\Documents and Settings\Administrator>

                     

                     

                    3. from the same command line window: F:\oracle\bin\dg4msql

                     

                    Microsoft Windows [Version 5.2.3790]
                    (C) Copyright 1985-2003 Microsoft Corp.

                     

                    C:\Documents and Settings\Administrator>F:\oracle\bin\dg4msql

                     


                    Oracle Corporation --- FRIDAY    JUL 18 2014 09:45:44.890

                     

                    Heterogeneous Agent Release 11.2.0.1.0 - Production  Built with
                       Oracle Database Gateway for MSSQL

                     

                     

                     

                     

                    4. tnsnames.ora - on Oracle Database server

                     

                    1. SQLLAB.world=

                    (DESCRIPTION =

                            (ADDRESS_LIST =

                            (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))

                            )

                            (CONNECT_DATA =

                            (SID = tg4msql))

                            (HS = OK))



                    5. tnsping SQLLAB.world


                     

                    bash-3.2$ tnsping SQLLAB

                     

                    TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.3.0 - Production on 18-JUL-2014 09:36:59

                     

                    Copyright (c) 1997, 2011, Oracle.  All rights reserved.

                     

                    Used parameter files:

                    /u01/app/oracle/product/11.2.0.3/network/admin/sqlnet.ora

                     

                     

                    Used TNSNAMES adapter to resolve the alias

                    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521))) (CONNECT_DATA = (SID = tg4msql)) (HS = OK))

                    OK (0 msec)

                    • 7. Re: DB Link to SQL Server
                      Kgronau-Oracle

                      You didn't correct your database tnsnames.ora file - you still have the wrong SID entry in there- see the tnsping:

                      (CONNECT_DATA = (SID = tg4msql)) (HS = OK))

                       

                      => according to your listener.ora file the SID is dg4msql.

                       

                      So please open the tnsnames.ora file on your Oracle database server and change the SQLLAB alias to:

                      SQLLAB.world=(DESCRIPTION =

                              (ADDRESS_LIST =

                              (ADDRESS = (PROTOCOL = TCP )(HOST =10.48.104.18)(PORT = 1521))

                              )

                              (CONNECT_DATA =

                              (SID = dg4msql))

                              (HS = OK))


                      1 person found this helpful
                      • 8. Re: DB Link to SQL Server
                        FrAGarcher

                        Yes You were right!

                         

                        After I've changed SID I got my data from my test MS SQL database! OMG... just one wrong symbol and two days to dance around ((((

                         

                        , Thank You alot!!

                         

                        But... Just another question.

                        Now I'm trying to select some data from a test table and faced to a problem:

                         

                         

                        SELECT * FROM "Sales"."Store"@ETWEBTEST.WORLD

                         

                         

                        [1]: (Error): ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Oracle][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'Store', database 'AdventureWorks', schema 'Sales'. {42000,NativeErr = 229} ORA-02063: preceding 2 lines from ETWEBTEST

                         

                         

                        But! If I grant sysadmin server role to the user account RECOVER - everything works correctly. If this account belongs only to the public server role - I get the error despite I mapped the RECOVER user account to my test database (AdventureWorks) as db_owner.

                         

                        What I do wrong now? How to restrict the user account to the public server role?

                        • 9. Re: DB Link to SQL Server
                          Kgronau-Oracle

                          The error message is clear - you do not have (when the public server role is granted) the permissions to access that object. You should check that with your SQL Server admin if users with that role can access that object.

                           

                          By the way, the RECOVER account shouldn't be used for normal selects. In the create database link statement you should specify a normal SQL Server username for example "sa". The recover account is only used for distributed transactions where the gateway has to write a temporary record into the HS_TRANSACTION_LOG table. The recover user only needs to own that object and needs to have full permissions on this table. Using that user to select/manipulate other resources is risky as you can find the user's password in the gateway init file (as long as you do not encrypt it).

                          - Klaus

                          • 10. Re: DB Link to SQL Server
                            FrAGarcher

                            Yes the meaning of the error message is quite clear, but since I myself is an administrator of the MS SQL Server I have granted a set of rights for the RECOVERY account . Therefore it was a surprise for me that I can perform select to a table on the MS SQL only in case when the RECOVERY user belongs to the sysadmin role...

                            So I assume that the RECOVERY account should have additional rights to accsess to some system object. But which objects?..

                             

                            -> In the create database link statement you should specify a normal SQL Server username for example "sa". Coould You please explain this in more detail: how can I do this. Pardon me for such a question, I work with the Gateway first time...

                             

                            And one other question: may be there is another way to build communication between Oracle and MS SQL server? I mean I remember that DG4MSQL requires one more license so may be there is a freeware to solve the task?

                             

                            I've learned some resources upont the subject and found out that some DBA use an hsodbc...

                            • 11. Re: DB Link to SQL Server
                              Kgronau-Oracle

                              Regarding the SQL Server privileges, please have a look at the MS documentation - for example start here: http://msdn.microsoft.com/en-gb/library/ms188659.aspx

                              It is also a good idea to open the SQL Server Management Studio - click on the System Database and to check out the Database roles and the permissions granted to public. For example here you see which objects you can access by default. Either grant more privs to that role or use another user who is able to select the objects.

                               

                              Regarding the database link. You currently created it like: create database link <name of your link> connect to "RECOVER" identified by ...

                              Let's assume you want to use a different user - for example let's use the SQL Server admin account (it is up to you to decide if that makes sense or not and matches your security requirements).

                              So first drop the already created link

                              drop database link ETWEBTEST.WORLD;

                              recreate it now with a different UID in the link: create database link ETWEBTEST.WORLD connect to "sa" identified by "<sa's password>" using 'SQLLAB.world';


                              As you said - DG4MSQL requires a license. The free product is called Database Gateway for ODBC. It uses the MS SQL Server ODBC driver to connect to the SQL Server. It is the follow up product of the desupported HSODBC.

                               

                              - Klaus

                              • 12. Re: DB Link to SQL Server
                                Mkirtley-Oracle

                                There is a difference in the functionality between DG4MSQL and DG4ODBC so if you have access to My Oracle Support have a  look at this note -

                                 

                                Functional Differences Between DG4ODBC and Specific Database Gateways (Doc ID 252364.1)

                                 

                                 

                                So it depends on what you want to do with the gateway as to which you use. For example, DG4ODBC does not support distributed transactions.

                                 

                                Regards,

                                Mike

                                • 13. Re: DB Link to SQL Server
                                  FrAGarcher

                                  kgronau

                                   

                                   

                                   

                                  kgronau написал(а):

                                   

                                  Regarding the SQL Server privileges, please have a look at the MS documentation - for example start here: http://msdn.microsoft.com/en-gb/library/ms188659.aspx

                                  It is also a good idea to open the SQL Server Management Studio - click on the System Database and to check out the Database roles and the permissions granted to public. For example here you see which objects you can access by default. Either grant more privs to that role or use another user who is able to select the objects.

                                   

                                  ok, I'll see, thank You.

                                   

                                   

                                  Regarding the database link. You currently created it like: create database link <name of your link> connect to "RECOVER" identified by ...

                                  Let's assume you want to use a different user - for example let's use the SQL Server admin account (it is up to you to decide if that makes sense or not and matches your security requirements).

                                  So first drop the already created link

                                  drop database link ETWEBTEST.WORLD;

                                  recreate it now with a different UID in the link: create database link ETWEBTEST.WORLD connect to "sa" identified by "<sa's password>" using 'SQLLAB.world';

                                   

                                   

                                   

                                   

                                  So If understood rihgt: there may be two accounts - in initdg4msql.ora and in create dblink statement. Am I correct?

                                   

                                   

                                  As you said - DG4MSQL requires a license. The free product is called Database Gateway for ODBC. It uses the MS SQL Server ODBC driver to connect to the SQL Server. It is the follow up product of the desupported HSODBC.

                                   

                                  - Klaus

                                   

                                  would You please give me points to an instruclion how to setup and to configure  the  DG4ODBC?

                                   

                                  Here, in the conference I found a topic with a link: How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1). But it's about configuring DG4ODBC on 64bit Unix OS. And what about similar instructions on windows systems?

                                   

                                   

                                  I found this: How to setup dg4odbc

                                   

                                  Is this instruction correct?

                                   

                                   

                                  Download the Gateway:

                                   

                                  In case the 11gR2 64 install does not install the latest g4odbc gateway, do as follow:

                                   

                                  Click on the Patches and Updates tab in My Oracle Support. Put in patch number 10098816 and

                                   

                                  Microsoft Windows x64 (64-bit) for the platform.

                                   

                                  This is the 11.2.0.2 database patchset. The gateways distribution is the 5th file. You can

                                   

                                  verify that by looking at the patch readme.

                                   

                                  This file is the complete gateway cd. You only need to download this one file. When you run the setup, it will create the 11.2.0.2 oracle home

                                   

                                  and install the gateway that you select while running the setup. Select the odbc gateway (dg4odbc).

                                   

                                   

                                   

                                   

                                  But I still didn't understand: where can I get an installation pack of DG4ODBC?

                                  • 14. Re: DB Link to SQL Server
                                    FrAGarcher

                                    Well

                                    Actually the task I have to consider is migration to a new server.

                                    Now I have a souce system:

                                    1. Oracle Database Server 10g Release 1

                                     

                                    +

                                     

                                    2. MS SQL Server  2005 SP4 and  Oracle TG4MSQL  - both installed on the same windows server 2003 R2SP2.

                                     

                                    I have to consider how to migrate an applicational database placed on the MS SQL Server. Additional problem is that there is no documentation upon this source system and I don't know who performed deployment of the system. So I have to "explore" it from the groud zero...

                                     

                                    Another problem is that the applicational database is planned to migrate to our corporate "big-n-fat" MS SQL Server 2008 R2 where we are going to consolidate as more applicatioanl database from separated ms sql servers  as we can. So I cannot do deployment on this "big-n-fat" mssql-server anything including the TG4MSQL/DG4ODBC because "big-n-fat" is in windows failover cluster.

                                     

                                    Sorry I realyze that two many words, but I try to explain a background..

                                     

                                    What if to install Oracle TG4MSQL to a dedicated Windows Server 2008 R2 SP1? May be it will be the simpliest way - just to copy configuration from the source system on a new one? But then where can I get TG4MSQL software installation package for Windows? Is it still supported?

                                    1 2 Previous Next