Home » RDBMS Server » Security » secure external password does not prompt for password (oracle 10gr2, windows)
secure external password does not prompt for password [message #503331] Thu, 14 April 2011 11:53 Go to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
Hi,

I'm trying to hide the password for the batch programs that connect to the DB Server

as Cadot pointed out in http://www.orafaq.com/forum/?t=msg&goto=496262&137592/&srch=secure+external+password#msg_496262

Quote:

use secure external password store



with reference to http://download.oracle.com/docs/cd/B19306_01/network.102/b14266/cnctslsh.htm

when I create wallet, the system does not prompt me for password

C:\>mkstore -wrl "C:\ora102\NETWORK\ADMIN" -create




when creating login credentials, again the system never prompts me for password

C:\>mkstore -wrl "C:\ora102\NETWORK\ADMIN" -createCredential db10g scott tiger



here's my sqlnet.ora configurations
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY =C:\ora102\NETWORK\ADMIN)
     )
    )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSESSL_VERSION = 0


here's my tnsname.ora settings

DB10G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mike)
    )
  )



here's the outcome
C:\Documents and Settings\Administrator>sqlplus /@db10g

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 13 22:53:06 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-12534: TNS:operation not supported


Enter user-name:



so I google around for the solution to the ORA-12534 error, one of the site, http://ora-12514.ora-code.com/ suggest lsnrctl services

here's my lsnrctl services


Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "MIKEXDB" has 1 instance(s).
  Instance "mike", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: LENG, pid: 3548>
         (ADDRESS=(PROTOCOL=tcp)(HOST=leng)(PORT=1172))
Service "MIKE_XPT" has 1 instance(s).
  Instance "mike", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:4 refused:0 state:ready
         LOCAL SERVER
Service "mike" has 1 instance(s).
  Instance "mike", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:4 refused:0 state:ready
         LOCAL SERVER
The command completed successfully



right now I think I will be a fool to think that the solution is to resolve the ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor.

so what is wrong with my setup, or is it some patch that I need to apply? Can someone enlighten me on how to resolve this buggy issue?

thanks a lot!
Re: secure external password does not prompt for password [message #503332 is a reply to message #503331] Thu, 14 April 2011 11:55 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
what clues exist with listener.log file
Re: secure external password does not prompt for password [message #503506 is a reply to message #503331] Sat, 16 April 2011 12:19 Go to previous messageGo to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
here's the log


17-APR-2011 01:16:11 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=services)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169870336)) * services * 0
17-APR-2011 01:16:17 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=services)(ARGUMENTS=64)(SERVICE=kyte_listener)(VERSION=169870336)) * services * 0
17-APR-2011 01:16:21 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=kyte_listener)(VERSION=169870336)) * status * 0
17-APR-2011 01:16:26 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169870336)) * status * 0
17-APR-2011 01:17:12 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=mike)(CID=(PROGRAM=c:\ora102\bin\sqlplus.exe)(HOST=LENG)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1199)) * establish * mike * 0




here's the tracing

[17-APR-2011 01:17:12:436] nstoSetupTimeout: ATO enabled for ctx=0x013FD370, val=60000(millisecs)
[17-APR-2011 01:17:12:436] nstoUpdateActive: Active timeout is 0 (see nstotyp)
[17-APR-2011 01:17:12:436] nsopen: opening transport...
[17-APR-2011 01:17:12:436] nttcnp: getting sockname
[17-APR-2011 01:17:12:436] nttcnp: getting peername
[17-APR-2011 01:17:12:436] nttcnr: waiting to accept a connection.
[17-APR-2011 01:17:12:436] nttcnr: getting sockname
[17-APR-2011 01:17:12:436] snlinGetNameInfo: Using numeric form of host's address 127.0.0.1
[17-APR-2011 01:17:12:436] nttcnr: connected on ipaddr 127.0.0.1
[17-APR-2011 01:17:12:436] snlinGetNameInfo: Using numeric form of host's address 127.0.0.1
[17-APR-2011 01:17:12:436] nttvlser: valid node check on incoming node 127.0.0.1
[17-APR-2011 01:17:12:436] nttvlser: Accepted Entry: 127.0.0.1
[17-APR-2011 01:17:12:436] nttcon: set TCP_NODELAY on 284
[17-APR-2011 01:17:12:436] nsopen: transport is open
[17-APR-2011 01:17:12:436] nsnainit: inf->nsinfflg[0]: 0xd inf->nsinfflg[1]: 0xd
[17-APR-2011 01:17:12:436] nsopen: global context check-in (to slot 5) complete
[17-APR-2011 01:17:12:436] nsanswer: deferring connect attempt; at stage 5
[17-APR-2011 01:17:12:436] nscon: doing connect handshake...
[17-APR-2011 01:17:12:436] nscon: got NSPTCN packet
[17-APR-2011 01:17:12:436] nsevdansw: exit
[17-APR-2011 01:17:12:436] nstoClearTimeout: ATO disabled for ctx=0x013FD370
[17-APR-2011 01:17:12:436] nstoUpdateActive: Active timeout is -1 (see nstotyp)
[17-APR-2011 01:17:12:436] nstoControlATO: ATO disabled for ctx=0x013FD370
[17-APR-2011 01:17:12:436] snlinGetNameInfo: Using numeric form of host's address 127.0.0.1
[17-APR-2011 01:17:12:436] nsglbgetRSPidx: returning ecode=0
[17-APR-2011 01:17:12:436] nsc2addr: (ADDRESS=(PROTOCOL=BEQ)(PROGRAM=c:\ora102\bin\oracle.exe)(ARGV0=oraclemike)(ARGS='(LOCAL=NO)'))
[17-APR-2011 01:17:12:436] nsbeqaddr: connecting...
[17-APR-2011 01:17:12:436] nsopen: opening transport...
[17-APR-2011 01:17:12:436] snlpcss: Spawn Oracle completed oracle     (LOCAL=NO) mike.
[17-APR-2011 01:17:12:436] sntpcall: Attempting to open pipe \\.\PIPE\ORANTPCC8.D08
[17-APR-2011 01:17:12:436] sntpcall: Successfully established pipe 304 to child with 0 retries.
[17-APR-2011 01:17:12:436] sntpcall: Attempting to open pipe \\.\PIPE\ORANTPCC8.D08.w
[17-APR-2011 01:17:12:436] sntpcall: Successfully established pipe 332 to child with 0 retries.
[17-APR-2011 01:17:12:436] nsopen: transport is open
[17-APR-2011 01:17:12:436] nsopen: global context check-in (to slot 6) complete
[17-APR-2011 01:17:12:436] snlinGetNameInfo: Using numeric form of host's address 127.0.0.1
[17-APR-2011 01:17:12:436] nsbequeath_stg2: doing connect handshake...
[17-APR-2011 01:17:12:436] nsbequeath: doing connect handshake...
[17-APR-2011 01:17:12:436] sntpwrite: Attempting to write 4 bytes to handle 304
[17-APR-2011 01:17:12:436] sntpwrite: WriteFile returned 4 bytes
[17-APR-2011 01:17:12:436] sntpwrite: Attempting to write 60 bytes to handle 304
[17-APR-2011 01:17:12:436] sntpwrite: WriteFile returned 60 bytes
[17-APR-2011 01:17:12:436] sntpwrite: Attempting to write 8 bytes to handle 304
[17-APR-2011 01:17:12:452] sntpwrite: WriteFile returned 8 bytes
[17-APR-2011 01:17:12:452] sntpread: Attempting to read 4 bytes from handle 332
[17-APR-2011 01:17:12:452] sntpread: ReadFile returned 4 bytes
[17-APR-2011 01:17:12:452] sntpread: rc = 0, ntresnt[0] = 0
[17-APR-2011 01:17:12:452] sntpread: Attempting to read 4 bytes from handle 332
[17-APR-2011 01:17:12:452] sntpread: ReadFile returned 4 bytes
[17-APR-2011 01:17:12:452] sntpread: rc = 0, ntresnt[0] = 0
[17-APR-2011 01:17:12:452] nsbequeath: NSE=12586
[17-APR-2011 01:17:12:452] nsbequeath: error reading REDIR/NSE msg
[17-APR-2011 01:17:12:452] nserror: nsres: id=5, op=72, ns=12586, ns2=0; nt[0]=0, nt[1]=0, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[17-APR-2011 01:17:12:452] nscon: sending NSPTRS packet
[17-APR-2011 01:17:12:452] nstimarmed: no timer allocated
[17-APR-2011 01:17:12:452] nstoClearTimeout: ATO disabled for ctx=0x015A3500
[17-APR-2011 01:17:12:452] nstoClearTimeout: STO disabled for ctx=0x015A3500
[17-APR-2011 01:17:12:452] nstoClearTimeout: RTO disabled for ctx=0x015A3500
[17-APR-2011 01:17:12:452] nstoClearTimeout: PITO disabled for ctx=0x015A3500
[17-APR-2011 01:17:12:452] nstoUpdateActive: Active timeout is -1 (see nstotyp)
[17-APR-2011 01:17:12:452] nsclose: closing transport
[17-APR-2011 01:17:12:452] sntpclose: Closing pipe 332
[17-APR-2011 01:17:12:452] sntpclose: Closing pipe 304
[17-APR-2011 01:17:12:452] nsclose: global context check-out (from slot 6) complete
[17-APR-2011 01:17:12:452] nstimarmed: no timer allocated
[17-APR-2011 01:17:12:452] nsclose: closing transport
[17-APR-2011 01:17:12:452] nsclose: global context check-out (from slot 5) complete
[17-APR-2011 01:17:12:452] nsbeqaddr: connect handshake is complete




thanks a lot!
Re: secure external password does not prompt for password [message #503507 is a reply to message #503506] Sat, 16 April 2011 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The rightmost number of each record in listener.log file is the result status code.
A value of 0 means success.
The file should contain a 1 or more records where the status value is 12514.
If not then connection request is be processed by some other listener.
I need to see the complete record containing the error 12514
I need to the the actual connection string that results on the ORA-12514 error
I need to see the reults of following command from DB Server system
lsnrctl service
Re: secure external password does not prompt for password [message #503508 is a reply to message #503507] Sat, 16 April 2011 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I google around for the solution to the ORA-12534 error, one of the site, http://ora-12514.ora-code.com/ suggest lsnrctl services

You searched for the wrong code, I only see a 12534 error in your post.
http://ora-12534.ora-code.com/

- Are you able to connect to the database with the same credentials WITHOUT using secure password store?
tnsping db10g


- What is your Oracle Edition?
select * from v$version;


Regards
Michel

[Updated on: Sat, 16 April 2011 12:43]

Report message to a moderator

Re: secure external password does not prompt for password [message #503528 is a reply to message #503508] Sun, 17 April 2011 10:00 Go to previous messageGo to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
Hi Blackswan,

previously you reply

If not then connection request is be processed by some other listener.

yes I have configured a non default listener, according to a list of security arrangements being put across to me. they requested me to configured a non default listener without giving me any specifics.

however according to http://download.oracle.com/docs/cd/B19306_01/network.102/b14212/listenercfg.htm#sthref962

Quote:

If you want PMON to register with a local listener that does not use TCP/IP, port 1521, configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.



am I right to say that it is pointless to configure a non default listener if it is still using the same port? so should I configure back to the default listener

your previous reply

Quote:

I need to see the complete record containing the error 12514



unfortunately I did not see any of the error either in C:\ora102\NETWORK\listener_log\kyte_listener.log or C:\ora102\NETWORK\listener_trace\kyte_listener.trc




here's the connection string in tnsname.ora


DB10G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mike)
    )
  )




lsnrctl service

C:\Documents and Settings\Administrator>lsnrctl service

LSNRCTL for 32-bit Windows: Version 10.2.0.4.0 - Production on 17-APR-2011 22:50:25

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "MIKEXDB" has 1 instance(s).
  Instance "mike", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1002 state:ready
         DISPATCHER <machine: LENG, pid: 1228>
         (ADDRESS=(PROTOCOL=tcp)(HOST=leng)(PORT=1113))
Service "MIKE_XPT" has 1 instance(s).
  Instance "mike", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER
Service "mike" has 1 instance(s).
  Instance "mike", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:6 refused:0 state:ready
         LOCAL SERVER
The command completed successfully


thanks a lot!

please pardon me if I'm made a mistake as I was still learning.

Hi Michel,

here's the result of tnsping db10g


C:\Documents and Settings\Administrator>tnsping DB10G

TNS Ping Utility for 32-bit Windows: Version 10.2.0.4.0 - Production on 17-APR-2011 22:35:56

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

Used parameter files:
c:\ora102\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = mike)))
OK (30 msec)



Are you able to connect to the database with the same credentials WITHOUT using secure password store?

if I'm using listner method, i.e. sqlplus tomkyte

I'm able to connect to db server
C:\Documents and Settings\Administrator>sqlplus sgtel10

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 17 22:43:33 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the OLAP, Data Mining and Real Application Testing options

tomkyte0@MIKE>


if I'm using connect_identifier, sqlplus tomktyte@gt10
I'm able to connect to db server

C:\Documents and Settings\Administrator>sqlplus tomkyte@gt10

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Apr 17 22:44:21 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the OLAP, Data Mining and Real Application Testing options

tomkyte@gt10>



here's my oracle version.
sys@mike> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

sys@mike



thanks a lot!
Re: secure external password does not prompt for password [message #503531 is a reply to message #503528] Sun, 17 April 2011 10:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
C:\Documents and Settings\Administrator>sqlplus tomkyte@gt10

C:\>mkstore -wrl "C:\ora102\NETWORK\ADMIN" -createCredential db10g scott tiger

Are you able to connect without Secure Password Store AND same credentials?
Note that tomkyte<>scott and gt10<>db10g

Regards
Michel
Re: secure external password does not prompt for password [message #503628 is a reply to message #503531] Mon, 18 April 2011 08:21 Go to previous messageGo to next message
kytemanaic
Messages: 55
Registered: February 2009
Member
without secure password store


C:\Documents and Settings\Administrator>sqlplus tomkyte@db10g

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 18 21:14:54 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the OLAP, Data Mining and Real Application Testing options



with secure password store

C:\Documents and Settings\Administrator>sqlplus /@db10g

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 18 21:15:59 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the OLAP, Data Mining and Real Application Testing options

tomkyte@db10g>



yes, Michel you have indeed an eye for detail, and thanks for pointing out my error.

However I encounter another issue right now

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 18 21:18:18 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

ERROR:
ORA-01031: insufficient privileges


Enter user-name:



I'm not not able to login using os authentication.

here's my settings for sql.ora

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TRACE_LEVEL_SERVER = USER

LOG_FILE_CLIENT = log_client.log

LOG_DIRECTORY_CLIENT = C:\ora102\NETWORK\log_client

LOG_DIRECTORY_SERVER = C:\ora102\NETWORK\log_server

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = C:\ora102\NETWORK\ADMIN)
     )
    )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0



even if I remove the following section, I still encounter the same error.


WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = C:\ora102\NETWORK\ADMIN)
     )
    )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_VERSION = 0


before I create the wallet credentials I don't have this issue, is there any way that I can use os authentication again?

thanks a lot!
Re: secure external password does not prompt for password [message #503629 is a reply to message #503628] Mon, 18 April 2011 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add
SQLNET.AUTHENTICATION_SERVICES = (NTS)
to your sqlnet.ora file.

Regards
Michel

[Updated on: Mon, 18 April 2011 08:25]

Report message to a moderator

Re: secure external password does not prompt for password [message #503933 is a reply to message #503629] Wed, 20 April 2011 09:10 Go to previous message
kytemanaic
Messages: 55
Registered: February 2009
Member
Hi Michel,

You are indeed god like and have an eye for detail! once again merci infiniment!
Previous Topic: show labels from Oracle Label Security
Next Topic: How to use secure external password store in dot net
Goto Forum:
  


Current Time: Thu Mar 28 09:25:05 CDT 2024