During all my professional lifetime that I have been programming in Windows enviroments, I advised my friends and coworkers to not use a DSN database connection in their applications, based on my own personal conclusions, but I really haven't seen any evidence of them.
I know that using a DSN connection via ODBC data source, implies that this connection will use an ODBC driver. I know by some documentation that ODBC is an language-agnostic, OS-agnotic API to connect to databases, everything that carries this level of flexibility comes with some downpoints (could be possible that some specific functionability can't be used with ODBC) or could be that the performance could be lower than using a more direct API (OLEDB or other).
I know that using a DSN-less connection has some problems by itself like securely storing the access information to the database server/file ( can be solved with some kind of encrypter on the data), or unexpected changes in the name of provider used in the connection drivers (Specific case of old informix drivers).
Is this correct, or I'm missunderstanding something?
Best Answer
I'll go out on a limb and say no it is not "bad practice". It is a tool intended for flexible implementations. I might never use it myself as I'd prefer to use the native api for database connections but it clearly has a purpose. If you are writting client-ware to connect to arbitrary databases in different work environments, a DSN solution could reasonably save you a lot of work. You'd write your connection libraries to use DSN via odbc and be able to talk to whatever data system they use.
To try and understand it better, maybe asking the question:
Is it "good practice" to use the db-native api to couple the client app with its backend thereby tying the application to a specific storage system?
You'd have to rewrite the client should you ever want to change the backend. the DSN approach is backend agnostic so potentially better.
On the other hand, having homogeonous systems is itself a benefit when trying to hire someone to come in a fix/update/upgrade the system. Instead of hiring a 'generic DBS-using' contractor, you find yourself an Oracle, Access, MSSQL, or Mysql expert and they'd work in their area of expertise.
I'm always hesitant to outright call anything "bad". It exists therefore it must have had a good reason to come into existance. Truly (and I mean 100%) 'bad' things tend not to get widely adopted. Maybe they become bad over time (shrug).