ColdFusion 11 Datasource Using Oracle Wallets/PKI

[Tyler_Stevens_637]

ColdFusion v11,0,10,300066

Oracle v12.01.00.02

Has anyone worked with setting up Oracle datasources utilizing Oracle Wallets?  cwallet.sso? Auto login.  I have successfully implemented it in Java applications where I am able to create and provide a set of properties to the JDBC connection method, in addition to some other configuration. The CFIDE Administrator Data Sources GUI does not appear to give me the option to implement Wallets.  Is there a way to configure them directly? 

Any help would be greatly appreciated!

Thanks!

[davidleelambert]

If the built-in ColdFusion drivers don't have the option you're looking for but there is a JDBC driver that allows the connection, you can use it directly.  First, in "Server Settings" → "Java and JVM", add the ".jar" file(s) for the driver to the "ColdFusion Class path" field. If the driver requires any system properties, add those to "JVM Arguments" as well.  Restart the server.  Go back to "Data & Services" → "Data Sources".  Create an "other" data source; the fields on that screen should be self-explanatory.

[Tyler_Stevens_637]

Thanks for responding!  The issue I with that is the java properties vary by datasource and I have multiple datasources.  I have added the necessary Oracle jars to the CF Classpath.  I also added the "oracle.net.authentication_services" and "javax.net.ssl.keyStoreType" properties to the JVM Arguments.  I updated the java.security file to add the Oracle PKI Provider in the correct location manually.  I need the ability to point each datasource to a different Oracle Wallet.  Any ideas?  Thanks!

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Security.insertProviderAt(new oracle.security.pki.OraclePKIProvider(), 3);
String url = "jdbc:oracle:thin:@(DESCRIPTION = " +
"(ADDRESS_LIST = " +
"(ADDRESS = " +
"(PROTOCOL = TCPS)" +
"(HOST = XX.XXXX.XXX.XXX)" +
"(PORT = XXXX)" +
")" +
") " +
"(CONNECT_DATA = (SERVICE_NAME = DBSID))" +
")";

java.util.Properties info = new java.util.Properties();
info.setProperty("oracle.net.authentication_services", "(TCPS)");
info.setProperty("javax.net.ssl.trustStore", "C:/example/cwallet1.sso");
info.setProperty("javax.net.ssl.trustStoreType", "SSO");
info.setProperty("javax.net.ssl.keyStore", "C:/example/cwallet1.sso");
info.setProperty("javax.net.ssl.keyStoreType", "SSO");

Connection conn = DriverManager.getConnection(url, info);
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select user from dual");

[Tyler_Stevens_637]

I was able to configure datasources in the underlying Tomcat server and create JNDI Datasources in CF Administrator.  Everything connects fine.  The code in CFQUERY runs find but CFPROCPARAM calls result in "Missing IN or OUT parameter at index: n" ORA-17041 errors.  The n is always one more than the number of parameters that are required for the stored procedure. ColdFusion code should work the same regardless of the Datasource used?!?!  Especially when they are both using JDBC against the same Oracle database. If anyone has any insight on this it would be greatly appreciated. -Thanks

[peterk8476818]

Hi Tyler,

Did you ever sort this issue?  If I use the Oracle Drivers built in with the Coldfusion Trial, CFPROCPARAM works.  But since I'll be setting it to standard and have to use the JDBC drivers, I'm getting the "missing IN or OUT" error.

Thanks.

[Charlie Arehart]

Can you both report what cf version and update level you're using, as well as the jdbc driver version and oracle version?

Also, assuming you may even be using the latest of all 3, it would seem the next thing is to see just what is being sent to oracle, from the cfprocparam call. 

There are various ways to do that, from the feature in the cf dsn to add logging (which is VERY verbose), to a tool like FusionReactor (which shows the underlying sql sent from cf) , to any sort of display of execution plans or profiling in oracle to see the details of what it may show was sent from cf. 

[peterk8476818]

Hi Charlie,

I'm using ColdFusion Standard 2018, and I've tried the JDBC drivers ojdbc5.jar and ojdbc6.jar for Oracle 11g.  The driver connects successfully, is able to retrieve data, but when using the storedproc command, displays the "in or out" error.

[peterk8476818]

Here's the code that works with ColdFusion Oracle drivers, but not with latest JDBC drivers.

-- ColdFusion Code

<cfstoredproc procedure="SYSTEM_ACCESS.HAS_ADMIN_ACCESS" datasource="#ODBCdatasource#" returncode="yes">
<cfprocparam type="In" dbvarname=":iUser" value="#inConId#" cfsqltype="CF_SQL_VARCHAR">
</cfstoredproc>
<cfset rtnId = cfstoredproc.statusCode>

-- Oracle Code

FUNCTION HAS_ADMIN_ACCESS
(
iUser IN Varchar2
) RETURN number IS

...

-- Errors

java.sql.SQLException: Missing IN or OUT parameter at index:: 3
at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2107)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3657)
at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
at coldfusion.server.j2ee.sql.JRunPreparedStatement.execute(JRunPreparedStatement.java:101)

[peterk8476818]

Ok, one step close - I've looked at our old ColdFusion 11 setup and we had a swtich "ignoredbvarname".  Even though they're prefixed correctly, it still had the error.  Removing the dbvarname attribute from the code seems to let it continue.  To be continued...

[Charlie Arehart]

Ah, ok. So maybe you are moving to cf2018 from 11. You hadn't said that. Yes, this has been the cause of some confusion.

It was a certain update to 11 that re-introduced the honoring of cfprocparam use of dbvarname. And yes, then it had to have a prefix if the db required that.

And then Adobe introduced that jvm arg, to "turn that off". But they specifically DID NOT support that in cf2016 and beyond.

So this is very interesting to hear that a) the existence of the jvm arg had this negative impact, and b) only for the non-adobe driver, and c) even IF you do set correct dbvarname prefixes, and finally d) that it produces the error you saw (which is not at all obviously related to this combination of factors). 

It will be very interesting to hear if this proves to be the solution (to simply remove that jvm arg). 

As for why this may not be more widely observed, note that it's only on cf std that it would be *necessary* to use such a oracle driver, because it lacks the Adobe-provided oracle driver. (I do realize that one on cf ent/trial/dev might CHOOSE to use an oracle-provided driver also, but it's not typical. )

Do let us know as you learn more.