Creating a new ODBC Data Source Name (DSN) is a straight-forward operation. If you're connecting to SQL Server via Microsoft Access or a scripting language such as Perl using Win32:ODBC, you'll need a valid DSN. Using the OBDC Data Source Administrator, you can create User, System, and File DSNs as you need them.
ODBC has three different categories or types of DSNs:
Each DSN category serves a specific purpose and has a specific scope.
A user DSN is just that, a DSN for a specific user. If I create a user DSN under my user account, no other user can see it or use it. The DSN is for me and me alone. If you need a connection to a data source that only you should use, choose a user DSN.
A system DSN is a DSN that is seen by the entire system. Any user can see it, as well as any process or service. If you need a data source connection that should be seen more than just your user account, choose to use a system DSN. This is especially true if you are trying to establish a connection through IIS or some other service.
A file DSN is simply where the connection settings are written to a file. The reason for having a file DSN is if you want to distribute a data source connection to multiple users on different systems without having to configure a DSN for each system. For instance, I can create a file DSN to a reporting database on my desktop. I can then send the file to my users. My users can save the file DSN to their hard drives and then point their reporting applications at the file DSN.
I'll be creating a simple user DSN, but the process is basically the same for a file or system DSN. The only difference with a file DSN is you'll be prompted to save the DSN (since you're saving to a file), a step that isn't necessary with the other two DSNs.
The first step in creating a DSN is to open us the Data Source Administrator. On Windows 2000 Server systems (and other Server builds), one method to navigate via Start | Programs | Administrative Tools | Data Sources (ODBC) as in Figure 1.
Figure 1: Accessing the Data Source Administrator (Start Menu)
If you are using a workstation-class version of the operating system such as Windows 2000 Professional, chances are you won't see the Administrative Tools in your Start Menu. You can access the Administrative Tools by going through the Control Panel. The navigation path is Start | Settings | Control Panel | Administrative Tools. Within the Administrative Tools you should see an icon for Data Sources (ODBC) as in Figure 2.
Figure 2: Accessing the Data Source Administrator (Control Panel)
Choose Data Sources (ODBC) and that'll bring up the Data Source Administrator.
Once the Data Source Administrator opens up, choose the tab appropriate for the DSN you want to create. I'll leave mine as a User DSN since I'm just showing how to create a DSN in the first place. To begin creating a new DSN, choose the Add... as in Figure 3.
Figure 3: Adding a New DSN
Once I've chosen to add a new DSN, the first thing I'll have to pick is the correct driver. Since I'm connect to a Microsoft SQL Server database, I'll choose the SQL Server driver. This tends to be towards the bottom of the driver list, as in Figure 4.
Figure 4: SQL Server Driver
Once I've chosen the driver, I'll next be prompted for the name of the DSN and the SQL Server I'll be connecting to. In both cases I'll specify SQLServer as that's the name I want my DSN to be and that happens to be the name of the SQL Server I want to connect to, as in Figure 5.
Figure 5: Choose Name and Server
Once I've chosen the server I want to connect to, I next need to choose how I'll connect, whether by NT Authentication or SQL Server login. If I choose SQL Server login, I'll have to specify the username and password to my ODBC connection in my application. If you have questions on this, refer to your application documentation. In this particular case, I'll choose Windows NT authentication as in Figure 6.
Figure 6: Choosing Authentication method
Once I've chosen the authentication method, I can configure additional settings such as the default database. I want to make a connection to the Northwind database, so I'll choose that as my default database as shown in Figure 7. Do note, this is a standard SQL Server connection, so if I have access to other databases on the server, I could use this connection to get to them. I would either have to change databases in my application or access database objects in another database using a three-part naming convention (<database>.<owner>.<object> such as Pubs.dbo.authors).
Figure 7: Choosing a Default Database
Once I've chosen my default database and a few of the connection settings, I do have the option of one more screen of additional settings, as shown in Figure 8. I'll keep the defaults.
Figure 8: Choosing Additional Settings
Once I've configured all of my settings, I'm brought to a confirmation screen, as shown in Figure 9.
Figure 9: Confirmation Screen
One thing I always do is click on the Test Data Source... button to verify I'm able to make a connection to the data source. This step generally isn't necessary, but I do so just out of habit. If all is well, I should receive a successful test confirmation screen like in Figure 10.
Figure 10: Test Successful
And that's all there is to creating a new ODBC Data Source Name.