Maintaining Data Connections for the Server

Matching Data Connections on the Client and Server

For production systems, it is typical to run small jobs on the SAS Data Management Studio host, and to run large jobs on the SAS Data Management Server host. When jobs are executed on the server, they need any data connections that are referenced in the jobs. It is also typical for production systems to have the server and client use the same DBMS-based repository. When the server is started, it will attempt to use the DBMS connection specified in its repository definition.

To support such production systems, you can define DSN connections on the SAS Data Management Server that use the same DSN name and the same connection attributes as the corresponding connections on the client. For example, 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.

The Countries table is visible to a job on the SAS Data Management Studio host because an Oracle ODBC DSN named Oracle11g was added to the Data riser. If that job was deployed to a SAS Data Management Server, an Oracle ODBC DSN named Oracle11g would need to be created on the Data Management Servers riser. Otherwise the job would fail.

Likewise, suppose that a SAS repository specified an Oracle ODBC DSN named Oracle11g as the Data storage method for the repository database, as shown in the next display.

If this repository definition was copied to a SAS Data Management Server, an Oracle ODBC DSN named Oracle11g must be created on the Data Management Servers riser. Otherwise the server would not start because the repository could not be accessed.

The steps for creating DSNs for the server are similar to the steps for creating them for SAS Data Management Studio. Some of the differences and similarities are summarized below. For more information about adding data connections for the server, especially for UNIX systems, see the "Managing Data Connections" chapter in the SAS Data Management

Add a Data Connection on the Server

Perform the following steps to add data connections for SAS Data Management Servers: 

  1. Click the Data Management Servers riser on the SAS Data Management Studio desktop.
  2. Select the server that requires a new data connection.
  3. Enter any credentials that are required to access the server. The Summary tab for that server appears on the right.
  4. Click the Data Connections tab on the right. That tab becomes active, as shown in the next display.

  5. Use the New control to add Domain-Enabled ODBC connections, Custom connections, and SAS Data Set connections for the server.

    The icon to the right of the New control is the Manage ODBC Credentials button. This button displays the Manage ODBC Credentials window. You can use this window to register existing ODBC DSNs that have been defined for the server, using the ODBC Data Source Administrator window. You can also use this window to save user credentials for a registered ODBC connection, if saving credentials to a file is acceptable in your environment.

    To add a new ODBC connection, you display the ODBC Data Source Administrator window in the usual manner for your server and create an ODBC DSN. You can then use the Manage ODBC Credentials window to register the DSN that you created.

Other than the fact that you are starting from the Data Management Servers riser instead of the Data riser, most steps for creating data connections on the server are similar to the steps for the corresponding client connections. See Connection Types for the Client for links to topics that describe how to add each connection type.

Working with a Connection on the Server

Click the Manage ODBC Credentials button to add or update user credentials for a registered ODBC connection for the server. Other updates for ODBC DSN's must be done from the ODBC Data Source Administrator window.

Perform the following steps to edit the properties of Domain-Enabled ODBC connections, Custom connections, and SAS Data Set connections on the server.

  1. Click the Data Management Servers riser on the SAS Data Management Studio desktop.
  2. Select the server with connection that you want to edit.
  3. Enter any credentials that are required to access the server. The Summary tab for that server appears on the right.
  4. Click the Data Connections tab on the right. That tab becomes active.
  5. Right-click an existing connection in the Data Connections tab and select Properties. A properties window displays.
  6. Use the properties window to update the properties of the connection.

Saving User Credentials for a Connection on the Server

If a DSN connection is used in a job, and no credentials have been saved to the connection, then the job will fail to run on the server. To use fix the DSN on the server, you could can do one of the following, depending on the type of connection.

For Test Environments

If it is acceptable to save credentials for a data connection on the server, consider these connection types.

ODBC DSNs. You can specify credentials on several levels. You can save credentials in the ODBC Data Source Administrator window for this connection. You can click the Manage ODBC Credentials button (described in the previous topic) to add or update user credentials for a registered ODBC connection for the server. By default, saved credentials for the server are saved to the etc/dsn folder in the server installation directory.

If it is acceptable to copy saved credential files from the client to the server, see Saving User Credentials for a Connection on the Client.

For Production Environments

In a production environment, it might not be acceptable to save credentials for a data connection on the server. If so, consider these connection types.

Domain-Enabled ODBC Connections. You can create a connection on the server that references an ODBC connection and an appropriate Authentication Server domain. For more information, see Adding Domain Enabled ODBC Connections.

SAS data set connections. No user credentials are required for a connection that is created with the SAS Data Set Connection dialog. The SAS data is accessed directly on disk without mediation by a server.

Fixing an Architecture Mismatch Between an ODBC Driver and an Application

In general, a 32-bit ODBC driver can access a database running in 64-bit mode, and a 64-bit ODBC driver can access a database running in 32-bit mode. Not all ODBC drivers are this versatile, however. In some cases, you might encounter the following scenario with Windows-based systems.

Suppose that SAS Data Management Studio, which is a 32-bit application, is running on a computer with a 64-bit version of Windows. Suppose also that a 64-bit SAS Data Management Server is running on the same computer. When you create an ODBC DSN connection in SAS Data Management Studio, you will specify a 32-bit driver. (The SAS Data Management Studio interface for ODBC connections always calls the 32-bit ODBC Data Source Administrator, even on a computer with a 64-bit version of Windows.) The 32-bit driver will work if you use the connection in SAS Data Management Studio. However, if you deploy a job that uses a 32-bit data connection to the local 64-bit SAS Data Management Server, then the job might fail to run on the server because of an architecture mismatch between the 32-bit connection and the 64-bit server.

The remedy is to define a 64-bit connection on the SAS Data Management Server that uses the same DSN name and the same connection attributes as the 32-bit connection. Then, when you execute the job on the SAS Data Management Server, the DSN name will reference the 64-bit connection, and the job will succeed, if the connection is to data in a database management system. The job itself does not have to be modified. For more information, see Matching Data Connections on the Client and Server.