What if your ODBC DSNs want to play "hide-and-seek" with you ?

One of our customers has been reporting that they cannot see their "System Data Sources" under "System DSN" tab when they clicked on "ODBC Data Source Administrator" Control Panel icon.  They were also trying to add a new DSN, the wizard was completing succesfully but the DSN was not appearing in the list. Strange.. Ain't it ?

To understand what is going on in this scenario, we need to know the things going under the cover when we're adding a System DSN using "ODBC Data Source Administrator" Control Panel icon (By the way, I prefer typing "odbcad32" in Start/Run rather than going to Control Panel to reach "ODBC Data Source Administrator" tool)

An ODBC System DSN is othing more than some registry entries under HKLM\Software\ODBC (and/or HKCU\Software\ODBC).  

The HKLM\Software\ODBC contains two main keys :

  • 1) HKLM\Software\ODBC\ODBC.INI

1) HKLM\Software\ODBC\ODBC.INI : Contains the values for your specific driver under your DSN name. For example, if you've created a DSN named "MyDSN" against a SQL Server 2005 with SQL Native Client (SNAC) ODBC Driver, you're going to see a HKLM\Software\ODBC\ODBC.INI\MyDSN key on the left pane of the registry editor and some SNAC related parameters and the values you've entered during DSN setup. For example pairs like Database=pubs, Driver=C:\Windows\system32\sqlncli.dll, LastUser=benjaminlinus, Server=DHARMASERVER1, Trusted_Connection=Yes. (Yes, I'm a huge L.O.S.T fan 😉  )

Under this HKLM\Software\ODBC\ODBC.INI key there's an important key named HKLM\Software\ODBC\ODBC.INI\ODBC Data Sources . This key contains the name of the DSN and the type of the ODBC Driver that you've created.

2) HKLM\Software\ODBC\ODBCINST.INI : Contains all the "ODBC Drivers" existing on your machine (and just a few additional ODBC Core related keys)

Putting al those information in mind, I dived into the registry of the problematic server and walked through those registry keys and all were looking fine. Also the newly created DSNs were coming there under the registry places as they should be.

My colleague Olga (big thanks goes to her) warned me about checking the "(value not set)" values for the keys. Following her directions, I decided to take a registry export as .REG file of HKLM\Software\ODBC from the problematic Windows 2003 SP1 machine. Also got an export from my test box, compared and the thing that I noticed was surprising.

I noticed that value for "(Default)" is set to "(value not set)" under HKLM\Software\ODBC , HKLM\Software\ODBC.INI and "HKLM\Software\ODBC.INI\ODBC Data Sources" keys. But differently the value "(value not set)" has been entered manually. I mean in somehow this "(value not set)" has been entered as a value.

Normally Windows Registry Editor shows the value "(value not set)" for something if its value really not set. But of course, you can enter this "(value not set)" string by manually and the value looks same with the "real" "(value not set)".

When you've double clicked on "(Default)" you'll get "Edit String" dialog box like below if the value is really "(value not set)"; you should see nothing in "Value data" textbox :

But it was like below in the problematic keys; the "(value not set)" string value has been added there manually like that :

We've gone through all the keys under HKLM\Software\ODBC until being sure that there are no "problematic" values left. After this "cleaning up" process and all DSNs started showing up. We've also created new DSNs and see that they're showing up in DSNs list.

Comments (5)
  1. Jon Sayce says:

    Have you ever created an ODBC System DSN, only to watch as it doesn't appear in list in the ODBC

  2. Borgwan says:

    This can also happen if you get a non-printable character embedded in one of the ODBC registry strings.  Exporting the registry branches and looking at it with a text editor that shows non-printable characters will find them quickly.

  3. Faruk Celik says:

    On a 64bit OS, if you run "Control Panel/ODBC Data Source Administrator" you're running %WINDIR%system32odbcad32.exe which is 64bit. Let's say you've created a DSN using this, as long as you ran this ("Control Panel/ODBC Data Source Administrator" you're running %WINDIR%system32odbcad32.exe which is 64bit), you will see your DSN there

    But if your application is 32bit, it will never see this DSN created using 64bit odbcad32.exe. On a64bit OSS, to create a DSN which is going to be seen by 32bit applications you have to run %WINDIR%syswow64odbcad32.exe which is 32bit.

    Don't forget that your 64bit applications will also not see the 32bit DSNs.

    If these are not the reasons for your scenario, high likely you're creating too many DSNs and odbcad32.exe (32bit or 64bit) cannot show it though it's stored in registry HKEY_LOCAL_MACHINESOFTWAREODBCODBC.INI (If your OS is 64bit and you create a DSN using 32bit odbcad32.exe, the registry hive will be  HKEY_LOCAL_MACHINESOFTWAREWow6432NodeODBCODBC.INI)

    P.S. : Refer to our support.microsoft.com/…/256986 article before making any changes in registry.

  4. Lorenz says:

    Thanks Faruk Celik for making my day and giving my mystery an answer. Cheers Lorenz

  5. Jonathan H says:

    I was also missing what appears to be a “pointer” for lack of better verbiage to the ODBC
    Windows 2008 r2, looked in 32 and 64 ODBC Admin panels and did not see a System DSN that I needed to update.

    I found the reg key computer\HKLM\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ contained a listing for my DSN; however, I noticed another key
    computer\HKLM\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources\
    this key did not contain a string which identified my DSN. I added a new string to mimic the other SQL Server DSN’s
    Name: Value
    Type: REG-SZ
    Data: SQL Server

    once I added a new string with the same exact name as my DSN, the DSN then showed in the ODBC Administrator.

    Something to note, this DSN was created via a software install years ago. I was tasked with updating the connection string to a new database, yet it was not to be found until I poked around in the registry for the DSN name by doing a find and F3 for find next in a reg search until I landed on the registry entry I was looking for.

Comments are closed.

Skip to main content