locked
ORA-01017 using OracleClient provider RRS feed

  • Question

  • Greetings, folks.

    Here I am againg, still battling my way with SSMA.  We have a solution almost finished for a inhouse ETL using SSMA, but now we're facing a login problem.  We're using SSMAforOracleConsole to automate our database conversions, so in our script we have the oracle server section like this:

      <!--tns
      <oracle name="sourceserver">
        <tns-name-mode>
          <connection-provider value="OleDB Provider" />
          <service-name value="sourceserver" />
          <user-id value="sourceuser" />
          <password value="sourcepwd" />
        </tns-name-mode>
      </oracle> 
      tns-->
      
      <!--ezconnect
      <oracle name="sourceserver">
        <standard-mode>
          <connection-provider value ="OracleClient" />
          <host value="ezhost" />
          <port value="ezport" />
          <instance value="ezsid" />
          <user-id value="sourceuser" />
          <password value="sourcepwd" />
        </standard-mode>
      </oracle> 
      ezconnect-->  


    Depending on how the user inputs the Oracle server name (either a tns entry or a full server:port/sid string) we uncomment the accordingly bloc and change the respectives values for the connection.

    This works perfectly for the tns mode with the OleDB Provider, but the ezconnect bloc is giving the famous ORA-01017 error, prompting an invalide username/password.  The thing is the username and password are both correct, as well as all the other connection values:

      <oracle name="ora8">
        <standard-mode>
          <connection-provider value ="OracleClient" />
          <host value="ora8.local.ca" />
          <port value="1521" />
          <instance value="orcl" />
          <user-id value="myuser" />
          <password value="my_secret_password" />
        </standard-mode>
      </oracle> 

    This raises the mentionned error, while connection with the same values works correctly with SQLPlus:

    c:\> sqlplus myuser/my_secret_password@ora8.local.ca:1521/orcl.local.ca
    
    SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 20 12:02:06 2019
    
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    
    SQL>
    
    
    


    The problem does not arise when using the other block though, the tns mode:

      <oracle name="ora8">
        <tns-name-mode>
          <connection-provider value="OleDB Provider" />
          <service-name value="ora8" />
          <user-id value="myuser" />
          <password value="my_secret_password" />
        </tns-name-mode>
      </oracle> 

    So, the username/password is not the issue here, so I supposed there's something to do with the OracleClient provider used by the standard-mode.  I googled the issue but found nothing relevant.  In a post somebody told that the client was incompatible/too old, but I don't think this applies.  As shown with the SQPLus above, I'm currently using a 11.2 against an Oracle 12.1, so its perfectly compatible.

    A workaround would be require only TNS connection and refuse ezconnect strings when launching our process, but this is not really suitable since some of our clients don't even use tnsnames; our applications handle correctly ezconnect strings so should this homemade ETL.

    So... any ideas?

    Friday, September 20, 2019 4:18 PM

Answers

  • Hello,

    I should start by saying that it's very hard for us to troubleshoot such errors as we don't own Oracle connectivity components and are not allowed to reverse-engineer them to find bugs. For what it's worth, OracleClient in SSMA starting from version 8.0 is using "Oracle Data Provider for .NET" (aka ODP.NET), not an old OCI, so you may be able to find some additional information related to ODP.NET and your error.

    Also, you should be able to use tns-name-mode with OracleClient and standard-mode with OleDB provider. Although ODP.NET may require tnsnames file in some special location, I personally never used it with ODP.NET. This is something that should be described somewhere in Oracle's documentation, though.

    I'm also curious, if standard-mode with OracleClient works in GUI version of SSMA? We've seen issues with SSMA console before, where people didn't escape special characters properly (i.e. "&" should be "&amp;" when used within an XML attribute, etc.).

    Hope this helps,
    Alex.

    Monday, September 23, 2019 5:27 PM

All replies

  • Hi Leandro,

    How is your sqlnet.ora? Once i had a problem like this and i have to add these values in sqlnet file because of the OleDB version.

    SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
    SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

    or maybe in your case:

    SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
    SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

    Regards,
    Rafael


    • Edited by Rafael S. Melo Friday, September 20, 2019 5:43 PM Add more info
    Friday, September 20, 2019 5:41 PM
  • Hello,

    I should start by saying that it's very hard for us to troubleshoot such errors as we don't own Oracle connectivity components and are not allowed to reverse-engineer them to find bugs. For what it's worth, OracleClient in SSMA starting from version 8.0 is using "Oracle Data Provider for .NET" (aka ODP.NET), not an old OCI, so you may be able to find some additional information related to ODP.NET and your error.

    Also, you should be able to use tns-name-mode with OracleClient and standard-mode with OleDB provider. Although ODP.NET may require tnsnames file in some special location, I personally never used it with ODP.NET. This is something that should be described somewhere in Oracle's documentation, though.

    I'm also curious, if standard-mode with OracleClient works in GUI version of SSMA? We've seen issues with SSMA console before, where people didn't escape special characters properly (i.e. "&" should be "&amp;" when used within an XML attribute, etc.).

    Hope this helps,
    Alex.

    Monday, September 23, 2019 5:27 PM
  • Thanks for you answers.

    I tried Rafael's suggestion and tweaked the sqlnet.ora, but unfortunetaly nothing changed; in fact I just tried the local sqlnet.ora, should I modify the server's one?

    Alexander's tip about using standard-mode with OleDB made me feel silly.  I just copied the examples given in the sample XML files so I thought standard mode was exclusively OracleClient and tns-mode was exclusively OleDB.  Never thought about changing the provider in the blocs to test.  So I did and voilà: it correctly used the standard mode (the ezconnect string) with the OleDB.

    Still about the GUI version asked by Alexander, yeah, I also got curious about this because although I won't be using GUI I started studying SSMA there, but since then I had connectivity problems regarding the provider.  Here are my tests:


    *** USING THE GUI ***

    Provider: Oracle Data Provider for .NET
    Mode: Standard mode
    Server name: ora8.local.ca
    Server port: 1521
    Oracle SID: orcl
    Username: myuser
    Password: my_secret_pass

    ERROR: ORA-01017: invalid username/password; logon denied.

    Conclusion: looks like the provider could reach the database, so the ezconnect string is OK.  When validating username/password something went wrong then the connection is refused, although they are correctly.

    ======================================================================================


    Provider: Oracle Data Provider for .NET
    Mode: TNSNAME mode
    Connection identifier: ora8
    username: myuser
    Password: my_secret_pass

    ERROR: ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

    Conclusion: here it seems the provider could not find the right entry in the tnsnames file.  The entry is correct since I can manually connect with sqlplus myuser/my_secret_password@ora8.

    ======================================================================================


    Provider: OLEDB Provider
    Mode: Standard mode
    Connection identifier: ora8.local.ca
    Server port: 1521
    Oracle SID: orcl
    username: myuser
    Password: my_secret_pass

    SUCCESS!!!

    Conclusion: The OLEDB connector could reach and connect to the database using the very same ezconnect string as the OracleClient, so this proves that the username/passwrod are both correct.

    ======================================================================================


    Provider: OLEDB Provider
    Mode: TNSNAME mode
    Connection identifier: ora8
    username: myuser
    Password: my_secret_pass

    SUCCESS!!!

    Conclusion: once again the OLEDB connector correctly connected with the very same TNS entry refused by the OracleClient.

    Like I said, as I intent to use the console only, I did not pay much attention to these problems in the begining, but it seems the problem is the Oracleclient.  Still there's something bothering me about the OleDB.  Now I got lost and don't recall correctly why, but SSMA asked me to install ODAC (Oracle Data Access Components).  This is a bit of a hassle since we already request an Oracle client installed; moreover, installing said ODAC is not exactly a piece of cake.  If I recal correctly, the 12.1 version is installed in a command line with a batch file while the 12.2 have the whole Oracle Universal Installer.

    I'm a bit lost here why I had to install ODAC: did it (tried to) replaced the original OracleClient or did it installed OleDB?

    Anyway, tomorrow I will clean everything and try a fresh install to analyze and have more details the whole situation.  Will post here my fidings.

    Thanks for your help, pals.

    Tuesday, September 24, 2019 2:32 AM
  • Hey,

    "I tried Rafael's suggestion and tweaked the sqlnet.ora, but unfortunetaly nothing changed; in fact I just tried the local sqlnet.ora, should I modify the server's one?"

    Yes, this change should be done in the server side. You need restart/reload the listener too.

    Regards,
    Rafael

    Tuesday, September 24, 2019 10:52 AM
  • Hi again, folks.

    Thanks Rafael for your input.  Unfortunately modifying parameters or values in the production database is not an option; we have to stick with client side config only.

    Alexander's guess was right, OleDB really did the trick for what I was aiming.  I deinstalled everything on the machine, Oracle clients, ODAC, SSMA, and started over.  With a default Oracle Client (the complete full one, not the instant client) SSMA console connected correctly with the OleDB provider, both standard (ezconnect string) and TNSNames modes.  The OracleClient provider on the other hand never worked.  

    I don't wanna go further to try the ODAC; we're trying to keep it as simply as possible for this home ETL, so having to install an aditionnal component to run it will kind of defeat this goal.  I believe the OleDB provider is enough for the job.  But now I got curious about the performance.  Which provider is faster for extracting data from Oracle?  OleDB or OracleClient?

    If I have time mayb I will experiment a bit more and compare them.

    Thanks for all you help.  This comunity really rocks.

    Wednesday, September 25, 2019 1:22 PM
  • Good, at least you got something working!

    We didn't deliberately test any performance in relation to the driver used, but we did have customers reporting a memory leak when using Oracle's OLE DB provider. I don't remember particular version and circumstances, so it may not be relevant, but keep that in mind. ODBC driver, on the other hand, didn't have the problem in exactly the same situation, so might be another worth getting it setup and ready.

    In general, I'd expect OracleClient (ODP.NET) to be faster, as it's fully managed, so there is no transition/data marshalling from managed to native code.

    It's worth mentioning another thing we noticed with ODP.NET - it treats username case-sensitive. In order to match the behavior of the old native OCI we had to upper-case the user name in the SSMA code, so that sys and SYS are treated the same (both worked with OCI, but lower-case sys didn't work with ODP.NET). Now, if you know exactly what your username is, you can double-quote it explicitly when using standard mode. For example, in GUI just put "SYS" in the username textbox (including double-quotes). Internally SSMA always uses quotes for connection string arguments, the only difference is when it sees explicit double-quotes in the username - it strips them off and preserves the casing. It also has a negative side effect if you want a case-insensitive username (upper-case, in Oracle terms), but your username starts and ends with double-quotes - you need to upper-case it manually and double surrounding double-quotes, as one pair will be stripped by SSMA. :(

    Regards,
    Alex.

    Tuesday, October 8, 2019 5:34 AM