Export an ODBC Data Source from the registry

I recently found myself needing to do some Crystal Reports testing on a virtual machine. The tests were successful on my laptop but we use a  virtual machine running Windows Server 2008 R2 and IIS7 as a clean environment for testing. With these tests we were using both ODBC and SQL OLE DB connections to our database. The OLE DB connections were easy enough to replicate but we wanted to make sure our ODBC settings matched my laptop environment. To do this we used the Export feature in the system registry of my Windows 7 laptop.

Before I demonstrate this though I should explain what ODBC is. Open Database Connectivity (ODBC)  is a standard way for connecting to databases. ODBC and the associated driver can connect to a database regardless of the database manufacturer or the operating system on which it runs.

I will demonstrate the steps below on how to run the export.

1. First we need to see which ODBC connections we want to move. To view the existing ODBC connections we can open the ODBC Data Source Administrator. To do this click the Start button and type “ODBC” into “Search programs and files” on the Start Menu. Click “Data Sources (ODBC)” to run the program. It can also be launched from Administrative Tools | Data Sources (ODBC) if it is enabled on your machine. A tabbed dialog box will appear. Most of your ODBC connections should be housed under the User DSN tab.

2. To begin the export process we need to start the Registry Editor. To do this we can search “regedit” from the Start Menu and click Regedit.exe to begin. Another way is to launch the Run Command, enter “regedit” into the dialog and hit OK.

3. You  should now be within the Registry Editor. ODBC Data Sources are stored as keys within the Windows registry. Fortunately they are easy to retrieve. Your DSNs will be housed in two separate spots on Windows 7. (Other operating systems tend to place these in different files so you may need to hunt around.)

User DSNs: Computer\HKEY_CURRENT_USER\Software\ODBC\ODBC.INI

System DSNs: Computer\HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI

4. Once you have found the correct folder you can now export the desired connection(s). Right click the ODBC.INI folder and select Export to copy every ODBC key in the folder. You can also choose a more granular level of exporting by selecting one DSN within the folder at a time. Unfortunately  the CTRL key cannot be used to select multiple files for one export.

A dialog window will appear. You can choose to export it in multiple file types (although .reg is the standard) and where to save the exported file. You can also select what you want included in the file through the Export Range. The option defaults to “Selected branch” which means you only get the DSN properties listed in the key. You can also select “All” and this will export every registry key in your system. A caveat though – when exporting using All within the Export Range it exported a file containing 3, 907, 557 rows of data. (I ran it twice to be sure this was correct). If you do not have many DSNs to move you might be better off to export each DSN individually.

Once the Export is saved to disk it will create a .reg file. This file can be opened and examined with a text editor like Notepad. Within the file you will see the various properties of a DSN like server and database names, as well as the selected database driver.

With the .reg file open you will now need to modify the file. As you have seen above when you export the ODBC Data Source you only get the DSN properties. However, when you insert this code into the registry on the target machine the DSN will be inserted into the registry but will unavailable elsewhere. To ensure you will have access to the DSN within your Crystal Report file, the ODBC Data Source Administrator and other places you are using ODBC Data Sources you will need to prepend the following code to the file:

[HKEY_CURRENT_USER\Software\ODBC\ODBC.INI\ODBC Data Sources]
"DSN name"="SQL Server"

Modify the “DSN name” to match the name of the ODBC Data Source folder you exported.

5. You can now copy the .reg file to the destination computer for installation. Before you install though you might want to open the file and compare the DSN properties. It might be possible the target computer contains different settings than the export file. You can edit the .reg file to match before deploying the file.

6. To insert the values on the target computer open the ODBC Data Source Administrator and select File | Import… to browse to the file. Click Open to run the process. You can also simply double-click the .reg file and it will install the values into the correct locales.

7. The last step is to verify the install. Open the Registry Editor on the destination computer and navigate to the correct DSN folder path. Confirm that your ODBC Data Source is listed in the folder. Close the ODBC Data Source Administrator and re-open it to confirm they are accessible there as well. You can then tweak the DSN further by using the Configure button if need be.

That is it in a nutshell. In this tutorial we have exported ODBC Data Source connections between two computers running different Microsoft operating systems. We saved ourselves time and headaches by ensuring our connections are identical between the two machines.