Forum Stats

  • 3,874,174 Users
  • 2,266,677 Discussions
  • 7,911,754 Comments

Discussions

Unable to connect via basic connection type

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

Comments

  • Christian.Shay -Oracle
    Christian.Shay -Oracle Posts: 2,241 Employee
    edited Nov 2, 2022 4:39PM

    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
    User_ULSE6 Member Posts: 5 Green Ribbon

    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
    Christian.Shay -Oracle Posts: 2,241 Employee
    edited Nov 4, 2022 8:07AM

    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
    User_ULSE6 Member Posts: 5 Green Ribbon

    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
    Christian.Shay -Oracle Posts: 2,241 Employee
    edited Nov 4, 2022 4:27PM

    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
    User_ULSE6 Member Posts: 5 Green Ribbon

    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
    Christian.Shay -Oracle Posts: 2,241 Employee
    edited Nov 11, 2022 9:46AM

    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.

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

  • User_ULSE6
    User_ULSE6 Member Posts: 5 Green Ribbon

    Hi,

    I did update my review on the VS Marketplace :)

    Thank you for the quick and successfull support!