4 Replies Latest reply on Feb 20, 2012 12:25 PM by Dennis John

    Proxy connection

    529865
      I have done everything possible to try to get the proxy connection to work to no avail. I have upgraded my machine to the latest version of ODP and have tried numerous configurations of the connection string. Based on the documentation below, I should be able to use the following connection string with no password as I have not used the AUTHENTICATED USING clause.

      However when I use the following connection string:

      <add key="ConnectionString" value="Data Source=DEV;User Id=myuserid;Proxy User Id=proxyuser;Proxy Password=proxyuserpassword;Pooling=true"/>

      I get the following error;

      Exception Information

      System.Data.OleDb.OleDbException: ORA-01017: invalid username/password; logon denied

      The only way I can establish a connection through the proxy is to add my password to the connection string. Just to verify that I was connecting through the proxy user (proxyuser). I tried to connect using another user id that had connect privileges.

      <add key="ConnectionString" value="Data Source=DEV;User Id=anotheruserid;Password=anotherpassword;Proxy User Id=proxyuser;Proxy Password=proxyuserpassword;Pooling=true"/>

      As expected I received the following error since the connect through grants were not set on this user id:


      System.Exception: Failed To Connect:ORA-28150: proxy not authorized to connect as client

      ---> Oracle.DataAccess.Client.OracleException ORA-28150: proxy not authorized to connect as client at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src,

      So it seems that we are making the proxy connection, it is just not allowing me to use the documented functionality of authenticating without the client password. I am not getting a lot of help from my DBAs on this issue so any head up would be appreciated.

      Thanks
        • 1. Re: Proxy connection
          gdarling - oracle
          Hi,

          I'm not quite clear on what you're doing. The first error seems to indicate you're using System.Data.Oledb. To the best of my knowledge, ORAOLEDB doesnt support proxy auth.
          With respect to proxy auth working without password with ODP, this works fine for me... not for you?

          Cheers,
          Greg


          /*
          drop user proxyacct cascade;
          drop user puser1 cascade;
          grant connect, resource to proxyacct identified by proxpass;
          grant create session to puser1 identified by pass;
          ALTER USER puser1 GRANT CONNECT THROUGH proxyacct;

          */
          using System;
          using System.Data;
          using Oracle.DataAccess.Client;

          public class testproxy
          {
          public static void Main()
          {
          string constr ="data source=orcl;user id=puser1;proxy user id=proxyacct;proxy password=proxpass";
          using (OracleConnection oc = new OracleConnection(constr))
          {
          oc.Open();
          Console.WriteLine("con1 connected");
          }
          }
          }
          • 2. Re: Proxy connection
            529865
            I think I have narrowed down the problem.

            We are altering an existing users to grant connect through the proxy account. I think because the existing user already has a password set, the proxy connection requires a password as well. Does this sound feasible and if so, is there a work around?
            • 3. Re: Proxy connection
              gdarling - oracle
              Hi,

              I'm still not able to reproduce your complaint. Here's what I did. Does it mimic what you're doing? Can you reproduce this doing exactly what I'm doing?

              The only thing I can think of is that you used the AUTHENTICATED USING PASSWORD clause, but you've already stated you didnt.

              Cheers,
              Greg

              SQL
              =======
              SQL> connect system/oracle
              SQL> grant connect, resource to bob identified by bob;

              Grant succeeded.

              SQL> connect bob/bob
              Connected.
              SQL> connect system/oracle
              Connected.
              SQL> grant connect, resource to bobproxy identified by bobproxypass;

              Grant succeeded.

              SQL> ALTER USER bob GRANT CONNECT THROUGH bobproxy;

              User altered.


              ODP
              =========
              using System;
              using System.Data;
              using Oracle.DataAccess.Client;

              public class testproxy
              {
              public static void Main()
              {
              string constr = "data source=orcl;user id=bob;proxy user id=bobproxy;proxy password=bobproxypass";
              using (OracleConnection oc = new OracleConnection(constr))
              {
              oc.Open();
              Console.WriteLine("con1 connected");
              }
              }
              }

              OUTPUT
              =========
              con1 connected
              • 4. Re: Proxy connection-- Pls help me in this below error
                Dennis John
                SQL> create user proxy_user
                2 identified by pw_proxy
                3 default tablespace users
                4 temporary tablespace temp;

                User created.

                SQL> create user target_user
                2 identified by pw_target
                3 default tablespace users
                4 temporary tablespace temp
                5 quota unlimited on users;

                User created.

                SQL> alter user target_user grant connect through proxy_user;

                User altered.

                SQL> grant create session,
                2 create table
                3 to target_user;

                Grant succeeded.

                SQL> connect target_user/pw_target@dev
                Connected.
                SQL> create table targets_table (
                2 col varchar2(10)
                3 );

                Table created.

                SQL> insert into targets_table values ('foo');

                1 row created.

                SQL> connect proxy_user[target_user]/pw_proxy@dev
                ERROR:
                ORA-01017: invalid username/password; logon denied


                Warning: You are no longer connected to ORACLE.