Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36

    PHP ODBC Hadoop Cloudera Impala not accepting user and pw


    I'm trying to connect to Hadoop via ODBC but I get so far and when trying to pass the user name and password through the connection string I get an error
    Warning: odbc_exec(): SQL error: [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AuthorizationException: User 'server_name$@domain' does not have privileges to execute 'SELECT' on: database.table
    I've replaced the some of the contents above like server_name, domain, database.table for privacy reasons. I'm not quite sure about what I'm doing wrong here. Here's a snippet of what my code looks like when trying to connect to the ODBC connection (which I've setup and tested successfully).
    PHP Code:
    $conn odbc_connect("DSN=Sample Cloudera Impala DSN_32;host=host.domain.com;port=21050;Database=database""user""password");
    if( 
    $conn === false ) {die( print_rodbc_errormsg(), true));}
      
      
    $DispQuery "select shipto from database.table limit 1;";
      
    $DispResult odbc_exec($conn,$DispQuery);
        if( 
    $DispResult === false) {die( print_rodbc_errormsg(), true) );}
        while( 
    $Row odbc_fetch_array$DispResultODBC_FETCH_ASSOC) )
        {
            
    $RowShipto $Row["shipto"];
        }
        
      echo 
    $RowShipto;
      
      
    odbc_close($conn);
      exit; 
    Again I replaced some of the contents in the php above. I don't really have a username of user or a password of password and my host is not host.domain.com. I can't figure out why I'm passing a username and it's not using it. It's using something like server_name$@domain instead. Am I doing something wrong in my code above?
    Thanks in advance!
  2. #2
  3. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,819
    Rep Power
    9646
    Providing a DSN in the connection string may be overriding the rest of the information - ODBC will connect using the "Sample Cloudera Impala DSN_32" information, not the username or password.

    Try
    PHP Code:
    "Driver=Cloudera ODBC Driver for Impala;host=host.domain.com;port=21050;Database=database" 
    Or look up what that sample DSN uses and copy the relevant parts (particularly the Driver).
  4. #3
  5. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36
    I've replaced the DSN with the driver code you posted and now I get the following error
    [Cloudera][ThriftExtension] (5) Error occurred while contacting server: No more data to read.. This could be because you are trying to establish a non-SSL connection to a SSL-enabled server.
  6. #4
  7. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,819
    Rep Power
    9646
    And... have you tried looking into that? Does your connection need to be over SSL? Have you researched what it might take to make that happen?
  8. #5
  9. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36
    I've looked around, not sure what to research to be honest. My ODBC connection on the windows box has SSL already set up and like I said it connects with no problems. I figured if it had SSL already set up then I shouldn't have to redefine it but never assume so I googled. I can hardly find anything about Hadoop, PHP and ODBC connections much less anything specific to SSL, is this something you have experience in? Why isn't it using the SSL that's already set up on the system DSN in the ODBC Data Source Administrator?
  10. #6
  11. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,819
    Rep Power
    9646
    Because you aren't using that DSN anymore. At least according to what you said.

    Here is the documentation for the driver. Either look at how the "Sample Cloudera Impala DSN_32" is configured and copy the settings over into your current DSN string, or look through the documentation for how you can set SSL in a connection string (which I know is in there because I just read it).
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36
    Huge thank you for providing that document but I think I need a little more hand holding/direction. I see the SSL section and I think this is starting to make sense. If I'm reading it correctly, I need to do the following:
    SSL=1 (because SSL is required)
    but I also need to (because my ODBC connection does these things)
    - allow self signed certifications
    - provide a trusted certification

    So, according to the documentation I would then need to add the following

    PHP Code:
    AllowSelfSignedServerCert=1;
    TrustedCerts=C:\Program Files (x86)\Cloudera ODBC Driver for Impala\lib\cacerts.pem 
    Am I following this correctly so far? I'll continue in case I am.

    When I add the line
    PHP Code:
    AllowSelfSignedServerCert=1
    I now get the following error (am I doing something wrong? Format perhaps?)
    Warning: odbc_connect(): SQL error: [Cloudera][ImpalaODBC] (100) Error from the Impala Thrift API: No more data to read., SQL state 08S01 in SQLConnect in F:\xampp\WebPages\Hadoop\index.php on line 2
    [Cloudera][ImpalaODBC] (100) Error from the Impala Thrift API: No more data to read.
    Here's what my final PHP looks like
    PHP Code:
    $conn odbc_connect("DRIVER={Cloudera ODBC Driver for Impala};Host=server.domain.com;Port=21050;Database=database;SSL=1;AllowSelfSignedServerCert=1;TrustedCerts=C:\Program Files (x86)\Cloudera ODBC Driver for Impala\lib\cacerts.pem;""user""password");
    if( 
    $conn === false ) {die( print_rodbc_errormsg(), true));} 
    Last edited by DKY; December 29th, 2017 at 08:23 PM.
  14. #8
  15. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,819
    Rep Power
    9646
    Let's try the other direction.

    That old DSN you stopped using. What is its configuration? It was at least able to connect, which is apparently more than your current code can do right now.
  16. #9
  17. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36
    Well, this code in the first post seemed to be connecting but I can't get it to accept the user and password that I'm passing through.
    PHP Code:
    $conn odbc_connect("DSN=Sample Cloudera Impala DSN_32;host=host.domain.com;port=21050;Database=database""user""password"); 
    if( 
    $conn === false ) {die( print_rodbc_errormsg(), true));} 
       
      
    $DispQuery "select shipto from database.table limit 1;"
      
    $DispResult odbc_exec($conn,$DispQuery); 
        if( 
    $DispResult === false) {die( print_rodbc_errormsg(), true) );} 
        while( 
    $Row odbc_fetch_array$DispResultODBC_FETCH_ASSOC) ) 
        { 
            
    $RowShipto $Row["shipto"]; 
        } 
         
      echo 
    $RowShipto
       
      
    odbc_close($conn); 
      exit; 
    but when it gets to the line that runs the query
    PHP Code:
    $DispResult odbc_exec($conn,$DispQuery); 
    that's when I get this error
    Warning: odbc_exec(): SQL error: [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AuthorizationException: User 'servername$@domain.COM' does not have privileges to execute 'SELECT' on: database.table , SQL state S1000 in SQLExecDirect in F:\xampp\WebPages\Hadoop\index.php on line 5
    [Cloudera][ImpalaODBC] (110) Error while executing a query in Impala: [HY000] : AuthorizationException: User 'servername$@domain.COM' does not have privileges to execute 'SELECT' on: database.table
    I've searched all over and everything I see says that this is the format for passing a user and password for odbc_connect(). I don't get it...
    PHP Code:
    odbc_connect "mydatasource""dbuser""dbpassword" 
  18. #10
  19. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,819
    Rep Power
    9646
    ...okay, so "DSN" is a bit ambiguous.

    This:
    Code:
    DSN=Sample Cloudera Impala DSN_32
    That corresponds to a configured connection string on your computer. What is its configuration?
  20. #11
  21. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36
    So, upload screenshots?
    note: in the first screenshot (main.jpg) I replaced the host and the realm for security purposes.

    main.jpg

    ssl.jpg

    Advanced.jpg

    test.jpg
  22. #12
  23. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,819
    Rep Power
    9646
    Is there anything in there that tells you the resulting connection string?

    To test that everything else works, switch the authentication to LDAP and enter your username and password. Then see if
    PHP Code:
    $conn odbc_connect("DSN=Sample Cloudera Impala DSN_32"""""); 
    lets you connect and run queries...
  24. #13
  25. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36
    How do I do LDAP? Is that the same as "No Authentication"?

    I have found away to create connection string, here's what it creates...

    [ODBC]
    DRIVER=Cloudera ODBC Driver for Impala
    UID=
    UseSystemTrustStore=0
    UseSQLUnicodeTypes=0
    UseSASL=1
    UseOnlySSPI_DSN-less=0
    UseOnlySSPI=0
    UseNativeQuery=0
    UseKeytab=0
    TSaslTransportBufSize=1000
    TrustedCerts={C:\Program Files (x86)\Cloudera ODBC Driver for Impala\lib\cacerts.pem}
    StringColumnLength=32767
    SSL=1
    SocketTimeout=30
    ServicePrincipalCanonicalization=1
    Schema=defailt
    RowsFetchedPerBlock=10000
    Port=21050
    LCaseSspKeyName=
    KrbServiceName=impala
    KrbRealm=DOMAIN.COM
    KrbFQDN=server.domain.com
    Host=server.domain.com
    EnableSimulatedTransactions=0
    DESCRIPTION=Data Science Environment
    DelegationUID=
    DelegateKrbCreds=0
    CurrentSchemaRestrictedMetadata=0
    CheckCertRevocation=1
    AutoReconnect=1
    AuthMech=1
    AsyncExecPollInterval=10
    AllowSelfSignedServerCert=1
    AllowHostNameCNMismatch=0
  26. #14
  27. Impoverished Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,819
    Rep Power
    9646
    No, LDAP authentication is not the same as no authentication.

    After setting up LDAP authentication and verifying that the DSN= connection string works, next step would be to copy all those options from the INI into a connection string and verify that works.
    PHP Code:
    "DRIVER=Cloudera ODBC Driver for Impala;UID=;UseSystemTrustStore=0;..." 
    (though you can put the username and password as additional arguments)

    If it does then you remove what looks like default values until it stops working or you're satisfied that the connection string isn't too unwieldy.
  28. #15
  29. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2003
    Posts
    1,019
    Rep Power
    36
    I don't have an LDAP option when I look in the authentication section of the ODBC connection manager. Here's what I've got and the only one that works is the Kerberos.

    Authentication.jpg
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo