Data Centers

Configure IT Quick: Configure client-server connectivity with the ODBC Data Source Administrator

Configure client-server database connectivity with ODBC Data Source Administrator


The ODBC Data Source Administrator, found in the Administrative Tools of Windows 2000/XP Professional, is a multipurpose tool for providing connectivity to one or more databases. ODBC stands for Open Database Connectivity, a Microsoft API that allows connectivity to a database using specific drivers. Each type of database, such as Microsoft Access, Paradox, Oracle, and SQL Server, requires its own ODBC driver. Many are installed automatically with Windows and will require no configuration. However, a network data source will sometimes need to be explicitly defined.

I'm going to provide an overview of the Data Source Administrator and then discuss in detail how to configure a client-server data source using both registry-based and file-based methods.

Overview of the Data Source Administrator
When Windows is first installed, it includes a number of ODBC drivers for many of the most commonly used databases. You can access the drivers using the ODBC Data Source Administrator (Figure A).

Figure A
The ODBC Data Source Administrator tool


The Data Source Administrator offers seven tabs. The User DSN (Data Source Name) tab lets you configure a data source that will be used only on the local machine by a local user. Typically, entries in this tab are added by applications that are installed locally and that use a data source, such as Microsoft Office. However, you can add entries manually. Figure B shows this tab after Microsoft Office 2000 has been installed. Other applications may install other user data source names.

Figure B
Data sources available after installing Microsoft Office


The System DSN tab allows you to configure a specific data source to a client-server database. I will go into more detail about this later when we work through our example.

The File DSN tab does the same thing as the System DSN tab, but there's a difference between the two. Using the System DSN tab to configure a connection will write an entry for that connection in the registry, while using the File DSN tab will create a file stored on the local server (more about that later as well).

The Drivers tab lists the ODBC drivers that are stored on the computer, with information about them, including company, date, and version.

The Tracing tab is most likely to be used by programmers in debugging ODBC-related problems. With its options, they can trace calls to ODBC functions and analyze the results.

The Connection Pooling tab allows you to enable and configure connection pooling for a specified driver. Connection pooling uses a connection to a data source from a pool of connections, so that a connection does not have to be reestablished each time you access the data source. Unless you are having trouble establishing or maintaining a connection, you probably won't use this tab.

The About tab lists the version and file location of each of the core ODBC Windows components.

Establishing a System DSN connection to a client-server database.
As I mentioned, the System DSN tab lets you create a connection to a data source in the registry. Specifically, the registry entry is created in:
HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI\ODBC Data Sources

The advantage of this is faster access, since it's in the registry. The disadvantage is that it will have to be created on each local machine.

We will create and configure a System DSN entry to connect to the Pubs database on a SQL Server 2000 server named Server. (Pubs is a demonstration database that comes with SQL Server 2000.)

To create a new data source, start by clicking the Add button in the System DSN tab. Next, select the correct driver—in this case, SQL Server, found at the bottom of the list—and click Finish, as shown in Figure C. This will initiate a wizard to create a new data source to SQL Server.

Figure C
Select a data source.


In the Name text box, type the connection name, Pubs Database. The description is optional, so leave it blank. Then, select the correct server from the What SQL Server Do You Want To Connect To drop-down list (Figure D).

Figure D
Select a SQL Server as the data source.


The next screen (Figure E) offers two ways to authenticate as a SQL Server database user, depending on how the database administrator configured it: with Windows NT authentication or with SQL Server authentication. For this example, use NT authentication, and leave the check box selected to obtain other default settings from SQL Server.

Figure E
Select an authentication method.


Now, click the Client Configuration button to access the options shown in Figure F. SQL Server uses what are called “network libraries” for network protocols to connect to SQL Server. In a standard Windows network, you will use TCP/IP, which is the default. There may be cases where another protocol is required, but stick with TCP/IP here. By default, SQL Server uses port 1433. We'll assume that the default has not been changed, so leave the Dynamically Determine Port check box selected. When that's not the case, you can deselect the check box and type in the correct port number. Now, l click OK and then click Next.

Figure F
Networking configuration


In the next window, configure a connection specifically to the Pubs database by selecting the Change The Default Database To check box and choosing Pubs from the drop-down list (Figure G).

Figure G
Select a specific SQL Server database


After specifying the Pubs database, click Next to advance to the final window, which offers options for changing language and regional settings. For a standard installation in the country for the language version in use, you probably won't have to change anything here. When you click Finish, you'll have the option to test the connection just created. Once this is completed, you'll have an entry for the Pubs Database connection in the System DSN tab.

Establishing a File DSN connection to a client-server database.
Establishing a File DSN connection to a client-server database is similar to the procedure for a System DSN. The only difference is that instead of writing an entry in the registry, this procedure will create a file with the extension .dsn on the local drive in the folder at C:\Program Files\Common Files\ODBC\Data Sources. The advantage of this approach is that the file can be easily copied to other machines.

To create the file, select the File DSN tab and click Add. As before, select SQL Server as the ODBC driver. The next window will ask for a filename (Figure H).

Figure H
Provide a filename for a File DSN connection.


Once you provide the name and click Next, all of the remaining steps are exactly as they were when creating a System DSN connection.

Final word
These examples made use of SQL Server. There are, of course, other relational database management systems (RDBMS) in use, such as Oracle or Sybase. The ODBC Data Source wizard for each one will have differences based on the peculiarities of each system. It is important for you to have some understanding of the features of the RDBMS your users are accessing so that you can effectively configure their connections. At the same time, applications that use an RDBMS will, in all likelihood, provide instructions on exactly how the data source should be configured.

 

Editor's Picks

Free Newsletters, In your Inbox