Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: How to use credentials file for setting database password in a secure way?

  1. #1
    Join Date
    Jan 2016
    Posts
    9

    Default How to use credentials file for setting database password in a secure way?

    I managed to use the a "Get Data from XML --> Set Variables" transformation sequence to store database credentials in an external XML file and set Kettle variables. These wil be used in subsequent transformations for accessing the database(s).

    So far so good, but: each time the credentials are set in the step "Set Variables", the values of the variables are written to the log file:

    Code:
    2016/01/15 14:13:17 - Set Variables.0 - Setting environment variables...
    2016/01/15 14:13:17 - Set Variables.0 - Set variable DATABASE to value [db1]
    2016/01/15 14:13:17 - Set Variables.0 - Set variable PASSWORD to value [secret]
    Ugh! Now this is unacceptable from a security point of view.

    As an alternative, I know that I could store the DB credentials in the transformation steps directly, e.g. in "Table Output" step - thus, I would end up with an encrypted password stored in the ktr file. But this is no viable way, because if credentials changed, I would have to change multiple ktr files. This is not what I want.

    Is there a way to read in the credentials from some place on a server and use it in a Kettle job without exposing them to the log?

    I am using Pentaho PDI 6.0.0.

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Why so complicated?
    Just maintain your settings in a properties file and use Set-Variables as your first entry in a job.
    You even can store passwords in obfuscated form.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Or even put the:
    PROD_DB_PASSWORD=Encrypted 2be98afc86aa7f2e4cb79bd75dd80aace

    into the kettle.properties file, and then you don't need to use "Set Variables"
    In the password field, you simply reference ${PROD_DB_PASSWORD} in the password field of the DB config.

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    I'm old school - try to keep product and application configuration apart ...
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by marabu View Post
    I'm old school - try to keep product and application configuration apart ...
    And then there's also the option of using JNDI configurations...
    Which meets both pieces, but I don't think simple-jndi supports the "Encrypted" values.

  6. #6
    Join Date
    Aug 2011
    Posts
    360

    Default

    Quote Originally Posted by gutlez View Post
    And then there's also the option of using JNDI configurations...
    Which meets both pieces, but I don't think simple-jndi supports the "Encrypted" values.
    We use JNDI (with tomcat on server side). For the client side, we have inplemented a simple jndi converter
    (See source of simple-jndi) and use EncryptedPropertied class of jasypt library to provide encryption features for datasource in simple-jndi.
    So passwords are AES encrypted on client side too.

  7. #7
    Join Date
    Aug 2011
    Posts
    360

    Default

    Quote Originally Posted by Mathias.CH View Post
    We use JNDI (with tomcat on server side). For the client side, we have inplemented a simple jndi converter
    (See source of simple-jndi) and use EncryptedPropertied class of jasypt library to provide encryption features for datasource in simple-jndi.
    So passwords are AES encrypted on client side too.
    And we have modified the Spoo.bat script such that it ask your encryption password before starting to pass it to jndi converter.
    So the key is not stored anywhere on the client machine

  8. #8
    Join Date
    Jan 2016
    Posts
    9

    Default

    Thanks for your ideas!

    I did not know that it is also possible to store the credentials in encrypted form (thanks @marabu!). I decided to use this solution:


    1. Store Password in encrypted form in XML config file
    2. Use "Get data from XML" step to read connection properties from XML config file
    3. "Set Variables" transform the read XML data to internal variables
    4. In any database related transformation, use ${DB_HOST}, ${DB_PASS} etc. variable in the connection setting


    The XML config file would read something like this:

    Code:
    <?xml version="1.0" encoding="UTF-8" ?>
    <connections>
        <connection name="primavera">
            <name>primavera</name>
            <description>Primavera Prod</description>
            <host>databasehost.host.com</host>
            <database>prod_db</database>
            <port>1234</port>
            <user>fred</user>
            <password>Encrypted 345df823824845435354543545452324</password>
        </connection>
        <connection name="asv">
          ... etc ...
        </connection>
    </connections>
    In the log file, Set Variables would leave this trace to the curious reader:

    Code:
    2016/01/15 14:13:17 - Set Variables.0 - Set variable PASSWORD to value [Encrypted 345df823824845435354543545452324]
    which is accepeptable ...
    Last edited by andimeier; 01-18-2016 at 06:04 AM.

  9. #9
    Join Date
    Aug 2011
    Posts
    360

    Default

    But then people can still use this encrypted password in pentaho, since this is kettle obsuscation, you can use it encrypted....
    If you dont want anything to be logged, use the setVariable function in a javascript step, it wont log anything

  10. #10
    Join Date
    Jan 2016
    Posts
    9

    Default

    @Mathias.CH: Brilliant!

    Thank you for this tip. I employed it in my solution in thread http://forums.pentaho.com/showthread...222#post426222.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.