Two Ways To Connect To SQL Server 2000 Via ASP - Using system DSN's

(Page 2 of 5 )

A Datasource Name (DSN) is a name used to request a connection to a database. DSN's allow you to connect to a database simply by referencing the name of the DSN, and hides the actual details of the database server, user id, password, etc. There are three different types of DSN's:

1. User DSN: A user DSN stores the details of how to connect to a database for the currently logged in user only. If you log off your machine and log back in as a different user, then the DSN will not be accessible. The details of a user DSN are stored on the local computer, within the registry.
2. System DSN: A system DSN stores the details of how to connect to a database. It is accessible by every user on the machine where it was created, including Windows NT services. The details of a system DSN are stored on the local computer, within the registry.
3. File DSN: A file DSN allows you to connect to a data provider and can be shared by authenticated users who have the same database drivers installed. The details of a file DSN are stored within a file on the local computer.

System DSN's are the most popular because they can be used irrespective of the user who is logged in. Let's look at the steps involved in creating and using a system DSN.

Firstly, open the control panel (start menu -> settings -> control panel). Creating DSN's is considered the task of a system administrator, so double click on the "Administrative Tools" icon:



From within the administrative tools folder, double click on the "Data Sources (ODBC)" icon. This will load the ODBC data source administrator property page:



By default, the user DSN tab is visible. Change to the system DSN tab and click on the "Add..." button. The "Create New Data Source" wizard is displayed. Firstly, you will be asked which type of database you want to create a DSN for.

DSN's can be created for a number of different databases including Access, dBase, Excel, FoxPro, Oracle, Paradox, MySQL and SQL Server. Because we are creating a system DSN for SQL Server 2000, choose the "SQL Server" option and click on the finish button:



The wizard will now ask for the details of the DSN, which will be stored in the registry. The name of the DSN can be anything you like and can contain spaces. For our sample DSN, enter "TestDSN" into the name field.

The description field allows us to enter some details relating to what our system DSN is for. We will leave ours blank. Lastly, we need to enter either the IP address or netbois name of the SQL server that we will be using our DSN to connect to. Enter either "127.0.0.1" or "localhost". Click on the finish button to proceed to the next step of the wizard.

Now we need to setup the login parameters and method for our system DSN. We have two available authentication options: Windows NT authentication or SQL Server authentication. Because we are connecting to a database, select the SQL Server authentication method. I've had trouble in the past with Windows NT authentication: if you change your Windows login credentials once you've created a DSN, then you're DSN won't pass the authentication check when you try to use it.

If you have a standard SQL Server 2000 installation and have not modified the user accounts, then enter a login Id of "sa" (System Account) and leave the password blank. On the other hand, if you've added more user accounts, then enter the login Id and password of your preferred SQL Server user account. The wizard should look like this:

Next: Using system DSN's (contd.) >>
 

More ASP Articles
More By Joe O'Donnell

Comments On: Two Ways To Connect To SQL Server 2000 Via ASP

>>> Be the FIRST to comment on this article!