BookmarkSubscribeRSS Feed

SAS Viya 3.4: Creating a Connection to a Database using CONOPTS Option in the User Interface

Started ‎11-05-2018 by
Modified ‎11-05-2018 by
Views 6,033

In the SAS Viya 3.4 user interface you can create connections to databases in SAS Data Explorer, SAS Environment Manager, or in the Data selection dialogs within specific applications (e.g., SAS Visual Analytics, SAS Data Studio, etc.).  Note: You must have the appropriate SAS Data Connector licensed and installed in order to setup a database connection in SAS Viya 3.4.  However, there may be connection options that are not listed in the documentation for a database type. This is where the conopts parameter is useful since it allows you to pass connection options directly to the database.  The conopts parameter is specified in the user interface by using an Advanced parameter.

 

There have been several questions on the Hub and ListServs about how to connect to a PostgreSQL database that uses SSL in SAS Viya 3.4 since SSL is not a connection option that is listed in the SAS Data Connector documentation for PostgreSQL connections.  It was these questions that prompted me to write this blog to show how this can be accomplished by using the conopts parameter to connect to a PostgreSQL database that uses SSL.  

 

To specify an option using the conopts parameter, first I need to know what the specific connection option parameter name and value to use is for my database type.  In my case, I want to connect to a PostgreSQL database using SSL, so I need to look up what is the parameter name and value(s) used by PostgreSQL for this option.  By looking at the PostgreSQL documentation for database connectionsParameter Key Words section, I find that one of the parameters is called sslmode.  Here is a listing of the possible settings for this parameter:

 

 (click to enlarge image)(click to enlarge image)

 

 

To connect to my desired PostgreSQL database, I need to set the sslmode parameter to require.  

 

In the SAS Viya 3.4 user interface on the Connection Settings dialog, I enter the database connection information for my PostgreSQL database in the Settings section.  Note: Don't forget to check the option for Persist this connection beyond the current session.

 

2_ConnectionSettings.png

 

 

In the Advanced section, I first select the Authentication Domain for my connection.  Next, I enter the following in the Additional Parameters section to add my sslmode=require setting using the conopts parameter. 

 

3_ConnectionSettingsAdvanced.png

 

 

Now, I can Test and Save my connection to a PostgreSQL database that uses SSL.  After the connection is saved, I may want to consider authorizing other users and/or groups to use this connection. The connection is now ready for use in SAS Viya 3.4 and can be used to create reports, data plans, etc.

 

4_SavedSSLConnection.png

 

 

This post just reviewed one use of the conopts property.  You can use this property to pass other connection options to your database if needed.  For more information on Creating Database Connections in SAS Viya 3.4, you can refer to the documentation.    

Version history
Last update:
‎11-05-2018 02:00 PM
Updated by:
Contributors

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

SAS AI and Machine Learning Courses

The rapid growth of AI technologies is driving an AI skills gap and demand for AI talent. Ready to grow your AI literacy? SAS offers free ways to get started for beginners, business leaders, and analytics professionals of all skill levels. Your future self will thank you.

Get started

Article Tags