how can i provide password to SQOOP through OOZIE to connect to MS-SQL?

Question

I'm exporting information from HDFS into MS-SQL using SQOOP. I'm running SQOOP through OOZIE. Right now I've hard-coded the uid, pwd for the jdbc connection in the OOZIE workflow. Once I switch to prod I won't be able to do this. What is the best way to pass authentication information in a situation like this?

<sqoop xmlns="uri:oozie:sqoop-action:0.2">
            <job-tracker>${jobTracker}</job-tracker>
            <name-node>${nameNode}</name-node>
            <arg>export</arg>
            <arg>--connect</arg>
            <arg>jdbc:sqlserver://$sqlServerIP:1433</arg>
            <arg>--table</arg>
            <arg>tableName</arg>
            <arg>--export-dir</arg>
            <arg>/user/sqoop/file</arg>
            <arg>--username</arg>
            <arg>me</arg>
            <arg>--password</arg>
            <arg>password</arg>
</sqoop>

I could pass them in as parameters like $userName, $password. But the actual uid/pwd would still show in the oozie web console.

UPDATE

I've tried two ways (as suggested bellow) to do this...In VIM I created pwd to just have the password (no white spaces or anything else). Called this pwd.

1)I tried using the file system. However I got an IOException saying that the file doesn't exist. After looking through the code, It looks like sqoop uses passed conf to access the fs. So I'm assuming when ran through oozie it will have only access to HDFS.

2)I loaded the password file into a random location on hdfs. /users/my-name/pwd (pwd is the file). Now it can access the file (since I don't get an IOException). However it fails to connect to SQLServer. I'm not sure what I need to do to get it to work?

UPDATE 2 I was creating the password file like so: echo "pwd" > my.password This adds an EOL to the file my.password I changed to echo -n "pwd" > my.password and now it works.

Link

Just wanted to note that (at least on CentOS 7) echo -n "pw_d" > my.password omits the _ symbol if it exists, writing only pwd to the file. So i.e. vi can be a better alternative – Douce

Answer 1

I think that you can take advantage of specifying --password-file argument, so that Oozie will never see the password. Take a look into the Sqoop User Guide for more details.

Forenoon

thank you for this. Looking at the code, it looks like I can only put the password file on hdfs when running through oozie. In the doc they claim that it could be either on hdfs or the hard-drive. Do you know how I can force it to go to the HD? – Link

Loading the file from local drive is indeed feasible, but as the oozie will execute Sqoop on any random machine on your Hadoop cluster, using this ability from Oozie is kind of useless. I would recommend to upload the password file on HDFS and change the permissions so that only you can read it. – Forenoon