What and why do I publish this here?!?
Great question! This is not the type of posts which I usually publish. There is nothing new in the content and nothing that I add value from myself. ALl the information might be found in other threads in forums and posts.
This is a pure answer to a question in Microsoft new forum interface Q&A. The forum does not allow me to publish the answer, raising the absurd error "Exceeded the Normal request". This is a very simple answer which is not even long as you can see. At this time this is the only way I have to provide the user a detailed answer to his question.
I hope this at least will be useful for my other readers as well :-)
The original Question
ODBC System-DSN cannot store user password (Azure SQL)
we connect our application to a Azure SQL database using ODBC system dsn. SQL-Authentication (user/Password) is in use. I am not able store the user password. Everytime the app starts I need to enter the user password. Help is apprectiated.
Kind regards, Roland
Good day Roland,
Your question has nothing to do with "Azure SQL Database" (or with the "Microsoft SQL Server ODBC drivers" as Dan already explained).
Your question is related to your client side application, which you use in order to connect to the server. There are applications which allow to store the password for future connection (like the Azure Data Studio or SQL Server Management Studio) and there are applications which does not have this option.
According to the image in your post, it seems like you are using a client application named odbcad32.exe or full named "Microsoft Open Database Connectivity (ODBC) Data Source Administrator tool". This tool is simply manage DSNs
DSN = Data Source Name is a string that has an associated data structure used to describe a connection to a data source. It simply store the information and it can be used by the connection string in the same machine.
The 32-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\SysWoW64 folder. The 64-bit version of the Odbcad32.exe file is located in the %systemdrive%\Windows\System32 folder. As explained, these are simple client applications (tools).
Therefore, the answer to "how do I save the password in odbcad32.exe" is the same regardless the database type which you use.
When you are using the odbcad32.exe application in order to connect to a database, then you configure which driver to use. But again, the driver itself also does not store any data of the password.
Back to your question...
odbcad32.exe does not store the password as you found out. Your can store the password in the application side (The App that you use which uses the DNS which you configured in the odbcad32.exe APP).
The user DSNs are stored under the following registry subkey: HKEY_CURRENT_USER\Software\ODBC\ODBC.INI or in the following: HKEY_CURRENT_USER\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\ODBC
The system DSNs are stored under the following registry subkey: HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI or under the following registry subkey: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI
Inside the location you should find a key with the DSN name which you created
For example, in windows 10 if you create system DSN with the name "Ronen_DNS_ServerName", then all the information of the DSN should be in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\Ronen_DNS_ServerName
Inside this location you will find a value with the name: "KeystoreSecret" and empty data. You should NOT edit the registry or so anything like adding the password in this location even if others do it :-)
I am not working with DSN usually, and I found a thread where Warren Read from Microsoft says that: "Even if you modified your registry information to store the password ODBC would not read that information". You should not test it but someone might want to do it without my recommendation.
Anyhow, you can always add value to the key with the data of the password and you can read ther key from your application in order to use it for the password - can do does mean you should do!
In short, The official answer is that odbcad32.exe does not store the password in the DSN information. You should manage the password in your application level.
I'd recommend you NOT to store userid/password in DSN. Instead, specify these values when connecting from your application or use other authentications like AD.