If you’re looking for ideas about ODBC Data Source Name (DSN), you’re in the right place.
But first, Open Database Connectivity (ODBC) is not dead. Development of version 4 is. Drivers are also updated and sold. Even more, you can set up drivers on 3 major operating systems: Windows, Linux, and Mac. So, it’s not farfetched to talk about it today.
Here’s what we are going to cover:
- What is an ODBC DSN?
- Setting up an ODBC DSN
- How to test ODBC connection
- Using an ODBC connection string
Before we start, this guide will walk you through ODBC in a Windows 10 system.
Now, let’s dig in.
What is an ODBC DSN (Data Source Name)?
An ODBC Data Source Name (DSN) is a name that identifies an ODBC connection to a data source. With it goes the name of the ODBC driver and the database or file. Though it varies from one ODBC driver to the next, these are the basic requirements. Another repository is used for storing other configuration settings.
Setting up an ODBC data source is part of Windows Administrative Tools. You can do it in 2 ways: using the 32-bit or 64-bit ODBC Data Sources Administrator.
There are types of ODBC Data sources, as you will see next.
Types of ODBC Data Source
System DSN
When you need a Data Source Name accessible to all users in a workstation, use an ODBC System DSN. This way, anybody logging in to the workstation can use the same DSN. In Windows systems, System DSNs are stored in the registry. You can find the list of it in Computer\HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources.
Figure 1 shows the list of System DSNs using the 64-bit ODBC Data Source Administrator. And Figure 2 shows the same list in the registry.
Aside from the list of System DSNs, you can also find the configuration settings of each DSN under the ODBC.INI folder. See the AdventureWorks System DSN details in Figure 3.
All the entries you did in the ODBC Data Source Administrator are stored in the registry. This includes the driver, server name, database, and security credentials. In Figure 3, you see SQL Server connection settings to the Adventure Works database. It also uses Trusted Connection = yes. It means Windows Authentication is used instead of SQL authentication.
System DSN Pros and Cons
Pros:
You only set it up once for all users using the same workstation
Cons:
Security credentials like user IDs and passwords are readable in the registry. Use Windows authentication or restrict access to the Registry Editor to avoid this problem.
User DSN
A User DSN is the same as the System DSN except it’s intended for a specific user only. The registry location for User DSNs is in Computer\HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources.
Figure 4 shows the list of User DSN using the ODBC Data Source Administrator. Meanwhile, Figure 5 shows the corresponding list in the registry.
User DSN Pros and Cons
Pros:
If intended for a single user in a workstation, this will do.
Cons:
User ID and password are readable in the registry. Use Windows Authentication instead or restrict access to the Registry Editor.
File DSN
The ODBC File DSN stores connection settings to a text file with a DSN extension. So, you won’t find this in the registry. You can view the DSN file with any text editor like Microsoft Notepad. The default ODBC DSN file location is in C:\Program Files\Common Files\Odbc\Data Sources folder. You can change this setting. If another user logs on to your workstation and has access to the folder set for ODBC File DSN, he can change the File DSN.
Figure 6 shows the list of File DSNs using the ODBC Data Source Administrator.
Note that passwords are not saved in the DSN file. Here’s the file content of Sakila MySQL File DSN:
- [ODBC]
- DRIVER=MySQL ODBC 8.0 Unicode Driver
- UID=root
- PORT=3306
- DATABASE=sakila
- SERVER=localhost
File DSN Pros and Cons
Pros:
You can simply copy the DSN file to different stations that need the same File DSN
Cons:
Passwords are not saved. The next time you test the File DSN, the connection will fail because of the missing password. If a data source accepts Windows authentication, use it instead of SQL authentication.
Setting Up an ODBC DSN
Setting up an ODBC DSN connection is as easy as pie. In this section, you will see an ODBC DSN example. We’re going to use a System DSN in our example. It’s almost the same to set it up using User and File DSNs.
Let’s begin.
STEP #1: Check if the ODBC Driver You Need is 32-Bit or 64-Bit
The ODBC Data Source Administrator looks the same whether you use 32-bit or 64-bit. But the drivers are different. You won’t see the 32-bit drivers in the ODBC Data Source Administrator (64-bit). See the difference for yourself in Figures 7 and 8.
So, if you need to connect to Microsoft Access of a 32-bit MS Office, for example, you need the 32-bit drivers. But you need the 64-bit driver for MySQL or Oracle. Note that SQL Server has both 32-bit and 64-bit drivers.
Then, proceed to the next step.
STEP #2: Check If Your ODBC Driver is Installed
From the list of drivers in the ODBC Data Source Administrator, you can see if the driver you need is installed. Check the Drivers tab and refer to Figures 7 and 8 for a sample screenshot.
If it’s not present on both 32-bit and 64-bit, you need to install it first. Then, proceed to the next step.
STEP #3: Add a New ODBC DSN
Check out Figure 9 for a sample that selects the Devart ODBC Driver for Oracle.
Following the sequence in Figure 9, here are the steps:
- First, click the System DSN tab.
- Then, click Add.
- Next, select the driver you need. In this case, the Devart ODBC Driver for Oracle database is selected.
- Finally, click Finish.
The next steps will let you configure the Oracle data source.
Step #4: Configure Your ODBC DSN Data Source
Configuring data sources vary depending on the driver you selected. Different vendors for the same database have different approaches also. You can always refer to the documentation for more information.
But in our example, you’ll see an Oracle data source configuration. See Figure 10 below.
From Figure 10, you must enter the Data Source Name. Then, input the server and security configuration. I also checked Direct mode which lets me input the Host and Port.
There are more options included in 2 more tabs in Figure 10. They are all set with default values. If you need to know their uses and when to change them, refer to the Devart Oracle ODBC driver’s documentation.
If you’re all set, it’s time to test the connection. The next section will discuss it in detail. If everything checks alright, click OK. Then, the configuration window will close, and you’ll be back in the list of System DSNs. You will see your newly created DSN in the list.
If you need to change the configuration, select the DSN. Then, click the Configure button. The same windows will appear with your previous inputs.
How to Test ODBC Connection
Testing your connection is necessary if you want the ODBC DSN to achieve its purpose. If the setup fails, any app using the DSN will also fail. Each driver may have a button to test the connection. For example, refer to Figure 10 again. Then, you will see a Test Connection button.
SQL Server Native Client driver has a Test Data Source at the end of the create DSN wizard. See Figure 11.
Meanwhile, MySQL has the Test button, as shown in Figure 12.
Clicking that Test Connection button in our Oracle example earlier is no different. See Figure 13 for a sample.
A successful connection message will appear when your connection is good to go.
But there can be problems also. Common problems include the following:
- Wrong User ID and/or password.
- You have no access to the Oracle database.
- The Service Name is misspelled.
- The Oracle database service is not running.
Other ODBC drivers will have similar common problems. The message may differ for each driver, but the point is the same.
Using an ODBC Connection String
After creating the DSN, you can finally use it in your apps through the ODBC DSN connection string. We will test the Oracle DSN we made earlier in the SQL Server 2019 Import and Export Data (64-bit) utility. (Note that you can also use this in your .Net code or SQL Server Integration Services data connections.)
So, use the Windows 10 Search and type Import and Export. If you have SQL Server 2019 installed, you will see SQL Server 2019 Import and Export Data (64-bit). Run it.
Then, skip the Welcome screen. And then, under Data source, select .Net Framework Data Provider for ODBC. See a sample in Figure 14.
Look at the connection string in Figure 14. It uses the DSN we created earlier (refer to Figure 10). Two things you can do to use the DSN in the connection string:
- First, type the DSN=OracleXE under the ConnectionString property.
- Or, type OracleXE under the Dsn property. And see the ConnectionString property filled up automatically.
Click Next to proceed to the next step. At this point, we already used the DSN using a connection string. In the end, the connection string is DSN=OracleXE or DSN=<data source name>.
Using a DSN-less Connection String
Another way to use an ODBC connection string is to use a DSN-less connection string or ODBC without DSN. With this, you don’t need to define the DSN using the ODBC Data Source Administrator. But you form the connection string manually using property-value pairs. Here’s an example of using the same Oracle Connection in a DSN-less connection string:
Driver={Devart ODBC Driver for Oracle};direct=True;host=localhost;port=1521;service name=XE;user id=system;password=hidden
This string contains the driver, host, port, service name, user id, password, and direct properties. If you paste this to the ConnectionString property of the wizard earlier, you’ll see a similar screenshot in Figure 15 below.
Forming the string manually should conform to the driver you’re using. It’s also not the same for each driver.
Conclusion
Connecting to ODBC data sources using DSN is easy. You only need 4 steps. You also learned to use the DSN through a connection string.
But if you have any questions or comments, fire them up in the Comments section below.
And if you like this article, please share it with your friends on your favorite social media platforms.
