SAS Workbench Connectivity to ODBC Data Sources

[#1]

There is a lot of excitement around SAS Workbench this year. One topic that can impact a customer’s use is how to get data in and out of SAS Workbench. There is information regarding what external data sources are supported, but I found that connecting to ODBC based sources like Microsoft SQL Server, Azure Synapse, Amazon Redshift, or Postgres can be a little tricky.

The challenge is when the libname statement uses a DSN. The use of a DSN means that the user must reference an odbc.ini file that contains such configurations. An administrator typically maintains this file and there is not a central shared data location for all users, so for Workbench, each user must also manage his/her own odbc.ini file. Even a dsnless connection still requires an entry in the ODBC.ini file.

The workaround is to:

1. Use the options set to name a location where the odbc.ini can reside and be edited appropriately. This can be done at the beginning of your program or placed in the autexec.sas file so that it runs with each new session. Below, I named the location in the autexec.sas file.
2. Obtain a copy of the provided template of the odbc.ini file and store in the new location. This is the part that may not be obvious of where to get the odbc.ini file that comes out of the box with the deployment of SAS Workbench. Use SAS Data Step to get a copy the odbc.ini file and place in the new location. Modify the odbc.ini file with appropriate data source information. See the code below and adjust accordingly with your deployment.              
3. Run the options statement to set the location of the odbc.ini file. Use a put statement to see that the change in location has been implemented. See below for an example.
4. Define the libname statement. The example below is for a connection to a Synapse SQL Pool. Run the libname statement.
5. Compare with data shown in the Synapse UI.

After completing the steps above a connection to an ODBC based external data source for data access. This method gives users more choices for data sources for their analytic work. Using SAS access technology not only gives access to external sources but can also increase performance and productivity by pushing work to be done inside the database. Implicit and Explicit SQL pass-through queries are good examples.

[Quentin]

Great tip.  I made also try this on my SAS 9 server where, as you say, the admins control the odbc.ini.  I hadn't realized that as a user it is that easy to point to a personal odbc.ini instead.  

[SASKiwi]

Nice to know. It is also worth pointing out that for some database connections you can avoid DSNs entirely and define everything in your connection strings like so:

libname sqllegno sqlsvr noprompt='Driver=SQLServer_Legacy;Address=machine2.reg.company.com,1433;Database=users;
       UID=myuser;PWD=mypass;' schema=myschema;

This works well for SQL Server in my experience.

[Quentin]

@SASKiwi I think DSN-less  connections to SQL server are straight forward from SAS running on a Windows server, but require more configuration from SAS running on linux (Workbench runs on linux). I never got it working from linux, as our admins wanted to retain control over odbc connections. But it's possible it could work from workbench.  Here's a link to a thread with a working example of DSN-less from linux: https://communities.sas.com/t5/SAS-Data-Management/Example-of-DSN-less-SQL-Server-connection-from-Li...

[SASKiwi]

@Quentin - Thanks for pointing out the differences. I work in a Windows-only world currently so I'm not so familiar with linux-based data connections. Interesting to see that some file configuration is still required there.