Office, Open Source

Forgot Password [Pentaho Data Integration a.k.a kettle]

It’s been a long time since i wrote something about from my work.  You may say about the orangescape cloud entry,  that was more of a obsession.  ok this was one of the classic scenario which many may face.  Passwords.  For security purposes we store passwords in encrypted formats.  When using some other products the encryption and storage are out of our hands.

We Use Kettle for ETL purposes in our work.  and we connect to variety of databases, each with their own set of id and passwords (some times more than one, with different set of permissions).

one of the password policies is that the password has to be changed every 30 days or so.  Now after every time the password changes we have to go to kettle, and change the password of the corresponding connection.  It so happened, that we had missed to change the connection password in one of the repositories, and when the required Job ran it threw an error saying Invalid Id or Password, and nobody remembered the password.  We can request to reset the password, but that is a laborious process and the higher ups will not be happy to approve the request without creating some drama.  So i set about to find a way to find the password.

Note:  I am detailing this so that if anyone else is in such a situation they can use this to find the password.  Please don’t use this to steal passwords.

you will require access to the repository database.  Since kettle stores the database connection details in the database table.  query the table r_database to get the encrypted password.

select Name, Host_Name, Database_Name, Port, UserName, replace(password, ‘Encrypted ‘,”) as password from r_database where Name = ‘connection name‘;

copy the value of the password column. and pass it as a parameter to the function this will give you the password. This package has changed after 2.5.0 it seems.  it has changed to org.pentaho.di.core.encryption.Encr.decryptPassword(). I got this function only when searching the internet.  They i had searched for the corresponding function inside kettle.jar of 2.5.0.

i have done a sample transformation which you can use if you want to.  V2.5.0 V3.2.0