Is it possible to connect Sql Server via Sqoop or Nifi with the AD user without a password?

[#1]

If it is possible, Could you please share steps that how I can do it.

Thanks.

Versions:

Sqoop1.4.7

NiFi1.8.0

[#2 · Accepted solution]

@Ali Erdem

YES it's possible to connect and run a sqoop job against an SQL server without a password. Hadoop credential provider API the CredentialProvider API in Hadoop allows for the separation of applications and how they store their required passwords/secrets. With Sqoop 1.4.5 or higher, the credential API keystore is supported by Sqoop.

The AD user ONLY needs to include the -Dhadoop.security.crendential.provider.path in the sqoop command. Here are the steps, The API expects the password .jceks file to be in HDFS and accessible to that user preferably in his/her home directory

Assumption password for Production sqlserver it's good to standardize eg sql_prod,sql_dev or ora_prod,ora_dev etc

$ hadoop credential create sql_prod.password -provider jceks://hdfs/user/erdem/sql_prod.password.jceks

The above command will prompt for the target database password see output below

Enter password:  {the_target_database_password}
Enter password again:  {the_target_database_password}ora_prod.password
has been successfully created.org.apache.hadoop.security.alias.JavaKeyStoreProvider
has been updated.

Now the password should be in your home directory,the file should be readable

$ hdfs dfs -ls /user/erdem
Found 1 items
-rwx------ 3 erdem erdem 502 2019-01-29 11:08 /user/erdem/sql_prod.password.jceks

Now the user erdem can run a sqoop job

sqoop import
-Dhadoop.security.crendential.provider.path jceks//hdfs/user/erdem/sql_prod.password.jceks
-Doraoop.timestamp.string=false -Dmapreduce.job.user.classpath.first=true \
--verbose --connect jdbc:sqlserver://sqlserver-name \
--username erdem \
--password alias ora_prod.password \
--driver   com.microsoft.sqlserver.jdbc.SQLServerDriver \
--table test \
--target-dir "{some_dir}" \
--split-by NOOBJETRISQUECONTRAT --direct --as-parquetfile

In the above, I modified the output from my oracle sqoop output especially for the driver part. But it should work without issue you will realise the user erdem didn't key in a password on the CLI a security loophole.

There you go revert if you need more help.

[#3]

@Ali Erdem

Any updates on this thread?