I know I may be in the minority here, but my feeling is that as a developer, my job is to make everyone else’s life a little easier. This includes administrators who must maintain code that I write.

Connection Strings have always been something that requires a little extra knowledge in order to understand how they work, something that developers should fully understand, but administrators shouldn’t. They have a different discipline to manage and shouldn’t have to manage the complexities of setting up connection strings.

Depending on the database, the developer may or may not have to provide the PROVIDER keyword. This is necessary when using ODBC; but what if the developer wants to use Microsoft-centric) SQL objects?? Then having PROVIDER in the connection string will throw an error.

Why should the administrator supporting or installing the application have to manage this complexity? Why should an administrator have to know the subtleties of setting up integrated security vs. mix-mode authentication?

How does one store a password in a connection string without encrypting part or all of the application configuration file?

One normally finds a connection timeout in the connection string, but not the command timeout. Why does the command timeout have to be treated differently within an application configuration file?

I propose that these elements be defined in a configuration file in an alternative way.   The following parameters should be separate key/value pairs:

  1. User Name
  2. Password: If this is not present, use Windows authentication
  3. Server
  4. Database Name (aka Catalog)
  5. Connection Timeout
  6. Command Timeout (which you wouldn’t find in a connection string for SQL Server)
  7. Database Type

Database type is key. This will determine how the developer parses a connection string and whether to use ODBC or Microsoft SQL objects in the data access layer.

One other thing I’d like to note here is the password, if presented, could be encrypted and stored in Base64 encoding. This mitigates the need to encrypt the application configuration file. Encryption is beyond the scope of this particular article.

For a database type, may I offer the following code:

 

And to parse a connection string for a SQL Server 2000 – 2014 database:

 

This may complicate things a bit for the developer, but I think this actually give the developer more control over inputs to his or her program and simplifies things for everyone else.

 

 

Like this post? Share it!