There are a bazillion articles on this subject, but this one is the only one you need. Why? Because. That’s why.
DSN: You lock up your database connection credentials inside Windows and your application asks for the credentials each time it needs to make a connection to the database.
DSN-less: You put the database credentials into your application source code and it hands that over each time it needs to make a connection to the database.
Which is more secure? Really? Neither.
Which is “better”? First, you have to define “better”. If you define it as being related to faster or less complicated “performance”, then DSN-less wins, but only by a smidgeon.
The reason is pretty simple: When you use a DSN, your application has to ask Windows to provide it’s contents each time you refer to it. In a web application, that means everytime a particular code page makes a database connection. Multiply this by how many users are hitting that page and you get a rough idea of the traffic load: both within the server-to-service connection, and between the server-to-resource channel (web app talking to remote database, assuming it’s on a different server). Windows stores DSN information in files and in the registry, which is essentially another file. So when you make a connection, it asks Windows for the DSN contents. Windows queries the registry and opens files and reads the information and hands it over. There are at least four or five steps (more if you start talking threads, locks and handles) for each DSN query.
I said “pretty simple” didn’t I. Heh heh. Never trust anyone who says that.
Now, with a DSN-less connection, you hand the credentials directly to the connection request statement. Much less overhead. No in-between steps.
Why is DSN no more secure than DSN-less? Because if you lock down the permissions to your server so that only “administrators” can logon or remote in, and you lock down the folders and shares properly, it will require the same access privileges to get to either one. It’s a zero sum gain by either route.
Of course, this is implying open source code like PHP or ASP, but ASP.NET uses this approach with a web.config or machine.config file as well. Same concept. Keeping your cash in a safe or in your armor car. yeah, that’s a pretty good analogy. Ok, maybe not.