I need to sqoop data from an MS SQL DB which has SSL enabled on it. The DBA has provide me a .cer file. How to use that .cer file in my sqoop command?

[#1]

@Syed Shahzer

This is what you should do you have to create the jks Java key store file as mentioned in the below link

http://community.hortonworks.com/answers/202336/view.html

If you found this answer addressed your question, please take a moment to log in and click the "accept" link on the answer.

That would be a great help to Community users to find the solution quickly for these kinds of errors.

[#2]

When I tried the first line in the mentioned link, it started asking me personal details like :What is your first and last name?

What is the name of your organizational unit?What is the name of your City or Locality?

I felt uncomfortable answering these. It felt like a phishing attempt to me. Can you comment on this?

[#3]

Afterwards I tried the third line in the post from the link you gave.

It went fine. But the sqoop command which I used gave me an exception 'Error executing statement: java.sql.SQLException: I/O Error: GSS Failed: Invalid name provided (Mechanism level: KrbException: Cannot locate default realm)' even though I modified my sqoop command the same way mentioned in that post. can you help?

[#4]

@Geoffrey Shelton Okot Please read my latest comment.

[#5]

@Geoffrey Shelton Okot I am also using' useNTLMv2=true;domain=ms' options in my sqoop command

[#6]

@Syed Shahzer

That's not a click bait quite normal with SSL/TLS certificates. You should have accomplished the steps in the sequence as indicated.

You have a kerberized environment, you should initially have run kinit as the sqoop user before launching the export/import sqoop job!

If you can explain the architecture then it's easier to figure out.

My assumption is the HDP cluster is kerberized and your MS SQL database is not?

SOLUTION

On Linux please specify the environment variable KRB5_CONFIG=<path_of_krb5_conf_file>/krb5.conf with the location of krb5.conf file or pass it as a java system property

-Djava.security.krb5.conf=<path_of_krb5_conf_file>/krb5.conf to locate the krb5.conf correctly.

See installation guideline for Kerberos on windows

If the MS SQL server is not in the same Kerberos domain copy the krb5.conf file renamed to krb.ini and should be placed in the C:\Windows directory see the link provided.

HTH

[#7]

@Geoffrey Shelton Okot

What do you mean exactly when you say MS SQL server is not in the same Kerberos domain?

[#8]

MS SQL is Microsoft SQL server!!

[#9]

I know MS stands for Microsoft here. What I am asking is that if I need to request the DBA to take the conf file from us and put it somewhere in the server

[#10]

@Syed Shahzer

Can you share your sqoop command please mask only important data !

[#11]

@Geoffrey Shelton Okot

sqoop import -Dmapred.job.queue.name=fabpops_q1 --connect "jdbc:jtds:sqlserver://XXXXXXXXXXXX;useNTLMv2=true;domain=ms;databaseName=XXXXXX;username=XXXXXXX;password=XXXXXX;encrypt=true;trustServerCertificate=false;trustStore=/mssql.jks" --null-string '' --null-non-string '' --fields-terminated-by --m "1" --query "SELECT * FROM table_name a where \$CONDITIONS" --target-dir XXXXXX