Setting up access with DSNs

This topic includes the following sections:

 

Using the ODBC Data Source Administrator, you can create and manage data source names (DSNs). Each DSN has a name and contains information needed to access a database — for example, the name of the connect file and user and password information. Once you have created a DSN for a database, users can access the database from an ODBC-enabled application by selecting the DSN. DSNs free end users from having to enter the location of the data files and other connection information, and they enable you to set connection options.

When you install xfODBC, the installation adds a sample DSN named xfODBC. Except for some default settings, this DSN specifies one thing: it specifies that the Vortex driver is “Genesis,” which means that the DSN is for a local database. Users can access any local ODBC-enabled Synergy database with this DSN, but they will be prompted for a connect file, username, and password because these aren’t specified in the DSN. You can modify this DSN, and you can create your own.

Note

On Windows, you must have write privileges to the Windows registry to create or modify a DSN. If you don’t, you’ll get a “Writing config data failed” error.

User DSNs, system DSNs, and file DSNs

There are three types of DSN: user, system, and file. Typically, ODBC-enabled applications can use all of these.

We don’t recommend sharable file DSNs because you have to manually edit the Windows registry to create them.

Adding a user or system DSN

1. Open the ODBC Data Source Administrator, which is available from Administrative Tools in Windows Control Panel. Be sure to use the correct version of this utility:

64-bit Windows machines usually have a mix of 32- and 64-bit applications, especially when using Visual Studio to develop 64-bit applications. For an ODBC connection that could involve both 32- and 64-bit applications, we recommend that you create both a 32-bit DSN and a 64-bit DSN that are identical in all aspects (including name).

Once you have opened the ODBC Data Source Administrator on 64-bit Windows, you can determine which version is running by looking at the title bar, which includes either “(32-bit)” or “(64-bit)”:

2. On the User DSN or System DSN tab of the ODBC Data Source Administrator window, click the Add button. The Create New Data Source window opens and lists the currently available ODBC drivers.
3. In the Create New Data Source window, select xfODBC.
4. Click Finish. The xfODBC Setup window displays (see figure 1).
5. In the xfODBC Setup window, enter the data source information in the following fields.      

1. Adding a DSN.

Adding a DSN

Data source name

Enter a descriptive name to identify the DSN. Some special characters are not supported. If you use an invalid character, the message “Writing config data failed” will display when you OK the dialog.

Description

Enter a description of the DSN. This is optional.

Appended to connect string

Enter a string you want sent via SQL OpenNet to the server. This is optional and is typically used to define environment variables on the server (under the direction of Synergy/DE Developer Support). To do this, use the following syntax (note the initial comma):

, ENV_VAR=env_spec[, …]

where ENV_VAR is an environment variable and env_spec is its definition. You can specify multiple environment variables by separating them with commas. Note the following:

Vortex driver

Select Net for a client/server configuration. The Net option instructs xfODBC to use SQL OpenNet. (The SQL OpenNet server must be running on the host specified in the Host field.) Select Genesis for a stand-alone configuration. The Genesis option instructs xfODBC to connect to the database directly without using SQL OpenNet.

Host

If you selected Net for the “Vortex driver” field, enter the host name or IP address for the machine on which the database resides. Host names can be up to 64 characters long. If you enter an IP address, it can be in IPv4 format or IPv6 format, depending on what the SQL OpenNet service is set to recognize. See vtxnetd and vtxnet2 programs.

Note the following:

host_name([domain\]uid/pwd)

where uid and pwd are the username and password for an account on the host machine or, if domain is specified, an account on a domain (Windows only).

Port

If the data files are on a remote machine, enter the port address for that machine. See Port settings for more information.

User name

Enter the username exactly as it is stored in the system catalog. This field is case sensitive. You can leave this field blank if you would like the end user to be prompted for a username when accessing a Synergy database.

By default, the username and password are encrypted as they are sent across the network via SQL OpenNet. For information on changing encryption settings for this, see Setting SQL OpenNet client options in net.ini.

Password

Enter the password for the user exactly as it is stored in the system catalog. This field is case sensitive. Leave this field blank if you would like the end user to be prompted for a password when accessing a Synergy database.

Connect file

Enter the connect file name. Connect files must be located in the directory specified by the GENESIS_HOME environment variable. In a client/server configuration, the connect file must be in the GENESIS_HOME directory on the server. You can leave this field blank if you would like users to be prompted to supply the connect file (which might be useful for testing).

Env. variables

Use this to define environment variables used by the xfODBC driver on the client. For services that use the xfODBC driver, such as web servers, this is the only place these environment variables can be set (unless you set them in the system environment and reboot the server). For example, for a web server in a client/server configuration, you can use this field to set any of the VORTEX_API_ environment variables, VORTEX_ODBC_CHAR, or VORTEX_ODBC_TIME. For a web server with a local database, you can use this field to set GENESIS_HOME as well.

To set environment variables in this field, use the following syntax:

ENV_VAR=env_spec[, …]

where ENV_VAR is an environment variable to be set on the client, and env_spec is the definition of the environment variable. You can specify multiple environment variables by separating them with commas.

Statements

The number of logical cursors to allocate for the connection. (xfODBC assigns a logical cursor to each SQL statement.) If this option is set to a value that is greater than the DB cursors setting, xfODBC is able to cache cursors by mapping multiple logical cursors to a single database cursor. Because logical cursors require less memory than database cursors, this improves performance. For optimal performance, set this option to the maximum number of SQL statements that will be open concurrently. Valid values are 4 through 1024. The default is 256.

DB cursors

The number of database cursors to allocate for the connection. Valid values are 4 through 256. The default is 64. This should be set to a value that is less than Statements.

Columns

The maximum number of columns that can be returned for a query. Enter the number of columns of the largest table in the database. Valid values are 4 through 1024. The default is 256.

Fetch buffer size

The number of bytes to allocate for the prefetch buffer. The fetch buffer size is the size of a data transfer block. Valid values are 0 (which disables the prefetch buffer) and 1024 through 99999999. The default setting is 4096.

Total

The amount of disk space in pages (4,096-byte blocks) to allocate for temporary sort tables (work files) for each open cursor. The default is 10000. Valid values are 1000 through 99999999. On 32-bit systems, however, the limit may be 2 GB due to file system limitations.

Important

The setting for Total must be greater than or equal to the setting for “In memory.” The Total and “In memory” settings are used to generate a “SET OPTION SORTPAGES totalpages memorypages” command when the DSN is used to connect to a database. An application uses the sum of the memory specified for all concurrently-opened cursors, and on Windows, vtxnetd uses the sum of memory allocated for all open cursors for every connected application. See Notes on SORTPAGES for more information.

In memory

The amount of internal memory in pages (4,096-byte blocks) to allocate for temporary sort tables (work files) for each open cursor. Valid values are 1000 through 999999. The default is 1000. This setting affects the performance of join queries and queries with ORDER BY clauses.

Max number of rows

The maximum number of rows that can be returned when a statement is optimized with multimerge. Multimerge optimizes SELECT statements that have one or more OR clauses by evaluating each side of each OR clause as a separate SELECT statement and then combining the results (in a multimerge operation). Note that this works only when keys are available to optimize each side of each OR clause. This setting must be a positive numeric value from 100 to 500000.

By default, multimerge is enabled (the default setting is 10000) because some applications automatically generate the kind of statements that a multimerge optimizes. For example, if you use Microsoft Access to issue a query that selects all the columns in a table, Access generates a SELECT statement with a series of OR clauses that repeatedly specify key segments.

Note the following:

SSL

Specifies whether the SQL OpenNet client requires SSL encryption (data packet encryption) for the DSN. If this is set to Yes, the SQL OpenNet service must be set to use SSL encryption when the DSN is used. Additionally, when this is set to Yes the "TLS level" and "Certificate file" settings on this dialog are used for connections that use the DSN (and SSL settings in net.ini are ignored).

If this option is set to No, the "TLS level" and "Certificate file" settings on this dialog are ignored, and the client will require SSL encryption only if the ssl option in net.ini is set. (Note that SSL encryption is used if the SQL OpenNet service is set to use it, regardless of this setting or the ssl setting in net.ini.)

For information on installing and configuring SSL for SQL OpenNet, see Using data packet encryption for SQL OpenNet. For information on net.ini settings, see Setting SQL OpenNet client options in net.ini.

TLS level

Specifies which TLS level will be required for SQL OpenNet connections. TLS 1.1 and 1.2 are supported, but server settings determine which levels are available (see Encrypting data packets (-e)). Note that security best practices require TLS 1.2.

This setting is used only if the SSL option on this dialog is set to Yes.

Certificate file

Specifies the name and location of a file on the client that is used to validate the server certificate for SSL (the certificate used for the SQL OpenNet service). If the server certificate is from a trusted certificate authority (CA), this setting should specify a certificate trust store file on the client (see Requesting a certificate from a certificate authority). If the certificate for the server is self-signed, this setting should specify a root certificate on the client (see Creating a local certificate authority).

This setting is used only if the SSL option on this dialog is set to Yes. If no certificate file is specified for the SQL OpenNet client, the client will not validate the server certificate.

6. Click OK.

You can now use the DSN in an ODBC-enabled application.

Adding a non-sharable file DSN

1. Add a user DSN by following the steps in Adding a user or system DSN.
2. Use a text editor to create a file with the following (where dsn_name is the name of the user DSN):
[ODBC]
DSN=dsn_name
3. Save this file to the directory where DSNs are stored for the ODBC Administrator. Give it a .dsn extension. You can now use the file DSN in an ODBC-enabled application.

Modifying a DSN

To modify an existing user, system, or non-sharable file DSN, do the following:

1. Open the ODBC Data Source Administrator utility. See step 1 in Adding a user or system DSN.
2. Select the DSN on the User DSN, System DSN, or File DSN tab. Then click the Configure button. When you click Configure, the xfODBC Setup window displays the current configuration.
3. Make any necessary changes to the fields in the xfODBC Setup window, and then click OK. See Adding a user or system DSN for information on these fields.
Note

If you’re using the ODBC Data Source Administrator, changes made to non-sharable file DSNs are automatically applied to the corresponding user DSN. If you make a change to a non-sharable file DSN that resides on a network machine, the change is applied to the corresponding user DSN on the machine you use to make the change. The corresponding user DSNs on other clients must be modified separately.

Prompting the user for information

If you would prefer that users enter the username, password, or connect file for the database, leave that information blank in the xfODBC Setup window. When the user attempts to access the Synergy data through a third-party application, the xfODBC driver checks the DSN and prompts the user for missing information as shown in figure 2).

2. The xfODBC Info window.

The xfODBC Info window

Deleting a DSN

1. On the User DSN, File DSN, or System DSN tab of the ODBC Data Source Administrator, select the DSN you want to delete.
2. Click the Remove button.

Port settings

To make a connection with SQL OpenNet, the port setting for the client must match the port number for the SQL OpenNet server. For example, if vtxnetd is started on port 1990, the client must use port 1990 to connect to the SQL OpenNet server.

On the server…

You can specify the port number in either of the following. We recommend setting the port number in the vtxnetd or vtxnet2 command.

For more information on server-side port settings, see Configuring Connectivity Series.

On the clients…

On clients, specify the port number in the DSN or, for a DSN-less connection, specify it with one of the following:

A quick way to ensure your port settings match is to run either the synxfpng utility (with the -x option) or the vtxping utility. For more information, see synxfpng utility and vtxping utility.