Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

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

Solved Go to solution

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

New Contributor

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

Accepted Solutions

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

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.

2 REPLIES 2

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

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.

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

Mentor

@Ali Erdem

Any updates on this thread?