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
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.
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
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.
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)?
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:
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
There is no SAS/Connect there. You are just using the SQL statement CONNECT.
Right, my fault. That said, i can't seem to find any examples of making a connection in this scenario.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: