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 5,091

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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags