Connection Strings and TableAdapters Revisited

In a previous post, I had ranted about an issue that I had regarding connection strings and TableAdapters. Since that time, I have dug deeper into the problem and identified what my problem was. And I have a suggestion to help keep it from happening.

First off, it's important to understand how the particular project was structured. It was a Web application and like all good ASP.NET developers, I had segregated various pieces of functionality into different tiers. Specifically, I have an assembly which contains the data access code. It is in this tier that the TableAdapters were being used.

When you use the designer to create the TableAdapter, you have the option to put the connection string into the configuration file. The person who created this assembly (it wasn't me) did exactly that. Which resulted in an app.config file being created in my data access assembly's project.

So now it comes time for me to use the data access assembly. While I have web.config file, complete with (what I thought) was the correct connection string, the name I gave to the string did not match what the TableAdapter had created automatically. This is the beginning of the problems. As I go through the testing, all appears to be working exactly as expected. But now I come to deploy the application.

After deployment, I get the application 'pausing' for about 30-45 seconds followed by an 'unable to connect to the database' message. I check the web.config and see the (still badly named) connection string has been modified appropriately. So I can't figure out why the application can't connect to the server. And I eventually find out the name of the connection string is incorrect. This is where the rant came in.

But the question that I was too relieved to ask at the time was "which server could the application not connect to". After I had cooled down, I started looking. And it turns out that it was trying to connect to my development database. Since I had changed the connection string through the configuration file, I couldn't figure out why the application would even know about the development database. Until I found it. My development connection string was embedded in the TableAdapter code. It was set up as the default value, so that if the connection string couldn't be found by name, it would just use the original string.

Now I have a couple of problems with this approach. First off, it kept me from noticing that I didn't have the correct connection string name being defined. I'll take some of the blame for this. But worse, it means that the connection string to my development database is hard-coded as a string in my assembly. And since I don't treat development database connection strings with the same security reverence as a production connection string, it also means that a valid user id and password were also hard coded in my assembly. And by 'hard coded', I mean that anyone with my assembly and a decompiler would have an easy time finding out a set of valid credentials.

So, ultimately, I'd like to request that Microsoft disable this automatic 'caching' of connection strings. Or at least give me the option to be able to turn it off myself. Because without this, I'm sure there are a number of assemblies that contain secrets people don't expect to be there.