Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Explorer

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

Thanks.

Versions:

Sqoop1.4.7

NiFi1.8.0

1 ACCEPTED SOLUTION

avatar
Master Mentor

@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.

View solution in original post

2 REPLIES 2

avatar
Master Mentor

@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.

avatar
Master Mentor

@Ali Erdem

Any updates on this thread?