topic Re: SAS ACCESS Connect to EC2 via jump host in SAS Programming

SAS ACCESS Connect to EC2 via jump host

jschoeneberger — Fri, 02 Sep 2022 19:30:15 GMT

I have a SQL database on an EC2 server set up by my company. In the past, this database was in a different location, where i set up a simple ODBC connection through the typicaly ODBC Administrator in Windows, connect through a VPN, and read data from tables directly into SAS. For the new version of the database, they have told me to connect using a jump host. I have successfully made use of the jump host information (including a .pem key file) and the database ODBC information to connect using the mySQL Workbench utility. Now, i want to be able to connect directly through SAS. The ODBC Administrator doesn't seem to be set up for accommodating jump host setups. Is there a pass-through work-around for this? Really, i'm looking for a step-by-step example of how to connect?

I have Base SAS, SAS ACCESS, and SAS CONNECT at my disposal.

Thanks,

Jason

Re: SAS ACCESS Connect to EC2 via jump host

SASKiwi — Fri, 02 Sep 2022 22:50:21 GMT

Is your SAS running on a PC or not? If it is on a PC, then the easiest solution would be to move your SAS installation onto the jump host, which would be a complete new install. Alternatively, if you have access to a SAS server, that could be configured to work directly with the SQL database and not using a jump host.

Re: SAS ACCESS Connect to EC2 via jump host

jschoeneberger — Tue, 06 Sep 2022 13:18:20 GMT

SAS is running on a PC...and i can't envision a scenario where the database/IT folks will work to install SAS on that server just for this one project. Sigh

Re: SAS ACCESS Connect to EC2 via jump host

SASKiwi — Tue, 06 Sep 2022 21:17:53 GMT

The problem is the database connection setup has to be on the same computer where SAS is installed. Why are your IT folks insisting on using a jump host? At the end of the day, the database connection still has to happen from your PC so you can extract database data back to it. I see no benefit to having a jump host in this scenario at all. A more common use case for jump hosts is where you are sharing it with other users but all of the required client software is installed on it.

Re: SAS ACCESS Connect to EC2 via jump host

jschoeneberger — Tue, 06 Sep 2022 21:46:31 GMT

Pre I doubt, the Drupal 7 database was on a remote server that I had to connect to a VPN, the I created a connection to the database using the ODBC administrator tool from Windows.

To connect to the new database, they. Liam the workaround for not using VPN is to use the jump host. I can connect to this database using My SQL Workbench’s ODBC connection and it works fine. But I don’t see the ability to supply the additional SSH credentials in the Windows ODBC tool…thought maybe SAS might have that enabled.

Re: SAS ACCESS Connect to EC2 via jump host

SASKiwi — Tue, 06 Sep 2022 22:25:20 GMT

Avoiding a VPN seems like a minor benefit, unless that type of connection performs poorly. Is My SQL Workbench sitting on the jump host though?

You mention having SAS/CONNECT. Do you use this to connect to a remote SAS server by any chance (that's what its purpose is if you didn't know)?

Re: SAS ACCESS Connect to EC2 via jump host

jschoeneberger — Wed, 07 Sep 2022 13:14:49 GMT

Hi SASKiwi,

MySQL Workbench is on my machine. Previously, I created an ODBC connection and then used SAS CONNECT to grab data (while connected via VPN), like this:

proc sql;

connect to odbc (user=jschoeneberger password=XXXX dsn=ondcp_prod);

create table dfcme_data as …

Now, however, I have the following to work with:

A bastion.key.pem
An EC2 hostname and the ec2-user as the username
An RDS connection hostname
An RDS connection username (jschoeneberger) and password (XXXX)

In MySQL Workbench, I can create a connection that makes use of all of these ingredients and allows me to query the database. However, when using the ODBC Administrator tool component from Windows, I don’t see how to make use of the EC2 connection information and key. Does CONNECT offer the ability to create such a connection?

Jason

Re: SAS ACCESS Connect to EC2 via jump host

Tom — Wed, 07 Sep 2022 17:07:23 GMT

There is no SAS/Connect there. You are just using the SQL statement CONNECT.

Re: SAS ACCESS Connect to EC2 via jump host

jschoeneberger — Wed, 07 Sep 2022 19:43:36 GMT

Right, my fault. That said, i can't seem to find any examples of making a connection in this scenario.

Re: SAS ACCESS Connect to EC2 via jump host

SASKiwi — Wed, 07 Sep 2022 20:16:01 GMT

You are using SAS/ACCESS to ODBC, just to clarify. So you have set up a DSN in the ODBC Administrator on your PC called ondcp_prod? Do you specify a user name and account in the DSN?

It's at this point that I think you should open a SAS Tech Support track as you will get far quicker answers that way.