Unable to connect via basic connection type

User_ULSE6

Hi,

have described the issue on the comment section of the VSC extension, in short:

When I use the basic connection type, enter the complete connection string most of the time I am unable to create a connection to the DB and get an ORA-12154 error. According to our DBA the connection request does never reach the server. Most as in on a few occasions I was able to create a connection, however I was never able to pinpoint the reason why it did work or most of the times didn't (was first thinking it might be an VPN issue but the behavior is the same when I am on premise).

Connection with the same connection strings as used in the basic connection work for example via python and sqlalchemy or directly via SQL Developer

When using the TNS Alias connection type and pointing to the corresponding tnsnames.ora file the connection works without any issues.

Since most of our DBs end up in the tnsnames.ora file eventually it is not that big of deal for me but it is an issue for us if servers are not yet in the ora file.

Few informations about the versions I am currently using:

• Oracle Developer Tools for VS Code Version 21.5.0
• .Net Install Tool for Extensions Authors 1.6.0
• VS Code Version 1.72.2

Christian.Shay -Oracle

You can only use the Basic connection type for certain types of database connections (specifically, ones where only host/ip, port and service name are sufficient. Connection types that need to use info in tnsnames.ora, sqlnet.ora, or ldap.ora, for example those using directory servers or wallets will not be able to connect using that method.

I am not sure of your database configuration, but perhaps a directory server is involved or perhaps you are using a wallet.

Can you please check the Tns Admin Location for connections that work for you, and please provide:

1) tnsnames.ora file

2) tnsalias you are choosing from that file

3) sqlnet.ora if it exists

4) ldap.ora if it exists

(You should redact info of course)

5) And please provide the info you are entering into the Basic connection, particularly the service name.

Finally, please note that users can paste the connection info from a tnsnames.ora into the Advanced connection type. So if you want you could provide them everything that comes after the "=" (equals sign) from one tnsnames.ora entry to use as a connection string in the Advanced type.

User_ULSE6

Hi,

thank you for the reply!

Just to clarify: when using the TNS Alias connection type every connection saved in the tnsnames.ora files works without any issues/problems, the problems only occur when the basic connection type is used (did also test the advanced connection type - which did also work without any problems for entries copied from the tnsnames.ora files)

Since I do not feel comfortable posting the complete tnsnames.ora file here, here is the (redacted) structure of the alias entry I am trying to reach via the basic connection type (again connection via advanced or tns alias method works flawlessly):

POWERDATENT.ORACLE.XXX.YYY =

  (DESCRIPTION =

  (ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)

  (HOST = POWERDATENT.ORACLE.XXX.YYY)(PORT = 1521)))

  (CONNECT_DATA = (SERVICE_NAME = POWERDATENT)))

the sqlnet.ora file contains the following data:

NAMES.DEFAULT_DOMAIN  = oracle.XXX.YYY

# Local Naming

NAMES.DIRECTORY_PATH  = (TNSNAMES)

# Client

LOG_DIRECTORY_CLIENT  = C:\temp

LOG_FILE_CLIENT     = client

TRACE_DIRECTORY_CLIENT = C:\temp

TRACE_FILE_CLIENT    = client

TRACE_LEVEL_CLIENT   = off

TRACE_UNIQUE_CLIENT   = on

# tnsping

TNSPING.TRACE_DIRECTORY = C:\temp

TNSPING.TRACE_LEVEL   = off

# authentication

SQLNET.AUTHENTICATION_SERVICES= (none)

ldap.ora does not exist in the file location

I have attached the screenshot of the information I am entering into the basic connection type (instead of xxx.yyy I am of course using the real values). When now trying to create the connection I get the ORA-12154 error mentioned in my original post.

As mentioned before for us it is not that big of a deal, since we can fall back to TNS Alias (or now that I know advanced connection) to connect to the DBs which exist in the tnsnames.ora file. However from time to time there is a new server which is not yet in the tnsnames.ora so it would be cool to be able to use the extension instead of having to fall back to sql developer.

And as I said before the identical data used in sql developer or used within python for sqlalchemy does work.

Christian.Shay -Oracle

Hi,

With connection type Basic, can you please try connecting with the same connection info you used before except set Service name to: POWERDATENT.oracle.XXX.YYY

If that does not work, please make a backup copy of sqlnet.ora, then edit sqlnet.ora like so:

Change:

NAMES.DIRECTORY_PATH  = (TNSNAMES)

to:

NAMES.DIRECTORY_PATH  = (EZCONNECT, TNSNAMES)

Then close VS Code, reopen and try to connect, this time using the original service name: POWERDATENT

User_ULSE6

Hi,

the first solution using POWERDATENT.oracle.XXX.YYY as service name does lead to the same behaviour as before (ORA-12154) error.

The sqlnet.ora file is located on a network drive, where I have no editing rights - so I can't try the second proposed solution.

Christian.Shay -Oracle

You can copy all the files in that shared directory to a non-share directory and then in the Oracle Developer Tools for VS Code extension settings, change the "Config File Folder" and the "Wallet File Folder" settings to the local folder.

Then make the changes I described earlier, restart VS Code, and retry.

User_ULSE6

Hi Christian,

when using local files the change from:

NAMES.DIRECTORY_PATH  = (TNSNAMES)

to:

NAMES.DIRECTORY_PATH  = (EZCONNECT, TNSNAMES)

Does solve the issue for me.

When doing this I did also actually discover that within the Extension Settings the path was/is set to:

C:\Users\USERNAME\Oracle\network\admin

which was empty (I copied the two .ora files to that location and did the changes there).

I guess to solve this issue more permenantly while still having access to the latest tnsnames.ora files I should talk with one of our DBA to see if the EZCONNECT part can be added to the sqlnet.ora file and point the extension to the network drive, correct?

Christian.Shay -Oracle

Hi,

Glad you got it working.,

Yes, the dba can add EZCONNECT to NAMES.DIRECTORY_PATH on the share. However, he or she could also just delete the entry entirely as the default includes both of them.

https://docs.oracle.com/en/database/oracle/oracle-database/21/netrf/parameters-for-the-sqlnet.ora.html#GUID-65EF582E-F414-43EE-A70F-EC90E0B7292F

Could you please update your review on the VS Marketplace to indicate that you identified the issue?

User_ULSE6

Hi,

I did update my review on the VS Marketplace :)

Thank you for the quick and successfull support!