I’ve tried everything I can think of. No matter what I do I get “invalid username/password” but I know the username and password are correct because they work in Oracle SQL Developer.
I’ve tried multiple versions of UiPath.Database.Activities from 1.7.1 to the latest.
I’m using provider Oracle.ManagedDataAccess.Client and this is the connection string:
Anyone out there ever get the “Connect to database” activity to connect to an Oracle database? I see a few posts but they’re old. The only solution offered was to set up an ODBC DSN but that shouldn’t be necessary.
We checked just now on our local test environment, and this connection string worked for Oracle DB: DATA SOURCE=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = FREE)));PASSWORD=xxx;USER ID=SYSTEM
With the latest version of the Database package.
In case this simple change of placing the password and user ID at the end doesn’t work… (I can’t see how this would be the issue here, but please try…)
We’d need a bit more info about your client and host environments: operating systems, Oracle version, are different locale settings involved on any side?
I tried your connection string, and with UiPath.Database.Activities 1.10.0 I get “the database does not accept your client’s authentication protocol”
If I downgrade to 1.7.1 then I get “invalid username/password”
I do not have the Oracle client installed on my computer, I didn’t think it’s necessary since the connection is direct using the UiPath database library which includes Oracle.ManagedDataAccess.Core.
Yes I understand how to do it with an ODBC data source, but that shouldn’t be necessary. We should be able to connect using a direct connection string, but it doesn’t work.
We’re trying to reproduce it. I would trust this error above more than the one on the previous Database package version because we specifically updated the Oracle dependency in the latest Database package to make it more robust.
Following this hint, is there any other configuration that you could share about your DB server that might seem relevant? I have a feeling that we have to augment the connection string with extra details, some of which are assumed by the Oracle SQL Developer environment.
I know just enough about this to be dangerous, but this was sent to me by our Oracle admin in response to me asking about the client authentication protocol error:
By the way, since login works from SQL Plus and Oracle SQL Developer, as well as in the ODBC “Test Connection” this all indicates everything is configured correctly on our side.
I can understand this sentiment; it’s something I typically go for myself.
Let us investigate based on the above information. If I may, could you still share the version of the Oracle driver that you use on your client machine?
We are still investigating, but something to check, based on the information you provided here:
And here:
The error message mentions that the values for these two parameters should be set on both the server and the client side.
My current thinking is that maybe SQL Plus and Oracle SQL Developer set it, and our package doesn’t (we’ll investigate that part).
If that’s the case though, it might work if you set the values in the sqlnet.ora file on your client machine. Do you think you could try to see if it helps?
Since using the DSN to connect in the automation works, that shows everything is set correctly on my end and on the Oracle server.
The connection using Oracle.ManagedDataAccess.Core that’s embedded in UiPath.Database.Activities does not use any installed drivers nor configuration. I was able to attempt the connection (but received invalid username/password) in the automation without having any client nor drivers installed. I simply installed the client to get the DSN connection working.
I directly installed Oracle.ManagedDataAccess.Core 23.5.0 in a test project. I tried connecting using Invoke Code. The code is fairly simple…
Using connection As New Oracle.ManagedDataAccess.Client.OracleConnection(connectionString)
Try
connection.Open()
Console.WriteLine("Connection successful!")
Catch ex As OracleException
Console.WriteLine("OracleException: " & ex.Message)
End Try
End Using
I passed in the connection string with an argument. And with that, I was getting the error “The database does not accept your client’s authentication protocol; login denied.”
I removed the direct installation of Oracle.ManagedDataAccess.Core and installed UiPath.Database.Activities 1.10.0 and the connection still worked.
So it seems you need to allow a way to set this in the Connect to Database activity with a property so we can set it.
Now my question becomes…how can I convert the output of that code with is datatype Oracle.ManagedDataAccess.Client.OracleConnection to UiPath.Database.DatabaseConnection so we can use it in other UiPath activities such as Run Query?
I see what you are up to. In theory, it could just work by you modifying this static class with Invoke Code and then simply using the Connect to database activity. Does it work then?
Tried that, didn’t work. Not sure why. Maybe you have some code inside Connect to Database that’s overriding it.
newConn = new DatabaseConnection()
newConn.Initialize(oracleConn)
Works perfectly.
What I ended up with is a custom activity I wrote that uses the Invoke Code to make the database connection and reflects it back to a UiPath DatabaseConnection. Works great, we just use the custom activity to connect and then all the other UiPath database activities work.
Here’s the final Invoke Code code. I created an argument that allows us to pass whichever version of the logonclient setting we want to use.
Select Case AllowedLogonVersion
Case "Version8"
Oracle.ManagedDataAccess.Client.OracleConfiguration.SqlNetAllowedLogonVersionClient = OracleAllowedLogonVersionClient.Version8
Case "Version9"
Oracle.ManagedDataAccess.Client.OracleConfiguration.SqlNetAllowedLogonVersionClient = OracleAllowedLogonVersionClient.Version9
Case "Version10"
Oracle.ManagedDataAccess.Client.OracleConfiguration.SqlNetAllowedLogonVersionClient = OracleAllowedLogonVersionClient.Version10
Case "Version11"
Oracle.ManagedDataAccess.Client.OracleConfiguration.SqlNetAllowedLogonVersionClient = OracleAllowedLogonVersionClient.Version11
Case "Version12"
Oracle.ManagedDataAccess.Client.OracleConfiguration.SqlNetAllowedLogonVersionClient = OracleAllowedLogonVersionClient.Version12
Case "Version12a"
Oracle.ManagedDataAccess.Client.OracleConfiguration.SqlNetAllowedLogonVersionClient = OracleAllowedLogonVersionClient.Version12a
End Select
Try
Dim OracleDBConnection = New Oracle.ManagedDataAccess.Client.OracleConnection(ConnectionStr)
OracleDBConnection.Open()
UiPathDBConnection = New UiPath.Database.DatabaseConnection
UiPathDBConnection.Initialize(OracleDBConnection)
Catch ex As Exception
ErrorMsg = ex.Message
End Try
