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

[#1]

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 connections – Parameter 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)

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.

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. 

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.

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.