Actions to Verify When Running Into ORA-01017 While Creating Datasource | JNDI

Actions to Verify When Running Into ORA-01017 While Creating Datasource (Doc ID 1556742.1)

NOTE:974949.1 - Oracle E-Business Suite Software Development Kit for Java (includes AppsDataSource, Java Authentication and Authorization Service, session management) Readme - Patch 13882058

APPLIES TO:

Oracle Application Object Library - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

PURPOSE

The Note:974949.1 "Oracle E-Business Suite Software Development Kit for Java (includes AppsDataSource, Java Authentication and Authorization Service, session management)" explains the steps to create a Datasource for external applications to connect to the E-Business Suite database.

An error often reported here is the ORA-01017: invalid username/password; logon denied which normally indicates that a database user passed incorrect credentials. However when it comes to creating Datasource the ORA-01017 is raised as soon as there is an issue with the connection and the actual ORA message is not reporting the actual error. 

This document will provide a number of things to verify which may assist in troubleshooting the ORA-01017 when raised in context of Datasource.

TROUBLESHOOTING STEPS

IMPORTANT: The Datasource uses a FND user for the connection. This may either be the seeded ASADMIN user or a own created user. For simplicity the examples in this document refer to ASADMIN, however this should be replaced by your own user when appropriate.

1. Verify the FND user defined in the Datasource

The user in the Datasource should be a valid FND user created in E-Business Suite. For security reasons it's recommended this user does not have any responsibilities assigned, but only has the required role: Apps Schema Connect [internal name UMX|APPS_SCHEMA_CONNECT].

When using the credentials in the AppsLocalLogin.jsp this should allow to login successfully. Another quick method to check the credentials are correct is to run the following SQL

SQL> select fnd_web_sec.validate_login('ASADMIN', '<replace with password>' ) from dual;

Result:

Y = Credentials are correct

N = Credentials are not correct. Verify the credentials passed and also take into account case-sensitivity. Resolve this problem before continuing with the steps

Also confirm the role UMX|APPS_SCHEMA_CONNECT is assigned and not expired

SQL> select user_name, role_name, start_date, expiration_date from wf_user_roles where user_name='ASADMIN';

Result:

Expected is to have 2 records. The first with the ROLE_NAME = USER_NAME and the second with ROLE_NAME = UMX|APPS_SCHEMA_CONNECT. Ensure that START_DATE is in the past and EXPIRATION_DATE is null or (far) in the future.

If the UMX|APPS_SCHEMA_CONNECT role is not assigned have this role added via User Management > Users function

2. Verify relevant profile options

There are some profile options in the E-Business Suite used to ensure connections come from trusted node

• FND: Desktop Nodes allowed [FND_SERVER_DESKTOP_USER]

Set a USER level and has a comma separated list of trusted nodes allowed to connect. The node names here should be in uppercase and exactly match the value for NODE_NAME column in FND_NODES table. If FND_NODES has NODE_NAME = MYSERVER the value to be used is MYSERVER (and not MYSERVER.MYDOMAIN or myserver). Query the FND_NODES table to confirm the exact name to be used here.

Run the following SQL to check relevant information from the FND_NODES table

SQL> select node_name, description, server_address, server_id from FND_NODES;

• FND: Validate IP address [FND_SERVER_IP_SEC]

Set at SITE level to one of the following

• N - None
• D - Desktop Only 
• DS - Desktop and Server 
• S - Server Only

• FND: Validate User Type [FND_SERVER_SEC]

Set at SITE level to one of the following 

• N - None
• D - Desktop Only 
• DS - Desktop and Server 
• S - Server Only

The default value for the last 2 profile options is 'Desktop Only' which verifies the connections when coming from a Desktop node. To verify if the ORA-01017 is caused by this validation it may be useful to temporarily change the value to 'None' so the validation is disabled for one or both profile options. This should however only be considered a diagnostics action and is not a definite solution since it impacts security.

The following SQL can be used to review the current values for these profile options

SQL> select p.profile_option_name SHORT_NAME,
n.user_profile_option_name NAME,
decode(v.level_id,
      10001, 'Site',
      10002, 'Application',
      10003, 'Responsibility',
      10004, 'User',
      10005, 'Server',
      10006, 'Organization',
      10007, 'Server Resp',
      '<UnDefined>') LEVEL_SET,
      decode(v.level_id,
      10001, ' ',
      10002, app.application_short_name,
      10003, rsp.responsibility_key,
      10004, usr.user_name,
      10005, svr.node_name,
      10006, org.name,
      10007, svr2.node_name ||' / '|| rsp.responsibility_key,
      '<UnDefined>') CONTEXT,
      nvl(v.profile_option_value,' ') VALUE
from fnd_profile_options p,
     fnd_profile_option_values v,
     fnd_profile_options_tl n,
     fnd_user usr,
     fnd_application app,
     fnd_responsibility rsp,
     fnd_nodes svr,
     fnd_nodes svr2,
     hr_operating_units org
where  p.profile_option_id = v.profile_option_id (+)
and    p.profile_option_name = n.profile_option_name
and    p.profile_option_name in ('FND_SERVER_SEC','FND_SERVER_IP_SEC','FND_SERVER_DESKTOP_USER')
and    usr.user_id (+) = v.level_value
and    rsp.application_id (+) = v.level_value_application_id
and    rsp.responsibility_id (+) = v.level_value
and    app.application_id (+) = v.level_value
and    svr.node_id (+) = v.level_value
and    svr2.node_id (+) = v.level_value2
and    org.organization_id (+) = v.level_value
and    language='US'
order by short_name, level_id;

3. Small test class to verify the connection

The below java code provides a simple Datasource to test connection using the credentials and DBC file created for the desktop node. This allows to verify that DBC file and credentials are correct without the need to setup the Datasource in WLS console. The code should be run from the desktop node.

The steps to create and run the class are:

• Create a file named Connect2.java and copy following contents

import oracle.apps.fnd.security.*;
import java.sql.Connection;
import javax.naming.Referenceable;
import javax.sql.*;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.pool.OracleDataSource;
import oracle.jdbc.pool.OracleDataSourceFactory;

public class Connect2
{

//apps user with umx| APPS_SCHEMA_CONNECT priviliges (default asadmin)
public static String user = "<username, e.g. ASADMIN>";
public static String pwd = "<password, e.g. ASADMIN>";
public static String dbcFile = "<location of the DBC file to be used by the Datasource>";

public static void testConnection (String dbcFile)
throws Exception
{
System.out.println("Running testConnection DBC version...");
AppsDataSource v2 = new AppsDataSource();
v2.setUser(user);
v2.setPassword(pwd);
v2.setDbcFile(dbcFile);

Object ods = new OracleDataSourceFactory().getObjectInstance(v2.getReference(), null, null, null);
DataSource ds = (DataSource) ods;
testSql(ds.getConnection());
}
public static void testSql (Connection conn)
throws Exception
{
String sql = "BEGIN fnd_profile.get(:1,:2); END;";
OracleCallableStatement cStmt = (OracleCallableStatement) conn.prepareCall(sql);
cStmt.setString(1, "APPS_WEB_AGENT");
cStmt.setString(2, null);
cStmt.registerOutParameter(2, java.sql.Types.VARCHAR, 0, 100);
cStmt.execute();
String profVal = cStmt.getString(2);
System.out.println(profVal);
}
public static void main (String j[])
throws Exception
{
System.out.println(new AppsDataSource() instanceof Referenceable);
testConnection(dbcFile);
} }

• Review the following 3 lines in the source-code and replace provide the variables with the correct values for your environment.

public static String user = "<username, e.g. ASADMIN>";
public static String pwd = "<password, e.g. ASADMIN>";
public static String dbcFile = "<location of the DBC file to be used by the Datasource>";

• On the desktop node the fndext.jar is available. Ensure the fndext.jar is incuded in the CLASSPATH environment setting

# export CLASSPATH=$CLASSPATH:<full path>/fndext.jar

• Compile the class:

# javac Connect2.java

• Ensure the location of Connect2.class is and the fndext.jar are available in the CLASSPATH environment variable

# export CLASSPATH=$CLASSPATH:<directory of Connect2.class>

• Run the class:  

# java Connect2

When connection is made this will show the following output. 

true
Running testConnection DBC version...
http://<host.domain:port>/pls/<SID>

Note the URL is not relevant. The value of APPS_WEB_AGENT is just used as example SQL to test the connection.

When there is a problem the following will be rendered

true
Running testConnection DBC version...
Exception in thread "main" java.lang.RuntimeException: ORA-20001:
ORA-06512: at "APPS.APP_EXCEPTION", line 72
ORA-06512: at "APPS.FND_SECURITY_PKG", line 533
ORA-06512: at line 1

        at oracle.apps.fnd.security.AppsDataSource.setDbcFile(AppsDataSource.java:156)
        at Connect2.testConnection(Connect2.java:24)
        at Connect2.main(Connect2.java:46)

In this case closely review the credentials passed for the user, the profile options mentioned and DBC file used to see which is setup wrong.