CODEWITHSUNDEEP
sqlsshodbcsas
SumitGupta
SumitGupta

Reputation: 105

Database connection in SAS using ssh tunneling

I want to know if there is a way to connect to a MySQL database in SAS using SHH tunneling using .ppk file.

I am able to connect to this MySQL database using SQLyog by providing the.ppk file.

Upvotes: 1

Views: 1353

Answers (1)

C Paxton
C Paxton

Reputation: 11

I achieved this via plink (from the PuTTy package). In my example, I connect to a remote server, but it would also work for localhost w/o all the forwarding.

First, set up your ODBC (32 or 64 bit) data source as you would normally - say you named it DB_SOURCE, with the appropriate driver, listing 127.0.0.1 or localhost as the server, and a local port number (LOCAL_PORT) . Second, use the x command (after adjusting options):

options noxwait NOXSYNC;
data _null_; /* set up tunnel to remote db */
    x '"C:\Program Files\PuTTY\plink.exe" REMOTE_IP -P 22 -T -l USER_NAME -i c:\users\LOCAL_USER_NAME\.ssh\PRIVATE_KEY_FILE_NAME.ppk -L 127.0.0.1:LOCAL_PORT:REMOTE_IP:REMOTE_PORT';
run;

Then, do your normal work. For example:

libname LIBNAME ODBC datasrc=DB_SOURCE qualifier=DB_TO_USE;

Key file Seemed best practice as leaving a password riding around in a plain text SAS program struck me as not good.

Upvotes: 1

Related Questions