5 Replies Latest reply on Apr 4, 2016 7:56 AM by Rati Todua

    link from oracle to ms sql

    Rati Todua

      hello senior dbas.

       

      i am trying to create link from oracle to ms sql. ms sql 2014 is on windows 7 and oracle 10.2 is on linux redhat 5;

      i installed gateway fro ms sql

      my  gateway location:

      /opt/app/oracle/product/10.2.0/db_1/dg4msql/admin

       

      initdg4msql.ora file:

      HS_FDS_CONNECT_INFO=192.168.**.**:1433//NitgenAccessManager

      HS_FDS_TRACE_LEVEL=OFF                                                                                                             

      HS_FDS_RECOVERY_ACCOUNT=RECOVER                                                                                                    

      HS_FDS_RECOVERY_PWD=RECOVER

       

      my tnsnames.ora

       

      dg4msql  =                                                                                                                         

        (DESCRIPTION=                                                                                                                    

          (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521))                                                                

          (CONNECT_DATA=(SID=dg4msql))                                                                                                   

          (HS=OK)                                                                                                                        

        )

       

      my listener.ora

       

      SID_LIST_LISTENER =                                                                                                                

        (SID_LIST =                                                                                                                      

          (SID_DESC =                                                                                                                    

            (SID_NAME = PLSExtProc)                                                                                                      

            (ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1)                                                                          

            (PROGRAM = extproc)                                                                                                          

          )                                                                                                                              

          (SID_DESC =                                                                                                                    

            (SID_NAME = dg4mssql)                                                                                                        

            (ORACLE_HOME = /opt/app/oracle/product/10.2.0/db_1)                                                                          

            (PROGRAM = dg4mssql)                                                                                                                                            

          )                                                                                                                              

        )                                                                                                                                

                                                                                                                                         

      LISTENER =                                                                                                                         

        (DESCRIPTION_LIST =                                                                                                              

          (DESCRIPTION =                                                                                                                 

            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))                                                                                 

            (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))                                                      

          )                                                                                                                              

        )         

       

       

      when i try to reboot listener:

      TNS-01201: Listener cannot find executable /opt/app/oracle/product/10.2.0/db_1/bin/dg4mssql for SID dg4mssql

       

      how can i change path and where should this dg4mssql file be?

       

      thanks

        • 1. Re: link from oracle to ms sql
          Rati Todua

          how foolish of me. the problem was that in tnsnames i have "dg4msql" while in listener i have "dg4mssql" with double s; i corrected it and now i get another error.

           

          i can tnsping dg4msql;

          when i create link and try to connect i get following error:

           

          [1]: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

          [1]: [Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation. {08001,NativeErr = 11}[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionOpen (()). {01000,NativeErr = 11}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}

          [1]: ORA-02063: preceding 2 lines from MSSQL

           

          i guess something is wrong with connection but i do now know why.

           

          i can ping successfully my ip of windows 7 where the sql server 2014 is installed and port is correct and database name is correct as well so why i have this error?

           

          i hope this error is not as embarassing as last one...

           

          thanks.

          • 2. Re: link from oracle to ms sql
            EdStevens

            2960138 wrote:

             

            how foolish of me. the problem was that in tnsnames i have "dg4msql" while in listener i have "dg4mssql" with double s; i corrected it and now i get another error.

             

            i can tnsping dg4msql;

            when i create link and try to connect i get following error:

             

            [1]: ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

            [1]: [Oracle][ODBC SQL Server Driver][libssclient24]General network error. Check your network documentation. {08001,NativeErr = 11}[Oracle][ODBC SQL Server Driver][libssclient24]ConnectionOpen (()). {01000,NativeErr = 11}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}

            [1]: ORA-02063: preceding 2 lines from MSSQL

             

            i guess something is wrong with connection but i do now know why.

             

            i can ping successfully my ip of windows 7 where the sql server 2014 is installed and port is correct and database name is correct as well so why i have this error?

             

            i hope this error is not as embarassing as last one...

             

            thanks.

            You might find my article on Oracle Transparent Gateway to be helpful.

            • 3. Re: link from oracle to ms sql
              mxallen-Oracle

              The error is coming from SQL*Server and can have many causes.

               

              If there is a firewall between the machine where the gateway is running and the Windows machine with SQL*Server, check to make certain it's not preventing the connection being made successfully.

              Other things to check are -

               

              1. Is SQL*Server authentication being used for connections to SQL*Server ? The gateway will not be able to connect if you are using Windows authentication.

              2. Check the privileges of the user in the database link on SQL*Server.

              3. From the machine where the gateway is running make sure you can telnet to IP address in the gateway configuration for the SQL*Server location.

              4. Make sure SQL*Server is using the port specified in the gateway configuration for SQL*Server, the default is1433.  Double check the format of the HS_FDS_CONNECT_INFO settinng.

              5. Is SQL*Server running as a cluster ? If it is then review this Microsoft information -

              http://support.microsoft.com/kb/307336

               

              Hope this helps!

              Matt

              • 4. Re: link from oracle to ms sql
                Rati Todua

                thank you for your response and sorry for me being late.

                 

                as you said error is because i am using windows authentication. other options seems to be rightly configured.

                i am trying to create sql server authentication now but it seems that i am not doing something right as i can not connect. i am searching the answer in sql server forums, i expect that as long as i create user to connect to sql server without windows authentication, i will be able to create link successfully, if not i will respond again to this topic, therefore i left it open for now...

                 

                thanks again

                • 5. Re: link from oracle to ms sql
                  Rati Todua

                  finally i managed to create user, then i managed to give enough permissions and all is working well. thanks again.