Rick Strahl's Web Log

Wind, waves, code and everything in between...
ASP.NET • C# • HTML5 • JavaScript • AngularJs
Contact   •   Articles   •   Products   •   Support   •   Search
Ad-free experience sponsored by:
ASPOSE - the market leader of .NET and Java APIs for file formats – natively work with DOCX, XLSX, PPT, PDF, images and more

Sql Connection Strings in .Config Files vs. Source Control


One basic question that I see time and time again with source control is how to manage connection strings stored in .config files within source control. Such a small thing, but I see that as a stumbling block for many projects starting up under source control especially for new developers. The problem is that on most developer machines there are differences in how connection strings - and even more specifically the server name - are referenced to access a database.

.config file differences in general are problematic under source control. In Web projects in Visual Studio there are .config transformations that can be applied that can mitigate some of this pain, but this only solves the problem for Web projects. If you have test projects or re-use components in other types of applications like services or desktop apps, .config transformations on their own can't solve that problem easily. There are workarounds described in this StackOverflow Question, but they're not built-in and require some extra effort as well.

Config File Connection Strings under Source Control

While most settings in .config files are pretty universal across configurations and can stay stable, the most common problem are connection string differences amongst users. For example some people use a full version of SQL Server on the local machine (.), others use SQL Express (.\SQLEXPRESS) or the new localDb Sql Server support (localdb) or simply run SQL Server on another machine altogether with a different server name.

If you have any divergence at all and you check in your changes to source control, and then another person with a different server requirement pulls those changes down, they'll likely get a merge conflict and accepting the change results the compiled application not running due to an invalid connection string.

Now you can fix the connection string in your project, but of course then when you push back your changes to the source server you then hose other people who are using a different string and the process repeats. Not ideal to say the least.

There are a number of different ways to address this, but I've been using one of the following two approaches to deal with this particular problem.

Server Aliases

The most common issue I've seen with connection strings is merely the server name rather than other stuff on the connection string. As the connection string is the same except for the server name for all involved, Server Aliases are an easy way to use a single connection string in all configurations.

If only the server name is different the easiest way to set up common base for all users is to set up a Server Alias for each of the users. SQL Server "Server Aliases" can be configured in the SQL Server Configuration Manager and as the name suggest let you specify a server's connection properties via a simplified alias name.  Typically this feature is meant to simplify complex connection features like TCP/IP ports and long SQL connection names, but it also works great to hide the server name from your .config files so that all users can use the same Server name regardless of the variant of SQL Server they are using.

To use it start the Sql Server Configuration Manager from the Start Menu, select the SQL Client configuration and then Aliases:

SqlConfigurationWizard

In the dialog that pops up you can configure the address of the server (. or here a local domain DbBeast server) and protocol, plus a configuration value such as the TCP/IP port (if custom) or a Named Pipe name for example.

Here I set up an Alias called CodePasteAlias that points at my local SQL server which is . or could also be my local machine name. If I were running SQL Express I'd use .\SQLEXPRESS instead for the server name.

Click OK to save and your configuration is set. Note that there are separate 32 bit and 64 bit configurations - you might have to set up the 32 bit or 64 bit or both depending how your application runs.

No SQL Server Client Tools? Use CliConfg.exe

The above assumes you have a local copy of SQL Server or at least the Sql Client Tools to configure. On a client machine that may not be available. Luckily Windows natively ships with a Sql configuration utility which you can access from the command prompt:

cliconfg.exe

Use the Alias tab there to configure the Alias.

Once you've configured the Server Alias you can now use it in a connection string in lieu of a server name.

<connectionStrings>
  <add name="CodePasteContext" 
       connectionString="server=CodePasteAlias;database=CodePasteData;integrated security=true" 
       providerName="System.Data.SqlClient" />
connectionStrings>

This approach is very easy to deal with as it's a one time config option and it will work across projects without changes required to the config files. But - and it's a big but - it only works works if the server name is the only thing that is different. It doesn't work if you have completely different connection strings or you have differing login names.

If you need more control over all of the connection string, this approach won't work.

In practice I've found that Server Aliasing is sufficient most of the time. If necessary creating a custom login as part of the database can ensure that everybody uses the same authentication as well (if integrated doesn't work universally) which is often the only other difference than server name.

External Configuration Sections

If you need more control over the differences in the connection string (or other parts of configuration files for that matter) you can externalize parts of the .config and keep the external pieces out of source control.

.NET Configuration files support the ability to externalize the content of a configuration section quite easily using a configSource attribute. This makes it possible to externalize the entire connectionStrings section for example and then keep the externalized file out of source control.

For example you can do something like this:

  <connectionStrings configSource="web_ConnectionStrings.config">    
  connectionStrings>

You can then create a web_ConnectionStrings.config file and store the following in there:

<connectionStrings>
  <add name="CodePaste" 
       connectionString="server=.;database=CodePasteData;integrated security=true" 
       providerName="System.Data.SqlClient" />
connectionStrings>

You now have externalized the database configuration settings into an external file which can be kept out of source control, so that each user has their own settings of the connection strings. But this has the unwelcome side effect that a new clone from a repository has no connection strings at all in place. They'll have to be manually added or copied in from some other location.

One Abstraction Further

One issue with this approach is that it doesn't solve the problem of multiple configurations when it comes time to deploy the app.

One hacky way of doing this is to store external configuration files for each build in a separate folder and then copy them in with a build task. To do this:

  • Create a folder somewhere in your solution path root (I use /config)
  • Copy one file for each configuration (ConnectionStrings.config.debug, *.release, *.deploy)
  • Change each file to match your environment.
  • Add post build event to copy the appropriate file from the folder into your project

The post-build task would look like this:

"copy" "$(SolutionDir)config\connectionstrings.config.$(ConfigurationName)"
       "$(ProjectDir)web_ConnectionStrings.config"

Using this approach you'd still have to instruct people to create the files inside of Solution /config folder outside of source control, but the advantage with this is that a) the project has all the files to run (even if the connection strings may be invalid you get an error message to that effect) and b) you can have multiple connection string settings files for the different configurations. So if you need to deploy you can be assured you're deploying with the correct connection string for the live site (or whatever config).

In this scenario you would set up the .debug and .release configurations to your dev setup and the .deploy (and whatever other versions like .staging) to the appropriate settings.

Hacky Stuff

This is all very ugly and just for the sake of source control. For small or personal projects it's easy to bypass this altogether by simply using the same connection strings for development and the server. But when working in even smal teams it's likely you'll run into divergence.

If only server names vary, Server Aliases can simulate a single connection string easily. This works just fine as long as connection strings only vary by server name. If this is possible this is definitely the easiest approach as nothing has to change in the project - you just need the manual connection setup once. Personally for me Server Aliasing works most of the time and it's easy enough to set up for all involved as long as the process is documented.

Back in the days of ODBC connections there was a repository of DSNs that could be declared at the system level, which in light of this particular issues seems like a good way to handle this - set up a connection globally once and then reference that remotely. Unfortunately, AFAIK the .NET universal provider doesn't recognize DSN connections.


The Voices of Reason


 

gerleim
February 28, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

What we often do, with different dev and test databases, is to store and read config keys with suffix _<machinename>.
That's also good for different settings for different dev and test scenarios. You can freely include and check in your own keys without causing headache for others and at source merging.

Rick Strahl
February 28, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

@gerleim - can you elaborate on that? Not quite sure what you mean by this...

PeterTran
February 28, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control


Kenny Eliasson
February 28, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

We also like to append "_[ComputerName]" to the connectionstring. My computer is called E6520 and I therefore call my connectionstring "Database_E6520". In code we look for that first, and if not fallback to the "Database" connection string. I read about it from here http://ayende.com/blog/135169/frictionless-development-web-config-and-connection-strings a couple of years ago.

Rick Strahl
March 01, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

@Kenny - yeah if you're explicitly pulling the connection string in code then there are a number of options like that available. Like the idea of the computer name since that's easily discoverable.

However, we're using EF CodeFirst in this app and trying to stick with the default connection name so no config is needed beyond the connection string, so that trick doesn't work since there's no hook for reading the string first.

gerleim
March 01, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

Hi Rick, same as Kenny wrote, the "_[ComputerName]" approach. We have a wrapper on reading config, and it always checks if any key exists with the computername suffix (sometimes we restrict this check only to debug version to keep release version a bit cleaner and to safeguard miscounfiguration).
Another example: for resource caching, we use a "big" timespan. When we tested the cache algorithm itself, the dev working on it was able to wrote a cacheSec_mymachine = 5 value to check cache invalidation, without interfering with other machines.

KierenH
March 02, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

Everyone runs their own local database instance and we just make sure we give our local instance the same name. We then just use web.config transforms to migrate between environments, but as you say, this only works for web projects.

Slapout
March 08, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

Sounds like a job for a database, oh wait.... :-)

Alex
March 12, 2013

# re: Sql Connection Strings in .Config Files vs. Source Control

I knew you could externalize "AppSettings" (like this <appSettings file="YourSettings.config">) but never came to me that you could do the same with connection strings.

But first of all - thanks for the servername tip, Rick! so simple and obvious.
 

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2017