Go DSN-Less!!

No more DSN connections ever… (almost)

For years, as a developer needing to access data from a variety of ‘non-native’ sources, I’ve built DSN’s or ‘Data Source Names’. The little files became a repository of all the connection information required to hook up with and get data into, and out of a data source, and then packaged and included them with our applications for distribution.

Whenever possible today, I prefer to utilize the newer “OLE DB” access method, but not all languages, nor all databases have OLE DB providers available yet. So like with many things, I use what I have, regardless of what the ‘best’ way might be.

For a while now, where ever I have to use ODBC, I’ve been taking a slightly different track. No more ‘DSN’ connections. Instead I’m focusing entirely on ‘Connection String’ type connections.

I know they’re not as popular, for a host of reasons, but for me, this track provides at least one significant advantage, flexibility. The problem with DSN connections, from my viewpoint is that they provide an ‘exposure’ of user name(s) and passwords to even the most casual hacker type

You might argue that embedding an entire connection string, in an application could do much the same thing. Simply hard coded in place I’d agree. However, once you place the components of the connection string in a separate file, and encrypt the sensitive components in the process, it now becomes much more secure than that stored DSN.

There are several ways to access data sources, some MS-SQL options follow:

ODBC DSN Connection String (Not recommended)
In Visual FoxPro

wrkStn = ALLT(SUBSTR(SYS(0),1,AT('#',SYS(0))-1))
myConnStr = "DSN(DSNName);” + ;
“UID=(user);” + ;
“PWD=(password);" + ;
"APP=Microsoft Visual FoxPro;WSID=" + ;
wrkStn + ";DATABASE=(sqldatabasename);” + ;
“Network=(networkname)"
nHandle = SQLSTRINGCONNECT(myConnStr)

ODBC Connection String (recommended)

wrkStn = ALLT(SUBSTR(SYS(0),1,AT('#',SYS(0))-1))
myConnStr = “DRIVER=SQL Server;” + ;
“SERVER=(sqlservername);” + ;
“UID=(username);” + ;
“PWD=(password);” + ;
“APP=Microsoft Visual FoxPro;” + ;
“WSID=" + wrkStn + “;" + ;
“DATABASE=(databasename);” + ;
“Network=(networkname)"
nHandle = SQLSTRINGCONNECT(MyConnStr)

The other reason I don’t recommend DSN, in any form is that, in any concurrent (multiuser) situation all users are bottlenecked by how fast the ASII file that holds the DSN can be accessed.
With “File DSN”, every connection.open must open, read, and close an ASCII file before presenting the data anew to the provider/driver since the ASCII file may have changed since last connection. Even System DSN’s require a reading of the registry to gather up the connection info.

DSN’s usually become bottlenecks on all but very lightly used systems.

Why go DSNLess? First, it requires no server setup, just a carefully constructed connection string as demonstrated above. DSNless connections demand that that you know the name of the file (i.e. file based databases like Access, Paradox, FoxPro, etc.) or the address of the data server (SQLserver for example). Armed with the appropriate information you can now open a data source without a DSN!

It’s faster than even a system DSN* since it saves a trip to read the registry on each attempt to open a connection, it’s also much more secure if you add in this next trick as well.

I use a system file to hold all of the connection information for the entire application. The user name and password are stored encrypted, as are the server and network names. I’ll admit the method I use to encrypt these values is not extremely secure, and could most likely be ‘cracked’. However, the ‘cracker/hacker’ would have to know what file these values are stored in, and, what each one of them ‘maps’ to as I don’t use identifyier names like ‘password’ in that file, only the application and the administrative tools know what these values are for.

So, what happens, when a something changes? The security team changes the production access password, user name or renames a server? If you’ve done something similar to what I’ve outlined above, the admin tools are run, the changes recorded and everyone goes back to work. Much, much simpler than redistributing a new DSN to every user, or rebuilding all the “System DSN’s” at the server level!!

This process grew out of our efforts to create ‘data factory classes’ that enable us to set the source database to MS-SQL, Oracle, or Visual FoxPro (basically any ODBC or OLE DB compliant source), without recoding the application!

In the process we discovered that attempting to maintain the ‘correct’ DSN’s even at the server level was not something we could rely on the customer to do. We could however rely on them to get us the correct connection information whenever they made a change.

The exception to the “No DSN’ rule for us is IBM DB2 data stores. These guys require a specific client, and none of the ODBC drivers I’ve had a chance to work with support DSNLess connections.

Is anyone else doing something similar? Thoughts? Ideas?