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