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?

  • 3
    I always thought of DSNs as glorified (?) connection strings.
    – Oded
    May 16 '12 at 15:50
  • 3
    well, i think this is a job for mythbusters
    – Rafael
    May 16 '12 at 15:53
  • 2
    @Rafael: I don't think Mythbusters will take this since there's very little potential for fiery explosions. ;) May 16 '12 at 15:54
  • 4
    And I thought the Windows environments was the bad practice all this time!
    – user7519
    May 16 '12 at 15:58
  • 1
    Your code will never be future-proof. It may run fine now, but throw it on the 128-bit CPU or a quantum computer and it will be laughable. The best you can do is solve the today's problems and try to anticipate the future a little bit. ODBC DSN works fine for us, that is because we have to talk to both SQL Server and Oracle. If Oracle was completely out of the picture, then we could do lots of things differently. So, if SQl Server is all you have to worry about, then you have lots of choices for how to connect to a database. Linq, Entity Framework, third party ORMs. ODBC+DSN is just a tool ...
    – Job
    May 16 '12 at 20:23

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

  • good point ... i like your reply
    – Rafael
    May 17 '12 at 15:14

As KenK stated, an absolute "good" or "bad" is a tough call. One potential negative of using a DSN connection is that if changes need to be made it can be difficult to update all clients. Another is if developers need to switch servers for testing, it can be difficult to do so, particularly in corporate environments which are increasingly locking down developer access to control panel commands. It may be easier and better to use a configuration file, which developers could be granted explicit permission to change locally on their development boxes, and could be updated as part of the network login script for regular users if changes need to be made. There are other options as well. But it really depends on your specific needs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.