My first technical post in this blog involves a solution that my team and I implemented at work recently. The project is a .NET Web project in Visual Studio 2012.

The Problem:

Each developer on the team has a personal copy of the database for a web application. How will you store each connection string for each developer, without revealing your login information to others?

A Path to a Solution:

(A) Each developer could place their connection string in the Web.config file, without checking in their file. This is not a good choice, because they will eventually need to make other changes to the Web.config file and would have to remember to revert the connection string before each check-in. If they accidentally check in the file with their connection string, they would be sharing it with everyone.

(B) You may ask your DBA to give machine access to each personal database, instead of user access.

More info on machine access for SQL Server databases:

• On MSDN: http://social.msdn.microsoft.com/Forums/eu/sqlsecurity/thread/315b5780-c131-4867-88b9-ebac82e68d77
• On DeceivingArts.com, posted by my colleague, Doguhan Uluca: http://deceivingarts.com/blog/2013/04/give-machine-access-to-sql-server-2008-r2-2012-up/

This will allow you to get access to your database without having to put any user/password information in it. However, each developer will still have have their own connection string.

(C) You could also use the “configSource” property in  the <connectionStrings> element within your web.config file. This involves 2 steps:

(i) Create a separate file (e.g. “ConnectionStrings.config”) in the same location as web.config, and copy your <connectionStrings> block into it.

<connectionStrings>
 <add ...>
</connectionStrings>

(ii) Refer to this file from your web.config file.

<connectionStrings configSource="ConnectionStrings.config">
 </connectionStrings>

Now you have the ability to maintain your own personal copy of each connection string, without affecting your ability to check in the web.config when you make other changes to it.

(D) Using the configSource approach, your personal “ConnectionStrings.config” file becomes a part of the Web project. This is a problem because each person should have a unique version of this file. In order to remove the ConnectionStrings file from the project, simply right-click the file in your Solution Explorer and click “Exclude From Project”.

You may click “Show All Files” in the Solution Explorer toolbar to ensure that your file still exists in the file system.

 

Your Web project will still work, and your Source Control system will never see this connection file. This approach requires developer education to ensure that each developer has created their own version of the file outlined above. In fact, you may now go back to using connection strings with passwords (instead of machine access) if your DBA cannot give you machine access.

NOTE: There are ways to encrypt sections of your Web.config file, but I won’t be getting into that here.

(E) Now, you may be wondering how your deployments will work in various environments (e.g. Test servers, Production servers, etc.) This is where XML transforms come in.

More info: http://msdn.microsoft.com/en-us/library/dd465318(v=vs.100).aspx

In fact, you should already be using transforms for your deployments. Simply expand your Web.config in the Solution explorer to reveal additional config files for each configuration.

If you open Web.Release.config, you should see a section for <connectionStrings> that is typically commented out in a newly-created Web project.

In addition to adding in the correct connection string per environment/configuration, you will also have to update the transform to remove the “configSource” attribute during the transform.

So, your updated Web.Release.config file may look something like this:

<connectionStrings xdt:Transform="RemoveAttributes(configSource)">
 <add name="MyDB" 
 connectionString="MyConnectionStringForThisEnvironment" xdt:Locator="Match(name)"/>
 </connectionStrings>

The Solution:

Here are the steps that you can follow to get the best out of all the suggestions presented above.

1. Create a ConnectionStrings.config file per developer, and exclude from web project.
2. Update the web.config’s <connectionStrings> element to include a configSource attribute to refer the personal file.
3. Update each environment-specific configuration file (e.g. Web.Release.config) to include transforms to remove the “configSource” attribute and add in the correct connection string.
4. OPTIONAL: If possible, configure your databases to allow machine access, so that you can leave out user login credentials from your connection strings.