4 Replies Latest reply: Jun 13, 2013 10:24 AM by clcarter RSS

    Unable to connect to XE service when running Cisco AnyConnect VPN

    1011890
      We have the Oracle 11g XE database installed on a Windows 7 laptop. The Oracle XE database runs fine when the laptop not connected to a network, when connected to our corporate lan, or when connected to a cable-modem. However, when running under the Cisco AnyConnect VPN client, we are unable to connect to the Oracle XE service.

      I have tried to resolve this, but am stuck. I do not if this issues is caused by:
      1. There is no IPv6 address listed for the AnyConnect VPN (from ipconfig /all)?
      2. Something regarding split-tunnelling that must be configured on the VPN?
      3. Can the Oracle XE database run under an SSL VPN?

      Below is the output of when I try to connect the Oracle XE service via sqlplus. Any suggestion or ideas are greatly appreciated.

      **************************
      The OracleXETNSListener service is starting.
      The OracleXETNSListener service was started successfully.

      The OracleServiceXE service is starting..............
      The OracleServiceXE service was started successfully.


      C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server\bin>sqlplus system/password@XE

      SQL*Plus: Release 11.2.0.2.0 Production on Fri May 31 07:03:24 2013

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

      ERROR:
      ORA-12546: TNS:permission denied


      Enter user-name:


      **************************
      listener\alert\log,xml

      <msg time='2013-05-31T07:03:24.950-04:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='DEV-M-137GF'
      host_addr='fe80::2884:a03d:5286:7996%21'>
      <txt>31-MAY-2013 07:03:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe)(HOST=DEV-M-137GF)(USER=ptavolet))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.99.8.39)(PORT=56324)) * establish * XE * 12514
      </txt>
      </msg>
      <msg time='2013-05-31T07:03:24.958-04:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='DEV-M-137GF'
      host_addr='fe80::2884:a03d:5286:7996%21'>
      <txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
      </txt>
      </msg>
      <msg time='2013-05-31T07:07:43.935-04:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='DEV-M-137GF'
      host_addr='fe80::2884:a03d:5286:7996%21'>
      <txt>31-MAY-2013 07:07:43 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=ptavolet))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * status * 0
      </txt>
      </msg>
      <msg time='2013-05-31T07:08:13.289-04:00' org_id='oracle' comp_id='tnslsnr'
      type='UNKNOWN' level='16' host_id='DEV-M-137GF'
      host_addr='fe80::2884:a03d:5286:7996%21'>
      <txt>31-MAY-2013 07:08:13 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=ptavolet))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=186647040)) * services * 0
      </txt>
      </msg>


      **************************
      listener\trace\listener.log file

      Started with pid=3688
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEV-M-137GF.paychex.com)(PORT=1521)))
      Listener completed notification to CRS on start

      TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
      Fri May 31 07:03:24 2013
      31-MAY-2013 07:03:24 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server\bin\sqlplus.exe)(HOST=DEV-M-137GF)(USER=ptavolet))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.99.8.39)(PORT=56324)) * establish * XE * 12514
      TNS-12514: TNS:listener does not currently know of service requested in connect descriptor


      **************************
      C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server\bin>lsnrctl status

      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 31-MAY-2013 07:07:43

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      STATUS of the LISTENER
      ------------------------
      Alias LISTENER
      Version TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 - Production
      Start Date 31-MAY-2013 07:01:12
      Uptime 0 days 0 hr. 6 min. 31 sec
      Trace Level support
      Security ON: Local OS Authentication
      SNMP OFF
      Default Service XE
      Listener Parameter File C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
      Listener Log File C:\ProgramData\oraclexe\app\oracle\diag\tnslsnr\DEV-M-137GF\listener\alert\log.xml
      Listener Trace File C:\ProgramData\oraclexe\app\oracle\diag\tnslsnr\DEV-M-137GF\listener\trace\ora_3688_
      5400.trc
      Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DEV-M-137GF.paychex.com)(PORT=1521)))
      Services Summary...
      Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      The command completed successfully


      **************************
      C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server\bin>lsnrctl service

      LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 31-MAY-2013 07:08:13

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

      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
      Services Summary...
      Service "CLRExtProc" has 1 instance(s).
      Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:0 refused:0
      LOCAL SERVER
      Service "PLSExtProc" has 1 instance(s).
      Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
      Handler(s):
      "DEDICATED" established:0 refused:0
      LOCAL SERVER
      The command completed successfully



      Again, I appreciate any help. Thanks.......Paul

      Edited by: 1008887 on Jun 4, 2013 10:57 AM
        • 1. Re: Unable to connect to XE service when running Cisco AnyConnect VPN
          Udo
          Hello,

          I think the issue arises because the VPN client gets "focussed": The interface gets the default gateway and primary host address, and probably the hostname/domainname is not longer valid on that interface.
          "Permission denied" may also result if your logged in user moves into a different domain not allowed to connect. You could check your sqlnet.ora to see what's configured...

          Concerning your questions:
          1. There is no IPv6 address listed for the AnyConnect VPN (from ipconfig /all)?
          Should be no problem.
          2. Something regarding split-tunnelling that must be configured on the VPN?
          Probably, see consideration about your default route/gateway above.
          3. Can the Oracle XE database run under an SSL VPN?
          If configured properly, this will usually work without issues. Just make sure your SSL endpoint has a static hostname or IP address and that this endpoint is part of your database listener configuration.

          -Udo
          • 2. Re: Unable to connect to XE service when running Cisco AnyConnect VPN
            1011890
            Hello Udo,,

            Thanks for your reply. Your explanation makes sense, but I am not sure how to proceed. We have never used the sqlnet.ora file in the past and it has always worked. This may sound naive, but if I add it back, which values would I try to set? I tried setting the NAMES.DIRECTORY_PATH value, but no success. Any suggestions on what values I should define?

            Thanks again.....Paul
            • 3. Re: Unable to connect to XE service when running Cisco AnyConnect VPN
              1011890

              After lots of trial and error, I was able to eliminate this problem.  What I wound up doing is defining the XE service again in the listener.ora file:

              SID_LIST_LISTENER =

                (SID_LIST =

                  (SID_DESC =

                    (SID_NAME = XE)

                    (ORACLE_HOME = C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server)

                  )

               

              I know that typically you should not have to do this, especially since I already had defined DEFAULT_SERIVCE_LISTENER = (XE) at the bottom of the listener.ora file.  Explicitly defining the XE service in the listener.ora file allows the listener to find it while the system is running under the Cisco AnyConnect VPN.  The only hiccup I found by doing this is that the XE service is discovered twice by the listener when the system is NOT running under the Cisco AnyConnect VPN.  It still works OK.  The listener just seems to ignore the repeated definition of the XE service (see output below):

              *******************************************

              C:\ProgramData\oraclexe\app\oracle\product\11.2.0\server\bin>lsnrctl service

               

              LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 13-JUN-2013 10:03:15

              .......(omitted output).......

              Service "XE" has 2 instance(s).

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

                  Handler(s):

                    "DEDICATED" established:0 refused:0

                       LOCAL SERVER

                Instance "xe", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "DEDICATED" established:0 refused:0 state:ready

                       LOCAL SERVER

              Service "XEXDB" has 1 instance(s).

                Instance "xe", status READY, has 1 handler(s) for this service...

                  Handler(s):

                    "D000" established:0 refused:0 current:0 max:1022 state:ready

                       DISPATCHER <machine: DEV-M-137GF, pid: 5544>

              (ADDRESS=(PROTOCOL=tcp)(HOST=DEV-M-137GF.paychex.com)(PORT=58257))

              The command completed successfully

              *******************************************

               

              If anyone has a cleaner solution for this problem, please let me know.  Otherwise, I am moving forward with what I did.

               

              Thanks.....Paul

              • 4. Re: Unable to connect to XE service when running Cisco AnyConnect VPN
                clcarter

                >> typically you should not have to do this

                 

                Correct, especially when the listener is running on the default port the database instance should automatically register with a listener running on port 1521.

                 

                >> [listener.ora change] (SID_DESC = ... (SID_NAME = XE) ...

                 

                That is one way, pointing the listener at the instance. But that also adds a static entry in the lsnrctl services listing.

                 

                Another way is use the instance local_listener parameter, telling the database the listener details. Try stopping the listener, remove the SID_LIST stanza from listener.ora, start the listener, and use a system connection in sqlplus:

                 

                sqlplus /nolog

                conn system ... password ... connected

                alter system set local_listener = '(ADDRESS=(PROTOCOL=TCP)(HOST=<hostname or IPv4 address>)(PORT=1521))' scope=both;

                alter system register;

                exit

                lsnrctl stat

                ...

                lsnrctl serv

                ...

                 

                Just be sure that the hostname resolves to the right NIC interface. DHCP can make that a challenge, unless you have a dynamic DNS, with the host getting registered correctly with the assigned address.