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
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.
- Click the OK button to accept the change.