Overview of Data Connections

Connection Types for the Client

In SAS Data Management Studio, you can add connection definitions in the Data Connections folder in the Data riser bar, as shown in the next figure.

The connections in the Data Connections folder are used to access data in jobs, profiles, data explorations, and data collections. They are also used to access DBMS-based SAS repositories. You can add the following types of connections for the client: 

• ODBC connections. You can use the Microsoft Windows ODBC Data Source Administrator dialog to define ODBC Data Source Name (DSN) connections for a wide range of data sources. For instructions, see Adding ODBC Connections. See also Adding Connections for Hadoop.
• Domain Enabled ODBC connections. You can link an ODBC DSN connection to an authentication server domain. The credentials for each user are obtained by the server and are automatically applied when the user accesses the domain-enabled connection. This approach provides more granular control over which credentials are used to access data. For instructions, see Adding Domain-Enabled ODBC Connections.
• SAS Data Set connections. You can connect to a folder that contains one or more SAS data sets. The SAS data is accessed directly on disk without mediation by a server. For instructions, see Adding SAS Data Set Connections.
• Custom connections. You can enter a custom connection string and save it to a connection definition in the Data Connections folder. Custom connections enable you to access data sources that are not otherwise supported in the SAS Data Management Studio interface. For instructions, see Adding Custom SQLite Connections and Adding Custom SAP Connections.

Connection Types for the Server

Like SAS Data Management Studio, SAS Data Management Server uses defined data connections to access source tables, target tables, and SAS repositories. However, the server does not use data connections that have been defined for the client. The server uses its own connections that are registered on the Data Management Servers riser, as shown in the next display.

You can use the New control to add Domain-Enabled ODBC connections, Custom connections, and SAS Data Set connections for the server. You can use the icon to the right of the New control to register ODBC connections that are created on the server with the ODBC Data Source Administrator window. For more information, see Maintaining Data Connections for the Server.

How Data Connections are Used in Different Contexts

Here are some examples of how a connection to the same data source is handled differently in different contexts. Suppose that a job uses an Oracle table named Countries as a data source. The next display shows this table as it might be displayed on the Data riser in SAS Data Management Studio.

To access this table from SAS Data Management Studio, which is a 32-bit application, you could create an ODBC DSN connection that used the SAS Oracle 32-bit Wire Protocol driver to access this table in the Oracle database. You might call this DSN Oracle11g. For information about standard ODBC connections, see Adding ODBC Connections.

If you specify the Oracle11g DSN in a profile, collection, or data exploration, and no credentials have been saved with the connection, then you will be prompted for credentials when you run the profile or use the exploration or collection. This prompting is appropriate because profiles, explorations, and collections can be used for interactive analysis.

However, if you specify the Oracle11g DSN in a data job or a process job, and no credentials have been saved to the connection, then the job will fail. The job will not prompt you to enter the missing credentials. This is appropriate because jobs are often executed in batch, so prompting for credentials would not work in that context. To use this DSN in a job, you would either have to save credentials to the DSN connection in the Data Connections folder on the Data riser, or you could use a domain-enabled ODBC connection, which can be configured to provide the missing credentials in a more controlled way. For more information, see Saving User Credentials for a Connection, or Adding Domain Enabled ODBC Connections.

You can upload a SAS Data Management Studio job to a SAS Data Management Server for execution, as described in Deploying Jobs to a Data Management Server. If you deploy a job that uses a DSN connection, and that DSN is not defined on the SAS Data Management Server, then the job will fail to run on the server because the referenced DSN is not available. The remedy is to define a DSN connection on the SAS Data Management Server that uses the same DSN name and the same connection attributes as the missing connection. For more information, see Matching Data Connections on the Client and Server.