Creating an ODBC Data Source Connection

While there are many ways of creating data connections for use in software development and applications, one of the most commonly used and supported methods is through the use of Open Database Connectivity (ODBC). The following example will show the steps required to create a data access source, which can then be used by supported applications for accessing data through a data connection. The source used here will be an SQL Server 2005 database on a Windows XP operating system.

To create an ODBC data source connection the Windows ODBC data source administrator tool is used and can be found by navigating to: Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC).

There are three different types of ODBC data source connection that can be created here, User, System and File. A system data connection source will be used for this example as this type will allow an administrator to create a connection for the host server or PC, independent of which user needs to access the data source. To create a system data source select the System DSN tab. The system DSN tab shows a list of all the current data sources, which can be modified, added to and deleted.

To create the data source required for this example, click the Add button located to left of the data sources list. From the resulting driver list, scroll down and select the SQL Server driver and then click Finish. A Create New Data Source connection wizard will appear.

The first section of the wizard requires a data source name, description, and the host name (or IP address) of the SQL Server data source. The data source name will be used for identifying the data source, which can be anything and will be used for data access.

The description field isn’t required, but maybe useful for describing the data source.

The SQL Server source name is the name of the server or PC from where SQL Server database resides. If the SQL Server database is on the same server or PC as the ODBC connection simply typing (local) as the SQL Server name can be used. Once this information has been entered click Next for the next section in the wizard.

The authentication for the SQL server will then be required for access to the database. Either windows or SQL Server authentication can be used here depending on preference. If SQL Server authentication is selected the database User ID and Password will be required. Click next to confirm the authentication credentials and to move to the next section of the wizard.

Within the next section, the wizard will give the option to specify a database. By default an SQL Server database will automatically be selected. This is the database that is set as the master Through SQL Server and is set at the time the SQL Server user credentials are created. If the database needs to be changed, Check the option for changing the default database and then select the database from the dropdown list. Click the Next button to move to the next section.

The next section has options for languages, encryption and data logging. The defaults here are adequate and can left without change. Click the Finish button to finalise the data source settings.

This will display a configuration window, which will allow the data source to be tested. Click the Test Data Source… button. If this returns TESTS COMPLETED SUCCESSFULLY! The configuration has been setup correctly. The data source connection is now ready for use.

Return to all articles