1 2 Previous Next 16 Replies Latest reply: Aug 29, 2013 1:59 AM by Mkirtley-Oracle RSS

    connection with ms sql 2005

    user9275313

      Dear all,

       

      I have two scenario to make connection with sql server 2005.

      1)sqlserver and database 11g both on windows xp 32 bit  .

      Is oracle Gateway best option in such scenario?

      2)Sqlserver on windows xp and 11g database on solaris 64 bit.

      Please provide step by step documents for this.

       

      Thanks


        • 1. Re: connection with ms sql 2005
          Mkirtley-Oracle

          Hi,

            You can use an Oracle Database Gateway for both scenarios.

          There are different combinations you can have depending how you want to setup your systems -

           

          1. sqlserver and database 11g both on windows xp 32 bit

          You can install the gateway either as part of the existing RDBMS install or in a separate ORACLE_HOME.  It is usually better to install the gateway standalone in a separate OH as this makes maintenenace easier.

           

          2. Sqlserver on windows xp and 11g database on solaris 64 bit.

          You can either -

          a) install the gateway on the Solaris 64-bit machine to connect to the SQL*Server on Wiondows. Again you can install standalone or as part of the existing RDBMS

          b) from the RDBMS on Solaris 64-bit you can access a gateway on the Windows machine. If this is the same Windows machine as in scenario 1 then you can use that gateway which is already installed.

           

          There are 2 gateways you can use to connect to SQL*Server -

          - Database Gateway for SQL*Server - DG4MSQL

          - Database Gateway for ODBC - DG4ODBC

           

          There are differences in the licensing and functionality of these 2 gateways which are discussed in these notes -

           

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

          Gateway and Generic Connectivity Licensing Considerations (Doc ID 232482.1

           

          The following notes have further details on setting up the gateways -

          How to Setup DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit post install (Doc ID 466267.1)

          How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install (Doc ID 562509.1)

           

          How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)

          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)

           

          The following note has further details on gateways including links to the documentation -

          Master Note for Oracle Gateway Products (Doc ID 1083703.1)

           

           

          These are all available on My Oracle Support.

          Regards,

          Mike

          • 2. Re: connection with ms sql 2005
            user9275313

            Dear Mike,

             

            Thank you for your detail reply.

             

            I have configured Gateway to follow the scenario 1 in my question, I have installed Gate on the same oracle home, Every thing seems to be

            configured well and given default sql server database master during installation. I am facing error as following.

             

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

            [Generic Connectivity Using ODBC][H006] The init parameter

            <HS_FDS_CONNECT_INFO> is not set. Please set it in init<orasid>.ora file.

            ORA-02063: preceding 2 lines from DG4

             

            Waiting your kind reply.

             

            Thanks

            • 3. Re: connection with ms sql 2005
              user9275313

              Latest update of my changing are as follows.

               

              I just change the tnsname hostname from localhost with ip address, It start to give other error

               

              ora-28545 error diagnosed by net8 when connecting to an agent sql server

               

               

              Thanks

              • 4. Re: connection with ms sql 2005
                user9275313

                Hi Mike,

                 

                Thanks I have sort out my issue.

                 

                Now I am going towards my actual scenario and you suggest me following.

                 

                b) from the RDBMS on Solaris 64-bit you can access a gateway on the Windows machine. If this is the same Windows machine as in scenario 1 then you can use that gateway which is already installed.

                 

                I would be very thankful if you give the step by step detail in the above mentioned scenario.

                 

                Suppose I have sqlserver and gateway on windows 32 bit machine and 11g database on solaris 64 bit machine.

                How can I configure them?

                 

                Thanks

                • 5. Re: connection with ms sql 2005
                  Mkirtley-Oracle

                  Hi,

                     For your proposed setup you would do the following -

                   

                  1. Configure the gateway on Windows to access SQL*Server

                  2. On the Solaris 64-bit create a tnsnames.ora entry that references the gateway listener on Windows 32-bit, for example -

                   

                  dg4msql  =
                      (DESCRIPTION=
                          (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname of the Oracle Gateway Server>)(PORT=1511))
                          (CONNECT_DATA=(SID=dg4msql))
                          (HS=OK)
                       )


                  - change the PORT and HOST as necessary.


                  3. In SQLPLUS on Solaris create a database link that uses this tnsnames.ora entry -


                  create database link dg4msql_link connect to "user_name" identified by "password" using 'dg4msql' ;


                  4. Test from sqlplus -


                  select * from dual@dg4msql_link ;


                  This is discussed in the setup notes referred to earlier.


                  Regards,

                  Mike

                  • 6. Re: connection with ms sql 2005
                    user9275313

                    Hello Mike,

                     

                    Thanks for your reply. I follow the same step you have mentioned only added tnsnames entry in solaris tnsnames.ora file.

                    So no linstener changes anywhere needs. I did all step and facing common error as follows.

                     

                    "ora-28545 error diagnosed by net8 when connecting to an agent sql server".

                     

                    Kindly let me know how to sort out it.

                     

                    Thanks

                    • 7. Re: connection with ms sql 2005
                      Mkirtley-Oracle

                      Hi,

                        The ora-28545 is a configuration error so to follow up can you post the following -

                      - gateway listener.ora on Windows

                      - tnsnames.ora entry on Solaris

                      - the create database link statement from Solaris

                       

                      Regards,

                      Mike

                      • 8. Re: connection with ms sql 2005
                        user9275313

                        LISTENER_GW =
                          (DESCRIPTION_LIST =
                            (DESCRIPTION =
                              (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.253)(PORT = 1525))
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
                            )
                          )

                        ADR_BASE_LISTENER_GW = C:\product1\11.2.0\tg_1

                         

                         

                        Tnsnames.ora

                         

                        LISTENER =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))


                        ORACLR_CONNECTION_DATA =
                          (DESCRIPTION =
                            (ADDRESS_LIST =
                              (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                            )
                            (CONNECT_DATA =
                              (SID = CLRExtProc)
                              (PRESENTATION = RO)
                            )
                          )
                        dg4msql  =
                          (DESCRIPTION=
                            (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.253)(PORT=1525))
                            (CONNECT_DATA=(SID=dg4msql))
                            (HS=OK)
                          )
                        PRD =
                          (DESCRIPTION =
                            (ADDRESS =
                              (PROTOCOL = TCP)
                              (HOST = 10.0.0.2)
                              (PORT = 1522)
                            )
                            (CONNECT_DATA =
                              (SID = PRD)
                            )
                          )
                        TEST =
                          (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.169)(PORT = 1521))
                            (CONNECT_DATA =
                              (SERVER = DEDICATED)
                              (SERVICE_NAME = test)
                            )
                          )

                         

                        DBLINK COMMAND

                         

                         

                        CREATE DATABASE LINK sqlser

                                 CONNECT TO "sa" IDENTIFIED BY "sa1234" USING 'dg4msql';

                         

                         

                        Thanks

                        • 9. Re: connection with ms sql 2005
                          Mkirtley-Oracle

                          Hi,

                            In listenener.ora you have the description list but you don't have the 'sid_list' which details the instances available.

                          You should have an entry similar to -

                           

                          SID_LIST_LISTENER_GW =

                            (SID_LIST =

                              (SID_DESC =

                                (SID_NAME = dg4msql)

                                (ORACLE_HOME=C:\product\11.2.0\tg_3)

                                (PROGRAM=dg4msql)

                              )

                            )

                           

                          and stop and start the gateway listener.
                          If you then issue -

                           

                          lsnrctl status listener_gw

                          or

                          lsnrctl services listener_gw

                           

                          you should see the dg4msql sid as available.

                           

                          Regards,

                          Mike

                          • 10. Re: connection with ms sql 2005
                            user9275313

                            Hi Mike,

                             

                            It works fine but I fall now in other issue, when I access same database through client I am getting following error.

                             

                             

                            ORA-12154: TNS:could not resolve the connect identifier specified - Oracle Error

                             

                            Thanks

                            • 11. Re: connection with ms sql 2005
                              user9275313

                              Hi Mike,

                               

                              When I applied same scenario on solaris database and try to run query I am getting this error.

                               

                              ORA-12154: TNS:could not resolve the connect identifier specified

                               

                              Please help me.

                               

                              Thanks

                              • 12. Re: connection with ms sql 2005
                                Mkirtley-Oracle

                                Hi,

                                  The ORA-12154 means that the gateway entry cannot be found in the tnsnames.ora file or the tnsnames.ora file is not being accessed..
                                Can you do the following -

                                 

                                1. Check that there is a tnsnames.ora entry for the gateway.

                                2. Post the entry here.

                                3. What are the contents of sqlnet.ora ?

                                4. Does the user or client that gets the ORA-12154 have access to the tnsnames.ora file containing the gateway entry ?

                                5. On Solaris what is returned by -

                                echo $TNS_ADMIN


                                If you have access to My Oracle Support have a look at this note -


                                TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name (Doc ID 114085.1)


                                You should also make sure that the tnsnames.ora entry has at least one blank at the beginning of each line except the first -


                                dg4msql  =

                                <blank> (DESCRIPTION=

                                <blank>   (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.253)(PORT=1525))

                                <blank>   (CONNECT_DATA=(SID=dg4msql))

                                <blank>   (HS=OK)

                                <blank>  )

                                 

                                Can you post the latest gateway listener.ora ?

                                 

                                Regards,

                                Mike

                                • 13. Re: connection with ms sql 2005
                                  user9275313

                                  In above issue I was missing correct tns name file.

                                   

                                  I am trying to configure sql server 2008 on window 7 32 bit with same oracle gateway software , I am getting following error.

                                   

                                  ERROR at line 1:

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

                                  [Oracle][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access

                                  denied. {08001,NativeErr = 17}[Oracle][ODBC SQL Server

                                  Driver][DBMSLPCN]ConnectionOpen (Connect()). {01000,NativeErr =

                                  64}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}

                                  ORA-02063: preceding 2 lines from OLDSILO

                                   

                                  Gateway Listener post is following.

                                   

                                  LISTENER_GW =
                                    (DESCRIPTION_LIST =
                                      (DESCRIPTION =
                                        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.220)(PORT = 1525))
                                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
                                      )
                                    )
                                  SID_LIST_LISTENER_GW=
                                    (SID_LIST=
                                        (SID_DESC=
                                           (SID_NAME=dg4msql)
                                           (ORACLE_HOME=C:\product\11.2.0\tg_1)
                                           (PROGRAM=dg4msql)
                                        )
                                    )

                                  ADR_BASE_LISTENER_GW = C:\product\11.2.0\tg_1

                                   

                                  Kindly let me know where is the issue.

                                   

                                  Thanks

                                  • 14. Re: connection with ms sql 2005
                                    Mkirtley-Oracle

                                    Hi,

                                      The critical error is this -

                                     

                                    SQL Server does not exist or accessdenied. {08001,NativeErr = 17}

                                     

                                    which is coming from SQL*Server. The gateway is making the connection but thne hitting a problem on SQL*Server itself.

                                    When other customers have had this error the solution was to change the SQL*Server configuration to be port based instead of named instances.

                                    Could you check with your SQL*Server DBAs ?

                                     

                                    If you have access to My Oracle Support look at this note -

                                     

                                    DG4MSQL Gives Error - SQL Server Does Not Exist or Access Denied - With NativeErr 17 and 67 (Doc ID 1482030.1)


                                    Regards,

                                    Mike

                                    1 2 Previous Next