This discussion is archived
4 Replies Latest reply: Jun 13, 2013 8:24 AM by clcarter RSS

Unable to connect to XE service when running Cisco AnyConnect VPN

1011890 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    >> 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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points