Windows 7 - MySQL ODBC System DSN creation

[Lea Massiot]

Hello and thank you for reading my post.

My software creates MySQL ODBC System DSNs...
... except it doesn't work with Windows 7.
It used to work with the previous Windows OSes including XP, Vista.
I've been struggling the whole day trying to find a solution.

*** I believe UAC (User Account Control) has something to do with this.
But I can't disable it as my customers have it usually enabled.
How can my application be allowed to create MySQL ODBC System DSNs and as a "normal" user?

*** The MySQL ODBC connector I have installed is:
mysql-connector-odbc-3.51.30-win32
and I have installed it logged as "Administrator".
(I also installed my application logged as "Administrator").

*** The MySQL server is on localhost for now... (that is to say on the same host as the application).

*** I've already updated the "Target" in the "Shortcut" tab in "Data Sources (ODBC) Properties" to "%windir%\syswow64\odbcad32.exe"
so that the "Drivers" tab is properly populated, including "MySQL ODBC 3.51 Driver".

*** Logged as "Administrator" (which shouldn't be of course: this account is disabled by default, I enabled it for testing reasons), MySQL ODBC DSNs are created but they are empty:
- only the "Data Source Name" field is not;
- the "TCP/IP Server", "User", "Password" and "Database" fields are empty.

*** In my application it results in the message: "No database selected".

*** If I'm logged as a "normal" user, MySQL ODBC System DSNs are not created at all
(or maybe they are but they do not appear in the "System DSN" tab).

I read something like: the application should be "elevated" but I don't know if it would solve the problem nor how to do it. Apparently UAC prevents applications from creating ODBC System DSNs... ! There has to be a way around!
Actually a solution is proposed on this website: http://kb.avecto.com/articles/how-to-allow-a-user-to-change-add-odbc-dsn’s/
but I do not wish to buy this product especially if there exists a solution without it.

I really don't know what to do now...
can you help me?

Thank you and best regards.

 

[Alejandro85]

Most likely yes, UAC is the cause of the problem. Changing system DSNs naturally requires administrator privileges and therefore CANNOT be done by normal users, UAC or not (UAC makes this worse by converting admins to normal users under the covers). But it's the normal, expected behavior, as regular applications aren't supposed to messing with global DSNs, and only elevated ones are allowed. Respecting that is the whole point of the admin/regular user distinction. You app seems to be abusing its administrative rights on pre-UAC systems.

First, as a side note, I think using DSNs is a bad programming practice, as it ties the database access to something not entirely manageable by the app itself. I would store the server/database/user/password somewhere else, where you know, and build a connection string from it at runtime and use that to connect, therefore forgetting the problems of DSNs. Such refactoring may be easy or complicated, depending how is the program made, but in the long run I think it's the best solution.

If you want to continue use DSNs, then at some point you must elevate the app to allow such change. I guess that the DSN is changed from a configuration screen somewhere where the user chooses the database, then the whole program simply uses that.
The idea is to create a second executable program and manifest it as "require administrator" and make that program make the changes, after UAC prompt. The main one can call that second passing the data to be used in the DSN.
While it looks complicated, it's the normal way an application elevates for certain operations only in Win Vista+, without executing the whole program as administrator everytime.

 

[Lea Massiot]

Actually, the problem is not UAC.
I re-installed a MySQL server and a MyODBC driver which are "compatible" with each other.
Even with UAC enabled, I can now use my application normally with successful accesses to the databases through the MyODBC connector.

This is an interesting article:
"The 32-bit version of the ODBC Administrator tool and the 64-bit version of the ODBC Administrator tool display both the 32-bit user DSNs and the 64-bit user DSNs in a 64-bit version of the Windows operating system":
http://support.microsoft.com/kb/942976

Best regards.