Jaa

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

  • Artikkeli

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
  • 2) HKLM\Software\ODBC\ODBCINST.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

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

  • Anonymous
    September 27, 2011
    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.

  • Anonymous
    October 09, 2011
    The comment has been removed

  • Anonymous
    March 25, 2014
    Thanks Faruk Celik for making my day and giving my mystery an answer. Cheers Lorenz

  • Anonymous
    April 06, 2016
    I was also missing what appears to be a "pointer" for lack of better verbiage to the ODBCWindows 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 keycomputer\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'sName: ValueType: REG-SZData: SQL Serveronce 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.