Forum Discussion

vikash_SB's avatar
vikash_SB
New Contributor
2 years ago

Unable to connect to Oracle database with service name (no SID)

I am using Licensed ReadyAPI version 3.48.0. Recently our Oracle DBA changed the SID way of connecting to Database and now only Service name is allowed for connection. I am able to connect to Oracle Database through SQL developer but not through Ready API tool. Earlier using SID I was able to connect through tool as well after putting ojdbc jar in /bin/ext folder. Could you please provide me a way . Thanks.

I have tried this but still did not get success - https://support.smartbear.com/readyapi/docs/testing/data-driven/drivers/connection-string.html.

Configuration
  • vikash_SB's avatar
    vikash_SB
    2 years ago

    Dear Rich,

     

    Thanks much for your response. I found and applied a working solution yesterday.

    That solutions resonates with what you have mentioned. What I did -

    1. In Settings (readyAPI) -> JDBC . Added new Driver and Connection String Template to accommodate Oracle ServiceName (jdbc:oracle:thin:<USER>/<PASSWORD>@<HOST:127.0.0.1>:<PORT:1521>/<ODBC_DSN_NAME>) connection.

    2. Since my ServiceName was containing "_" character. I received below warning and hence as suggested added the following parameter to \bin\ReadyAPI.vmoptions file.

    "The character '_' is not supported in JDBC connections strings since Java 17.0.3 and since ReadyAPI 3.41.0.
    Please remove the invalid characters or please try to use the JVM option -Dcom.sun.jndi.ldapURLParsing="legacy".      "

    3. I received this error message-"java.sql.SQLException: ORA-28040: No matching authentication protocol" , so downgraded the oracle jdbc connecter jar from ojdbc14.jar to ojdbc8.jar in \bin\ext folder.

     

    This solution worked for me and hope this solution helps someone !

     

    Cheers,

    Vikash

  • richie's avatar
    richie
    Community Hero
    2 years ago

    Hey vikash_SB 

     

    Whenever I try and connect to a database using Java I check a number of things.

     

    I determine the version of the RDBMS.  

    I determine the version of Java that is needed to support the correct JDBC drivers for the RDBMS version (making sure it's the correct 'bit type' of Java - either 32bit or 64bit)

    I ensure the correct version of Java is installed that supports the JDBC drivers

    I ensure I have the correct version of the JDBC drivers installed

    I then confirm the correct JDBC connection string URL for the RDBMS I'm trying to connect to.

     

    Some installed db interrogation tools that connect to Oracle don't bother using the tnsnames.ora entry - they just use the JDBC connection string  - I can't remember what SQL Developer uses.

     

    Have you updated the JDBC connection string to use the SERVICE NAME attribute rather than the SID within the ReadyAPI! preferences?

     

    cheers,

     

    Rich

  • vikash_SB's avatar
    vikash_SB
    New Contributor
    2 years ago

    Dear Rich,

     

    Thanks much for your response. I found and applied a working solution yesterday.

    That solutions resonates with what you have mentioned. What I did -

    1. In Settings (readyAPI) -> JDBC . Added new Driver and Connection String Template to accommodate Oracle ServiceName (jdbc:oracle:thin:<USER>/<PASSWORD>@<HOST:127.0.0.1>:<PORT:1521>/<ODBC_DSN_NAME>) connection.

    2. Since my ServiceName was containing "_" character. I received below warning and hence as suggested added the following parameter to \bin\ReadyAPI.vmoptions file.

    "The character '_' is not supported in JDBC connections strings since Java 17.0.3 and since ReadyAPI 3.41.0.
    Please remove the invalid characters or please try to use the JVM option -Dcom.sun.jndi.ldapURLParsing="legacy".      "

    3. I received this error message-"java.sql.SQLException: ORA-28040: No matching authentication protocol" , so downgraded the oracle jdbc connecter jar from ojdbc14.jar to ojdbc8.jar in \bin\ext folder.

     

    This solution worked for me and hope this solution helps someone !

     

    Cheers,

    Vikash