CODEWITHSUNDEEP
asp.netoracle-databaseplsqlplsqldeveloperoracle23ai
Prescott Chartier
Prescott Chartier

Reputation: 1695

Upgrading Oracle Database from 11g to 23AI FREE and cannot log on from application

I'm upgrading my Oracle Database from 11g to 23AI FREE, got all my data, procedures, functions, packages and user accounts/information migrated from the 11g database to the 23AI database and I'm able to connect to the 23AI database using one of my normal user accounts with Sql Developer, but unable to connect using that same account information with my application. Same username and password, so I know it's valid but I get the error message:

ORA-01017: invalid credential or not authorized; logon denied

I'm obviously able to get to the database, but for some reason it's saying that the same credentials that I use to connect with Sql, Developer are not valid. I'm confused, am I missing something?

This is the connection string I'm using in my app:

"Data Source=FREE;User ID=myuser;password=password1"

This is the code:

   Dim ConnObject As New OracleConnection
   ConnObject.ConnectionString = "Data Source=FREE;User ID=myuser;password=password1"
   ConnObject.Open()

With Sql Developer I needed to execute the command Alter Session set container = freepdb1 before logging on as the application user, everything I've read online about 23AI says that I don't need to do that with an app created with Visual Studio, in this case Visual Studio 2017. The app functions flawlessly on the 11G database, so I know the app is good. Am I missing something here? Is there a new step I got to take that I didn't need when connecting to the 11g database?

This is my listener.ora for 23AI:

DEFAULT_SERVICE_LISTENER = FREE

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

This is the tnsnames.ora:

DATA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREE)
    )
  )
FREE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREE)
    )
  )

LISTENER_FREE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

I added the Data entry myself, that is the entry I used in the connection string on the 11g database, so I added it here so I could just copy over the app and it would connect, but it didn't turn out that way. Whatever entry I use I get the same error, ORA-01017: invalid credential or not authorized; logon denied. So either way, I'm able to get to the database, the database says my credentials aren't valid, yet I can connect using Sql Developer with the same username and password. I'm baffled.

Upvotes: 0

Views: 289

Answers (1)

Valentin Tabacaru
Valentin Tabacaru

Reputation: 89

I suspect You are using the 23ai FREE from the docker container. Different from 11g, 23ai FREE has a multitenant architecture and the one on the docker container has a root container database (CDB) using service FREE, and one pluggable database (PDB) where your normal user accounts should be using service FREEPDB1. These services should look like this in tnsnames.ora:

FREE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREE)
    )
  )

LISTENER_FREE =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


FREEPDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = FREEPDB1)
    )
  )

Change the the connection string you are using in your app to:

"Data Source=FREEPDB1;User ID=myuser;password=password1"

Upvotes: 0

Related Questions