8 Replies Latest reply: Dec 25, 2009 4:48 AM by 743081 RSS

    trusted connections through odp.net

    607525
      My problem is that I can't get to connect through odp.net to Oracle using trusted connections.
      If you type "windows authentication odp.net" on google you'll get more links to sample codes like this one
      OracleConnection con = new OracleConnection();
      con.ConnectionString = "User Id=/;Data Source=orcl;";
      con.Open();
      Console.WriteLine("Connected to Oracle" + con.ServerVersion);

      but for some weird reason it doesn't work... I can connect with my external user through isqlPlus, but can't seem to do it with odp.net

      When using MS NET Managed Provider for Oracle (System.Data.Oracle.Client.dll library) something like the piece of code presented earlier with the connection string changed a bit (by using "Integrated Security=true" ) works.

      So I created an Oracle user authenticated externally and can connect to Oracle with it through isqlPlus (connect /) or through MS Net Managed Provider for Oracle, but can't seem to do it through odp.net.

      When trying to connect with odp.net I get ORA-1017 (invalid user/password).

      My OS is Win2k3, Oracle 10.1g , Oracle.DataAccess.dll for Oracle 10g or 11g (none of these libs works in this situation).

      I think there has to be made some kind of tweak to Oracle config in order to successfully connect with odp.net trusted connections.

      If you have any idea on this one please reply.
        • 1. Re: trusted connections through odp.net
          607681
          I also have this problem.
          • 2. Re: trusted connections through odp.net
            gdarling - oracle
            Hi,

            I cant say I've ever had an issue connecting via ODP with OS Authentication, when I was able to connect with sqlplus, or MS ODP using OS Auth.
            That is to say, with a console app at least. Is that what you're testing?
            I'd recommend testing with a simply console app if you're not already.
            Using OS Auth from IIS or with remoting is a whole different story though, and can require some pretty complicated setup.


            Greg
            • 3. Re: trusted connections through odp.net
              Mark Williams-Oracle
              Much like Greg, I've not had issues with using o/s authentication from odp.net once everything is all set up and working... one idea - it may be worthwhile to enable connect auditing in the database. i.e. SQL> audit connect;

              Then try to connect using the o/s authenticated account from the odp.net application. After this you can check the sys.aud$ table and see what the USERID column has...

              Maybe that will reveal enough information to help.

              - Mark
              • 4. Re: trusted connections through odp.net
                607525
                Greg..
                i wasn't just testing a console app... it was just a simple example to express the context of my problem

                Mark
                the problem may be lied to the fact that I was running my tests on a VM and maybe this could be one of the problems...
                I've tried to test my application in Win Vista and Oracle10.2 and it worked just fine.
                I'll try to follow your advice to see what's going on...
                Thanks to both of you.
                • 5. Re: trusted connections through odp.net
                  485735
                  I am also having the problem of connecting with ODP.NET (ODAC1110620) with operating system authentication.

                  I have my database set up properly to do this. I can connect with SQL*Plus. I can connect with System.Data.OracleClient provider connect strings like

                  connectionString="Data Source=//localhost/xe;Integrated Security=true;"

                  According to ODP.NET Developer Guide (http://download.oracle.com/docs/cd/B28359_01/win.111/b28375/toc.htm) I should be able to use:

                  connectionString="Data Source=//localhost/xe;User Id=/"

                  with the Oracle.DataAccess.Client provider - however I get only ORA-1017 (invalid username/password). With this provider, if I use a known user id and password, bypassing external authentication it works.

                  Am I missing something on the connectionString syntax of external authentication with ODP.NET or is there some ODAC setup issue I'm missing?
                  • 6. Re: trusted connections through odp.net
                    24208
                    Hi Michael,

                    I just put together a VMWare virtual machine using WinXP, Oracle XE, ODAC 11g, and Visual Studio C# Express Edition. I was able to create an os authenticated user in the XE database and connect via os authentication from a VS console app. The code I used is:
                    using System;
                    using System.Data;
                    using Oracle.DataAccess.Client;
                    using Oracle.DataAccess.Types;
                    
                    namespace XETest
                    {
                      class Program
                      {
                        static void Main(string[] args)
                        {
                          string constr = "Data Source=//localhost/xe;User Id=/";
                    
                          using (OracleConnection con = new OracleConnection(constr))
                          {
                            try
                            {
                              con.Open();
                            }
                            catch (OracleException ex)
                            {
                              Console.WriteLine(ex.Message);
                            }
                    
                            if (con.State == ConnectionState.Open)
                            {
                              Console.WriteLine("Connected to Oracle as:");
                              Console.WriteLine("  Version=" + con.ServerVersion);
                              Console.WriteLine("  " + con.ConnectionString);
                              Console.WriteLine();
                            }
                          }
                    
                          Console.WriteLine("ENTER to continue...");
                          Console.ReadLine();
                        }
                      }
                    }
                    Which resulted in the following output:
                    Connected to Oracle as:
                      Version=10.2.0.1.0
                      Data Source=//localhost/xe;User Id=/
                     
                    ENTER to continue...
                    I am not sure if you are able to connect via os authentication in your case using SQL*Plus from the 11g ODAC home or if you are connecting via SQL*Plus from the XE home.

                    A couple of things to check:

                    - Be sure you have a sqlnet.ora file in the 11g ODAC home (%ORACLE_HOME%\network\admin)

                    - If you do have a sqlnet.ora file there be sure it includes:
                    SQLNET.AUTHENTICATION_SERVICES = (NTS)
                    Thanks,

                    Mark
                    • 7. Re: trusted connections through odp.net
                      640872
                      I am seeing the same issues, for life of me, I cannot connect using the ODP framework. But if I switch my connection string to..."Data Source=xe;Integrated Security=True;" and use the System.Data.OracleClient framework, then everything works.
                      • 8. Re: trusted connections through odp.net
                        743081
                        Setting SQLNET.AUTHENTICATION_SERVICES = (NTS) in sqlnet.ora solved the issue.
                        Thank you Mark.