Node-oracledb: Error: ORA-12537: TNS:connection closed

[a1diablo]

I'm facing the following error when trying to make a connection to oracle always free database.
Failed to connect: { [Error: ORA-12537: TNS:connection closed] errorNum: 12537, offset: 0 }

Here is my script:

const oracledb = require('oracledb');
console.log('Getting connection');
let connection = oracledb.getConnection({
    user: 'ADMIN',
    password: '*****',
    connectString: '*****'
}).then(() => {
console.log('Connection established');
}).catch((err) => {
    console.log('Failed to connect: ', err);
});

Here are my version strings:

> process.platform
win32
> process.version
v10.16.3
> process.arch
x64
> require('oracledb').versionString
'4.0.1'

I'm trying to connect to Autonomous Transaction Processing Instance from Oracle Always Free cloud with database version 18c.

Thanks for taking the time to read this. Do let me know if more info is required from me.

[cjbj]

From the Cloud GUI page for your database, click 'DB Connection' to download the Client Credentials wallet.

Enter a new wallet password when prompted - this will not actually be needed for use with node-oracledb.

Unzip wallet.zip and edit sqlnet.ora. Set DIRECTORY to the directory with the extracted files. Keep the files safe. For node-oracledb you only need cwallet.sso, sqlnet.ora, and tnsnames.ora

Or don't edit sqlnet. if you put the extracted files in a default location like instantclient_19_3/network/admin. In this case there is also no need to set TNS_ADMIN

$ export TNS_ADMIN=/Users/cjones/q/Cloud/alwaysfree

$ sqlplus -l admin@cjdb1_high

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Oct 27 11:11:52 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password: 
Last Successful login time: Sun Oct 27 2019 11:07:38 +11:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL ADMIN@cjdb1_high> 

The next stage would be to create a normal (non admin) user to mess around with. Something like this:

define USERNAME = cj

begin execute immediate 'drop user &USERNAME cascade'; exception when others then if sqlcode <> -1918 then raise; end if; end;
/

create user &USERNAME;

alter user &USERNAME
      default tablespace data
      temporary tablespace temp
      account unlock
      quota unlimited on data;

grant  connect
     , resource
     to &USERNAME;

grant  create session
     , create table
     , create procedure 
     , create sequence
     , create trigger
     , create view
     , create synonym
     , alter  session
     , create type
     , soda_app
     to &USERNAME;

password &USERNAME

I'm working on a blog post for this.

[a1diablo]

I did as you mentioned and then I used the newly created username and password in oracledb.getConnection(). The error remains the same. Am I missing something?

[cjbj]

FIrewalls, typos, proxies?

[a1diablo]

I'm not using any proxies and turning off all firewalls didn't change a thing. Rechecked my username and password multiple times. I'm able to connect through SQL developer but not through oracledb in nodejs. Is it possible to turn on additional logging to know what is causing this problem, because my instance is certainly on as I'm able to connect to it through other means?

[cjbj]

Start by giving us actual screen logs (not screen shots - please) of a terminal window showing the listing of the directory with the wallet, the contents in sqlnet.ora, you setting TNS_ADMIN (if you don't use a default location for the wallet files), showing PATH containing the Instant Client libraries, and then running Nodejs

[a1diablo]

Sure. Here's the directory listing of the wallet placed inside network/admin of instant client.

Volume in drive C is Windows-SSD
 Volume Serial Number is 94D1-6E5D

 Directory of C:\oracle\instantclient-basic-windows.x64-19.3.0.0.0dbru\instantclient_19_3\network\admin

10/27/2019  08:54 AM    <DIR>          .
10/27/2019  08:54 AM    <DIR>          ..
10/27/2019  08:53 AM             6,661 cwallet.sso
10/27/2019  08:53 AM             6,616 ewallet.p12
10/27/2019  08:53 AM             3,241 keystore.jks
10/27/2019  08:53 AM                87 ojdbc.properties
10/27/2019  08:53 AM               114 sqlnet.ora
10/27/2019  08:53 AM             1,786 tnsnames.ora
10/27/2019  08:53 AM             3,335 truststore.jks
               7 File(s)         21,840 bytes
               2 Dir(s)  358,877,769,728 bytes free

Contents of sqlnet.ora file:

WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY="?/network/admin")))
SSL_SERVER_DN_MATCH=yes

My path:

echo %PATH%
-----;C:\oracle\instantclient-basic-windows.x64-19.3.0.0.0dbru\instantclient_19_3;-----

Contents of temp.js

const oracledb = require('oracledb');
console.log('Getting connection');
let connection = oracledb.getConnection({
    user: 'DUMMY',
    password: 'Dummy1@Password',
    connectString: 'adb.uk-london-1.oraclecloud.com:1522/**************_tp.atp.oraclecloud.com'
}).then((res) => {
    console.log('Connection established:', res);
}).catch((err) => {
    console.log('Failed to connect: ', err);
});

node temp.js

Getting connection
Failed to connect:  { [Error: ORA-12537: TNS:connection closed] errorNum: 12537, offset: 0 }

Really stuck on this for a couple of days now. I've recently started with the oracle database and the worst part is that I remember it connecting previously. Thanks again for taking your time going through this issue. Highly appreciated.

[dmcghan]

You need to update the contents of the sqlnet.ora. The directory should match the location of the credential/wallet files. See this video: https://www.youtube.com/watch?v=WaVdFZ90Jj8&t=7m39s

[a1diablo]

@dmcghan Thanks a ton mate! Your video was really clear and helpful in explaining what to do and why we're doing it. Great content!

[cjbj]

@a1diablo Was updating sqlnet.ora really necessary as part of your solution? The string "?/network/admin" should be being expanded by the Oracle net code to (in your case) C:oracleinstantclient-basic-windows.x64-19.3.0.0.0dbruinstantclient_19_3networkadmin

[a1diablo]

@cjbj Not really. What I understood from the behavior was, that I cannot use connect strings directly in nodejs code. I had to use the name from tnsnames.ora and set TNS_ADMIN as the path of the wallet contents; only then it would work.

[cjbj]

Since your wallet was in the default directory C:oracleinstantclient-basic-windows.x64-19.3.0.0.0dbruinstantclient_19_3networkadmin you don't need to set TNS_ADMIN or edit sqlnet.ora