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


Registry Editor

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.

Registry 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.

Registry Export Dialog

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.

Exported Registry File

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:

"DSN name"="SQL Server"

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

Modified Exported Registry File

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.

Registry Import

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.

ODBC Data Source Administrator

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.

About Ken Cenerelli
I am a Programmer Writer, .NET Developer, Microsoft MVP - Visual Studio and Development Technologies, Public Speaker, Blogger, and Microsoft Azure Nerd. I blog regularly at kencenerelli.wordpress.com and can be found on Twitter via @KenCenerelli.

8 Responses to Export an ODBC Data Source from the registry

  1. Jon Paulson says:

    Excellent ODBC tip Ken. This came in handy for me the other day.

  2. Mark Hatfield says:

    If it’s a 32bit ODBC the registry path for System DSN is:

  3. Pingback: Create an ODBC using a reg edit file | Stephen Shakespeare

  4. Pingback: 2015 year in review | Ken Cenerelli

  5. Shaun Calton says:

    I know this is an older thread but I was wondering if the export process takes into consideration an assigned static TCP\IP port rather than the typical 1433. The Registry Key contains does contain a number of accurate string values for the existing connector(DB, Description, Driver, Last User and Server).

    For SQL I was wondering how I would by default if the export records a static port configuration or if I’d need to add another DWORD or STRING value to accomplish this.

    If anyone is still reading this, Help!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: