ODP.Net - Visual Studio 2022 connection

[twm1005]

Switching to using an Oracle Wallet. I have verified that I can connect via SQL developer and programmatically via .NET PowerShell using Oracle.ManagedDataAccess.dll (both Core and Framework). I'm trying to not install Oracle Client and just use extended connection strings.

This works in PowerShell:

        Add-Type -Path "Oracle.ManagedDataAccess.dll"
        $connectionString = "Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = XXXl )(PORT = 2484))(CONNECT_DATA = (SID = XXX)));User ID=XXX;Password=XXX!;Wallet_Location=c:\Users\XX\Documents\XX\wallet_v5;"
        $con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($connection_string)
        $con.Open()

I'm having an issue connecting to the same database with the wallet via: 1) Visual Studio 2022 - Server Explorer (tried via TNS alias and Advanced) 2) Oracle Developer Tools for VS Code (SQL and PLSQL) - New connection (tried TNSNAMES and ODP.NET connection string. 3) programmatically in C# using core.

Here is how I'm trying in C# Razor

OracleConfiguration.WalletLocation = @"(SOURCE = (METHOD = file)(METHOD_DATA = (DIRECTORY=c:\Users\XXX\Documents\XXX\wallet_v5)))";
OracleConfiguration.SqlNetWalletOverride = true;
OracleConfiguration.SqlNetAuthenticationServices = "(TCPS)";
OracleConfiguration.OracleDataSources.Add("DEV", "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=XXX)(PORT=2484))(CONNECT_DATA=(SID=XXX)(SERVER=dedicated)))");
OracleConfiguration.StatementCacheSize = 25;
OracleConfiguration.SelfTuning = false;
OracleConfiguration.BindByName = true;
OracleConfiguration.CommandTimeout = 60;
OracleConfiguration.FetchSize = 1024 * 1024;
OracleConfiguration.SendBufferSize = 8192;
OracleConfiguration.DisableOOB = true;
OracleConfiguration.TraceOption = 1;
OracleConfiguration.TraceFileLocation = @"C:\RBI";
OracleConfiguration.TraceLevel = 7;
OracleConnection orclCon = null;
orclCon = new OracleConnection("User ID=XXX;Password=XXX;data source=DEV");
orclCon.Open();

Seems the difference is the ODP.Net tools for Visual Studio and VS Code. But I copied the Oracle.ManagedDataAccess.dll from my VS solution and used it sucessfully in my test powershell script.

I get the following error in C# and VS Code:

Oracle.ManagedDataAccess.Client.OracleException: 'Oracle Communication: Failed to connect to server or failed to parse connect string'
This exception was originally thrown at this call stack:
    System.Collections.CollectionBase.System.Collections.IList.get_Item(int)
    System.Security.Cryptography.X509Certificates.X509CertificateCollection.this[int].get(int)
    OracleInternal.Network.TcpsTransportAdapter.Negotiate(OracleInternal.Network.ConnectionOption)
    OracleInternal.Network.TcpsTransportAdapter.ConnectIterate()
    OracleInternal.Network.OracleCommunication.ConnectViaCO(OracleInternal.Network.ConnectionOption, OracleInternal.Network.AddressResolution)

For Server Explorer: I get Failure during SSL handshake.

I don't understand why I can connect via PowerShell but nowhere else. I tried placing tnsnames/sqlnet files in bin/* and root of application. I attached my last trace file. Any help would be much appreciated.

RBI.EXE_PID_25164_TID_1_DATE_2023_09_25_TIME_11_26_38_060982.trc.txt

[alexkeh]

I think the problem is that your WalletLocation is using the contents of your sqlnet.ora. Instead, it should be set to only the wallet directory location (i.e. c:\Users\XXXX\Documents\XXX\wallet_v5).

[twm1005]

Thank you for your time! I removed the sqlnet.ora and tnsnames.ora entirely from the wallet location. I created a test console app (CORE) and installed Nuget Oracle.ManagedDataAccess.Core. I get same error.

Here is my code:

            using Oracle.ManagedDataAccess.Client;
        //OracleConfiguration.TnsAdmin = @"c:\Users\XXX\Documents\XXX\wallet_v5";
        OracleConfiguration.WalletLocation = @"(SOURCE = (METHOD = file)(METHOD_DATA = (DIRECTORY=c:\Users\XX\wallet_v5)))";
        OracleConfiguration.SqlNetWalletOverride = false;
        OracleConfiguration.SqlNetAuthenticationServices = "(tcps)";
        OracleConfiguration.OracleDataSources.Add("V2SLCPRD", "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=XXX)(PORT=2484))(CONNECT_DATA=(SID=V2SLCPRD)))");
        OracleConfiguration.StatementCacheSize = 25;
        OracleConfiguration.SelfTuning = false;
        OracleConfiguration.BindByName = true;
        OracleConfiguration.CommandTimeout = 60;
        OracleConfiguration.FetchSize = 1024 * 1024;
        OracleConfiguration.SendBufferSize = 8192;
        OracleConfiguration.DisableOOB = true;
        OracleConfiguration.TraceOption = 1;
        OracleConfiguration.TraceFileLocation = @"C:\RBI";
        OracleConfiguration.TraceLevel = 7;
        OracleConnection orclCon = null;
        try
        {
            orclCon = new OracleConnection("User ID=XXX;Password=XXX;data source=V2SLCPRD");
            orclCon.Open();
            OracleCommand orclCmd = orclCon.CreateCommand();
            orclCmd.CommandText = "select * from wf_employees where rownum <= 10 ";
            OracleDataReader rdr = orclCmd.ExecuteReader();
            while (rdr.Read())
                Console.WriteLine("Employee Name: " + rdr.GetString(0));

            Console.ReadLine();
            rdr.Dispose();
            orclCmd.Dispose();
        }
        finally
        {
            if (null != orclCon)
                orclCon.Close();
        }

Here is the error from Visual Studio

Oracle.ManagedDataAccess.Client.OracleException
      HResult=0x80004005
      Message=Oracle Communication: Failed to connect to server or failed to parse connect string
      Source=Oracle Data Provider for .NET, Managed Driver
      StackTrace:
       at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
       at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, OracleConnection connRefForCriteria, String affinityInstanceName, Boolean bForceMatch)
       at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword, OracleConnection connRefForCriteria)
       at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
       at Program.<Main>$(String[] args) in XXX:line 21

      This exception was originally thrown at this call stack:
        System.Collections.CollectionBase.System.Collections.IList.get_Item(int)
        System.Security.Cryptography.X509Certificates.X509CertificateCollection.this[int].get(int)
        OracleInternal.Network.TcpsTransportAdapter.Negotiate(OracleInternal.Network.ConnectionOption)
        OracleInternal.Network.TcpsTransportAdapter.ConnectIterate()
        OracleInternal.Network.OracleCommunication.ConnectViaCO(OracleInternal.Network.ConnectionOption, OracleInternal.Network.AddressResolution)

    Inner Exception 1:
    NetworkException: Oracle Communication: Failed to connect to server
[CONSOLEAPP1.EXE_PID_11700_TID_10_DATE_2023_09_25_TIME_16_10_44_664875.trc.txt](https://github.com/oracle/dotnet-db-samples/files/12719149/CONSOLEAPP1.EXE_PID_11700_TID_10_DATE_2023_09_25_TIME_16_10_44_664875.trc.txt)
 or failed to parse connect string

    Inner Exception 2:
[CONSOLEAPP1.EXE_PID_11700_TID_1_DATE_2023_09_25_TIME_16_10_44_574487.trc.txt](https://github.com/oracle/dotnet-db-samples/files/12719145/CONSOLEAPP1.EXE_PID_11700_TID_1_DATE_2023_09_25_TIME_16_10_44_574487.trc.txt)

    ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. (Parameter 'index')

I also attached teh Oracle traces. CONSOLEAPP1.EXE_PID_11700_TID_1_DATE_2023_09_25_TIME_16_10_44_574487.trc.txt CONSOLEAPP1.EXE_PID_11700_TID_10_DATE_2023_09_25_TIME_16_10_44_664875.trc.txt

[alexkeh]

Sorry, the problem with WalletLocation I was pointing out was this line of ODP.NET code:

OracleConfiguration.WalletLocation = @"(SOURCE = (METHOD = file)(METHOD_DATA = (DIRECTORY=c:\Users\XX\wallet_v5)))";

WalletLocation should be just the wallet directory, such as: OracleConfiguration.WalletLocation = @"c:\Users\XX\wallet_v5";

ODP.NET WalletLocation property only takes a directory unlike the sqlnet.ora WALLET_LOCATION property.

[twm1005]

I seee. I made that line change and still have the same error.

[alexkeh]

There may be something unexpected ODP.NET is encountering in your wallet. Can you run the following command and share the output? orapki wallet display -wallet . -pwd mypasswd1 -complete

ORAPKI is a utility that can be found with the Oracle DB server install or the full Oracle DB client install. It's not part of the Instant Client nor ODAC installs since it's an admin utility.

If you don't want to post the wallet contents publicly, you can email it to us: dotnet_us (at) oracle.com.

[twm1005]

Will do. Why would it work in my PowerShell test script (Not core) but not in my C#.net (Core)?
Thanks

[twm1005]

So the powershell test works with the wallet. I created a simple console app. (.net framework) and installed the latest Nuget Oracle.ManagedAccess package. I couldnt connect. Same error. I copied the .dll into my PowerShell test and same error. Only difference is the version of ODP. So then I went back to the C# console app test uninstalled ODP and installed the version I used for PowerShell and it worked. I got the CORE ODP.NET test to work by using an older version as well. I had to use version 2.19.180.

[alexkeh]

Oracle has been able to reproduce this issue. Bug 35852518 has been filed to track the problem and fix it.

[twm1005]

Thank you Alex for your time.
We just wont talk about that day and a half where I thought I was going crazy.....

[alexkeh]

Bug is fixed and planned for the next quarterly patch.

[monsm]

@alexkeh I have three servers running version 11.2.0.4.0 of the Oracle Server, and the client is running version 21.12.0 of Oracle.Manageddataaccess to connect to the server, two of the three servers connected successfully, the third returned an error”Oracle Communication: Failed to connect to server or failed to parse connect string”, looking forward to BUG repair.

[alexkeh]

@monsm You can try out ODP.NET 21.13 when it's released to check whether it resolves your issue.

However, this bug is specifically related to one-way TLS. It's a regression that was introduced with ODP.NET 21.10. If version 21.9 worked without the error, then it's not likely to be the same bug.

[monsm]

@alexkeh It doesn't work with 21.9,Version 21.9 returns an error ORA-12505. Could you give me a hot fix for 21.13 so I can test it? Thanks

[alexkeh]

@monsm If you still see the problem with 21.9, then bug 35852518 is not the same issue you are encountering.

[monsm]

@alexkeh The problem was solved, and both 21.12 and 21.9 versions worked fine after changing the sid of the configuration file to service_name. Only when Sid is used, 21.12 returns Oracle Communication: Failed to connect to server or failed to parse connect string, 21.9 returns ORA-12505, and the information returned is inconsistent.

[alexkeh]

ODP.NET 21.13 is now available on NuGet Gallery.