We have successfully configured geo-server to connect to an Oracle database with a username/password using the Oracle plugin.
However, we are trying to connect to another Oracle database that uses the TCPS protocol and geo-server is refusing the connection. Has anyone successfully connected to Oracle using the TCPS protocol?
We can use an Oracle Wallet (which would have the server certificates) as well but couldn’t figure out how to configure this in geo-server
One option if you are finding limitations in GeoServer JDBC connections - is to configure in tomcat and access in GeoServer via JNDI Name.
Tomcat has a wider range of connections to work with…
Reading about it you need to collect some jars from oracle and ensure they are on the class path (so added to GeoServer WEB-INF/libs), or to Tomcat if you are using JNDI.
What steps have you done so far?
We followed the steps in: Setting up a JNDI connection pool with Tomcat — GeoServer 2.28.x User Manual
Here are the settings in the tomcat config file:
The only jar file we put in the correct path was: ojdbc11.jar
(from the geo-server oracle plugin package)
That was the only jar referenced in that document
Basic Store Info from the geo-server UI:
Data Source Name: jnditest
Enabled box is checked
dbtype: oracle
jndiReferenceName: java:comp/env/jdbc/oralocal
Everything else is blank
The error message in the stdout log as well as the UI says:
Error creating data store, check the parameters. Error message: Cannot find JNDI data source: Cannot find JNDI data source: java:comp/env/jdbc/oralocal
this is the text in the tomcat xml config file:
<Resource name="jdbc/oralocal"
auth="Container"
type="javax.sql.DataSource"
driverClassName="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@oracle:1521/MY_DB"
username="MY_USER" password="my_password"
maxTotal="20"
initialSize="0"
minIdle="0"
maxIdle="8"
maxWait="10000"
timeBetweenEvictionRunsMillis="30000"
minEvictableIdleTimeMillis="60000"
testWhileIdle="true"
poolPreparedStatements="true"
maxOpenPreparedStatements="100"
validationQuery="SELECT SYSDATE FROM DUAL"
maxAge="600000"
rollbackOnReturn="true"
/>
We are running Tomcat and geo-server in a container and my colleague just found different configuration instructions for configuring JNDI in a container. We will give those steps a try next
We found instructions for JDNI/Tomcat that were specific to running geoserver in a container: GitHub - geoserver/docker: GeoServer docker image
We are getting further along. We can now get Tomcat/geoserver to use the tnsnames.ora for connection info. But, we are still stuck trying to figure out how to make it use the wallet found in the path defined by TNS_ADMIN (for certificates needed to make the TCPS connection)
Hi,
not sure about TCPS, but we struggled as well, trying to connect to OCI databases using OCI wallets. For us the problem was that OracleNGDataStoreFactory (Oracle NG) makes some complex assumptions when parsing the “database” configuration string. We needed the ability to specify db alias (mapped to a DB connection in the tnsnames.ora) in the connection string, but the factory just simply wouldn’t allow us to, because of the complex parsing.
So our “workaround” was to code a new Oracle data-store factory, that basically works exactly like the Oracle NG, but makes absolutely no restrictions on what the “database” configuration string is. That allows us to use any of the allowed JDBC URI syntax (including the one that allows using tnsnames aliases).
With this, it should be possible to configure your data-store like this:
# 1) the alias from tnsnames
my_db_alias
# 2) alias and path to wallet
my_db_alias?TNS_ADMIN=/path/to/wallet
# 3) or even combine multiple parameters
my_db_alias?HTTPS_PROXY=proxy_host&HTTPS_PROXY_PORT&TNS_ADMIN=/path/to/wallet
# 4) any other supported JDBC connection URI
//host:port:db
This connection string would then by prefixed by jdbc:oracle:thin:@ and otherwise left as is. You could of course still be using -Doracle.net.tns_admin=//path/to/wallet, then in the above examples, the TNS_ADMIN would not be required.
Btw, the OracleNGOCIDataStoreFactory was no alternative for us as we didn’t want to be relying on the existence of a native library.
If such a factory would be of general interest, we could surly provide a PR for it t be added to gt-jdbc-oracle.
Nice work @skalesse - Improvements are very much welcome.
Is it possible to improve or relax the validation of the database string in the existing factory? I know validation is often added as a security measure (not sure if that is the case here for connecting).
Jody
Hi @jive
Actually I think it was not possible to fix the existing factory in a backward compatible fashion (which is why we didn’t make bug-type ticket in Jira).
If I remember correctly it was this part: geotools/modules/plugin/jdbc/jdbc-oracle/src/main/java/org/geotools/data/oracle/OracleNGDataStoreFactory.java at main · geotools/geotools · GitHub
See how that code (unfolded) basically shuts off the generic use-case that would have been JDBC_PATH + db:
protected String getJDBCUrl(Map<String, ?> params) throws IOException {
...
if (db.startsWith("(") || db.startsWith("ldap://"))
return JDBC_PATH + db;
else
if (db.startsWith("/") && host != null && port != null)
return JDBC_PATH + "//" + host + ":" + port + db;
else
if (host != null && port != null)
return JDBC_PATH + host + ":" + port + ":" + db;
else
throw new IOException(...)
...
}
Problem is, I cannot relax the first condition, because to me it seems that then, existing configurations might not work anymore.
But yeah, I totally agree with you that if we had any chance to fix the above code in a backward compatible fashion, than that would help a lot.
On the other hand, luckily those factories are not so heavy-weight. So not much maintenence is to be expected. The one I coded is 97 LoC, because it inherits from the OracleNG and overrides String getJDBCUrl(Map<String, ?> params)and void setupParameters(Map<String, Object> parameters).
Oh and the reason why we chose not to use the OracleNGOCIDataStoreFactory was surely because it hard-codes the jdbc path to “jdbc:oracle:oci:@” which makes the oracle driver try using the native oracle lib which we did not want at all.
So maybe the best bet for trying to fix existing code might be to try not card-code the jdbc path in OracleNGOCIDataStoreFactory, but somehow allow this to be configured to other allowed values.
… and then make a documentation that tells users of OracleNGOCIDataStoreFactory that the parameter “alias” can basically be anything that is an allowed JDBC URI.
Huh, there should still be away.
Perhaps an application property org.geotools.data.oracle.factory-validation=false to allow those checks to be disabled, and thus allow “unsafe” use.
Or if you want a safe option org.geotools.data.oracle.factory-validate allowing the admin to supply a regex (that can be supplied externally) used before the final IOException.