Storing the database password securely in SQOOP

As of SQOOP 1.4.6 onward it is introduced one option "--password-alias" which will take password alias instead of actual password on command line. The argument value for this option is the alias on the storage associated with the actual password.

To use the above option the underlying hadoop version should provides an API to separate password storage from applications. As of hadoop 2.6.0 hadoop command credential will provide this facility. The passwords are stored with their aliases in a keystore that is password protected. 

Ex: The below are the version tested this functionality.

Hadoop 2.6.0-cdh5.7.0 

Sqoop 1.4.6-cdh5.7.0

Step 1). Run the below command to see the usage of credential API.

$hadoop credential --help

create: to create a new password alias

delete: to delete already created alias

list: to list the available password alias

Step 2): Create a password alias for your database password as below.

Note: For this example using mysql db with username: root and password:cloudera

$ hadoop credential create mydb.password.alias -provider jceks://hdfs/user/cloudera/mydb.password.jceks

Enter password: 
Enter password again: 
mydb.password.alias has been successfully created.

org.apache.hadoop.security.alias.JavaKeyStoreProvider has been updated.

In the above command mydb.password.alias is an alias for our db password(cloudera in this example), and jceks://hdfs/user/cloudera/mydb.password.jceks is the HDFS path(/user/cloudera) where we want to create .jceks file which will be hidden and not a text file so its is secure.

Step 3): To list the above created alias use the below command.

$ hadoop credential list -provider jceks://hdfs/user/cloudera/mydb.password.jceks

Listing aliases for CredentialProvider: jceks://hdfs/user/cloudera/mydb.password.jceks


mydb.password.alias

Step 4): Sqoop command which will use above created alias in place of password in the import command.

$sqoop import \
-Dhadoop.security.credential.provider.path=jceks://hdfs/user/cloudera/mydb.password.jceks \
--connect jdbc:mysql://localhost/testDb \
--table student \
--username root \
--password-alias mydb.password.alias \
--delete-target-dir \
--target-dir /user/cloudera/student \

-m 1

Note: --password-alias is the option to specify the alias value we have created for our database.