Amadeus cookies policy - you'll see this message only once.

Amadeus use cookies on this website. They help us to know a little bit about you and how you use our website, which improves the browsing experience and marketing - both for you and for others. They are stored locally on your computer or mobile device. To accept cookies, continue browsing as normal. Or, go to the privacy policy for more information.

Accept and close

Prevent a SAS Session from Connecting to a SQL Database Until Required

If your SAS environment regularly interacts with SQL databases via ODBC/OLE DB pre-assigned libraries, a common complaint may arise from your SQL DBA team.  By default, when a SAS user connects to a SAS session (such as Enterprise Guide), an attempt will be made to connect that user to each ODBC/OLE DB pre-assigned library.

This behaviour can have the following impact:

SAS

  • SAS sessions can take longer to connect if there are many ODBC/OLE DB pre-assigned libraries to connect to.
  • Additional entries within SAS logs, potentially producing unhelpful errors, if a user does not have valid credentials to connect to the SQL databases associated with those libraries.

SQL

  • Unnecessary SQL database connections made.
  • Unhelpful warnings/errors detailing SAS users' failed database connection attempts, if a user does not have valid credentials to connect to a SQL database associated with a pre-assigned library.
  • Excess noise generated within DBA teams monitoring processes and logs, detailing repeated successful/failed database connection attempts for each SAS user who starts a SAS session.

There is an option which can be applied to each SAS library, which will prevent a database connection attempt being made, until there is a need for a connection to that database to be established. 

The following steps detail setting this option:

  • Open SAS Management Console and log in as an Administrator
  • Within the left-hand pane, click on the Plug-ins tab
  • Expand Data Library Manager
  • Expand Libraries
  • Right click on the SQL Database library and click Properties

Prevent a SAS session image1

The Properties windows will now appear...

  • Select the Options tab and select the Advanced Options button

The Advanced Properties window will now appear…

  • Click on the Connection tab
  • Locate the option Whether to defer a connection until needed and change the drop-down option to Yes.

Prevent a SAS session image2

  • Click the OK button to accept the change.

Posted in SAS Administration; Tagged SAS SQL, SAS Administration, Proc SQL, Database Connection Management; Posted August, 2017 by Amadeus Technical Team