Encrypt Connection Strings

Introduction

One of the problems that exists in database programming today is how to keep database Connection String information from prying eyes. Typically the Connection String is stored inside the application's XML-based "config" file. There are generally 5 ways to attack this problem, each with some significant drawbacks.

Don't put sensitive data in the configuration file in the first place. Use the database's "Integrated Security" feature instead, where each user is authenticated using their own credentials. That's probably the Microsoft "party line"... but not useful when you want to control access via your program and not allow direct access to the database at all.
The next approach is used in web programming, where the problem is easily solved by using the Data Protection API (DPAPI). However, since DPAPI uses the machine-based certificates of the web server to do the encryption/decryption, that's not a viable option for Windows Form application (since we don't know the PC's certificate). Once configured, the connection strings are no longer readable at design time.
A very clever approach to solving this problem is to use a custom installer that will encrypt portions of the config file during the installation process using the target PC's certificate and DPAPI. But that requires you to install the application on each PC. In my world, we have a shared network location where we keep all of our locally-developed applications. We do not install them on the individual PCs.
Another approach is to just use your own encryption/decryption routines in your application to either obscure or encrypt the information. This works fairly well (and is the technique I've used in my SOSOS program), but it causes problems with Visual Studio. In particular, if you use a strongly-typed DataSet with TableAdapters, that portion of your application that is automatically generated by Visual Studio doesn't know to use your custom encryption routines. If you try this, chaos and panic ensues and Visual Studio can corrupt your application!
The last approach, described here, uses a custom "Setting Provider" and custom "Build Event" utility to obscure the Connection String. It leaves the Connection String as clear text during development and obscures the Connection String at runtime.

So, if you A) want to limit direct access to the database, B) use "WinForm" applications, C) don't install your programs locally, and D) use TableAdapters, then you might consider the custom "Setting Provider/Build Event" technique below.

Clarification

Before I begin, a note of clarification is in order... the technique I'm about to describe is NOT a security measure. A true security measure would imply that the technique would provide reasonable protection against a hacker. That is not the case here. Instead this technique will merely obscure the information from all but the most determined users.

I'm not gonna tell you what the weakness are to this approach, but trust me...

The Custom Settings Provider/Build Event Technique

Let's start at the beginning... Typically you store configuration-based information such as a Connection String in the application's "config" file. This can be done by editing the "app.config" file directly, or by using Visual Studio's "My Project" (from the Solution Explorer) as shown below:

Note: If you created a strongly-typed dataset via the designer, then Visual Studio itself may have automatically created the Connection String entry in the Setting section of "My Project".

Using the example above, the resulting XML-based app.config file now has an entry that looks like this:

<connectionStrings>
  <add name="TestApp.My.MySettings.dbConnectionString" connectionString= _
   "Data Source=SomeServer;Initial Catalog=SomeDatabase;User ID=SomeUser; _
    Password=SomePassword"
    providerName="System.Data.SqlClient" />
</connectionStrings>

You can probably see why you wouldn't want to leave this information in an easily readable format. Anyone can view the contents of this text file and get all of the information they need (such as the Server name, User ID and Password) to gain direct access to the database.

Living In Two Worlds

Part of the problem is that your application actually lives in "two worlds"... the development environment and the run-time environment. For this trick to work, we need to leave the Connection String as "clear text" during development, and only convert it to "obscure text" at run time.

This is required because Visual Studio will use the "clear text" version of the Connection String whenever you make changes to the DataSet via the DataSet designer.

Luckily, Visual Studio provides separate configuration settings for each world. During the development environment, the configuration settings are stored in a file called "app.config". During the run-time environment, the settings are copied to a new file called "<YourApplicationName>.exe.config".

So that's where the custom "Build Event" comes in. We use a command-line utility to transform the "clear text" in the "<YourApplicationName>.exe.config" file to "obscure text" whenever the application is compiled.

The last part of the puzzle is to tell the application how to convert the "obscure text" back to "clear text". This is done via a custom Settings Provider that you configure via the "My Project/Settings" tab.

Part 1 - The Build Event Utility

We need a command-line utility that will be used inside Visual Studios as a custom Build Event to alter the applications config file every time the Visual Basic project is compiled. This application needs to have two command-line arguments... one is the location of the config file and the second is the name of the setting to obscure.

So, let's get started. Let's create a new console application called Base64Settings that converts clear text into Base64 encoded text.

Imports System.Xml
Imports System.Text

Module Main
    Sub Main()
        Dim xd As New XmlDocument
        Dim xnAltered, xnConnect As XmlNode
        Dim ConfigPath, SettingName As String
        Dim test As Boolean

        ' a quick sanity check
        If My.Application.CommandLineArgs.Count <> 2 Then
            Throw New ArgumentException("Required command-line parameters not provided")
        End If

        ConfigPath = My.Application.CommandLineArgs(0)
        SettingName = My.Application.CommandLineArgs(1)

        ' load the config file
        xd.Load(ConfigPath)

        ' let's see if this config file has already been altered
        xnAltered = xd.SelectSingleNode("//setting[@name=""Altered""]")
        If IsNothing(xnAltered) OrElse Boolean.TryParse(xnAltered.InnerText, test) = False Then
            Throw New ApplicationException("Required 'Altered' Setting not found")
        End If

        If CBool(xnAltered.InnerText) = False Then
            ' find our connection string
            xnConnect = xd.SelectSingleNode("//add[@name=""" & SettingName & """]")

            If IsNothing(xnConnect) Then
                Throw New ApplicationException("Required '" & SettingName & "' Setting not found")
            End If

            For Each at As XmlAttribute In xnConnect.Attributes
                ' find the attribute we want and change its value
                If at.Name = "connectionString" Then
                    at.Value = Convert.ToBase64String(Encoding.ASCII.GetBytes(at.Value))
                End If
            Next

            ' mark it as altered and save the changes
            xnAltered.InnerXml = "<value>True</value>"
            xd.Save(ConfigPath)
        End If
    End Sub
End Module

After you've compiled this program, you should copy it to a location on your PC so you can use it as a custom Build Event in another Visual Studio project (See Part 3).

Note: This utility is only required on the development PC, so there's no need to distribute it to the client PCs.

Part 2 - The Settings Provider

In Part 1, we created a utility that will obscure the data, but now we need something that will perform the reverse operation. However, this time this feature needs to be a part of the application (instead of a stand-alone utility).

In this example, we are just using a Base64 string to obscure the data... a more complex example using encryption is available in the downloadable example.

' Add a reference to System.Configuration
Imports System.Configuration
Imports System.Collections

Public Class Base64SettingsProvider
    Inherits SettingsProvider

    Private AppName As String

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As _
     Specialized.NameValueCollection)
        If String.IsNullOrEmpty(name) Then
            name = My.Application.Info.ProductName
        End If
        AppName = name

        MyBase.Initialize(AppName, config)
    End Sub

    Public Overrides Property ApplicationName() As String
        Get
            Return AppName
        End Get
        Set(ByVal value As String)
            AppName = value
        End Set
    End Property

    Public Overrides Function GetPropertyValues(ByVal context As SettingsContext, _
     ByVal properties As SettingsPropertyCollection) As SettingsPropertyValueCollection
        Dim spvc As SettingsPropertyValueCollection
        Dim lfsp As New LocalFileSettingsProvider

        ' Get the values from the "real" provider
        spvc = lfsp.GetPropertyValues(context, properties)

        For Each spv As SettingsPropertyValue In spvc
            ' convert the value from a Base64 string
            spv.PropertyValue = System.Text.Encoding.ASCII.GetString( _
             Convert.FromBase64String(spv.PropertyValue.ToString))
        Next

        Return spvc
    End Function

    Public Overrides Sub SetPropertyValues(ByVal context As SettingsContext, _
     ByVal values As SettingsPropertyValueCollection)
        Dim lfsp As New LocalFileSettingsProvider

        For Each spv As SettingsPropertyValue In values
            ' convert the value into a Base64 string
            spv.PropertyValue = Convert.ToBase64String( _
             System.Text.Encoding.ASCII.GetBytes(spv.PropertyValue.ToString))
        Next

        ' pass it on to the "real" provider
        lfsp.SetPropertyValues(context, values)
    End Sub

End Class

Part 3 - Putting the Pieces Together

You've probably used the My Project/Settings "tab" quite a few times, but never really looked at the properties box. Each of the Settings has a Provider property (which is normally blank). For each property that you want to obscure, you should change this property to use the name of the provider you created above. You should also change the GenerateDefaultValueInCode property to False.

Note: For some odd reason, you can only have one custom provider per project.

Likewise, you've probably never really looked at the Build Event button on the My Project/Compile tab.

Note: The Build Events button is not available in the free Express Editions of Visual Basic (but you can code it by hand inside the vbproj file)

When clicked, you can add a batch file or command line utility as a "Post-Build" event that will run each time the application is compiled.

In our case we need to use the utility created in Part 1. The complete command line would be something like this:

C:\Utils\Base64Settings "$(TargetPath).config" "TestApp.My.MySettings.dbConnectionString"

...where the 1st argument is a Visual Studio macro that points to the location where the compiler will put the application. The 2nd argument is the name of the setting as it appears in the configuration file.

Lastly, you need to add a Setting to your application that allows the Build Event utility to determine if the config file has already been altered. There are probably a dozen ways to do this, but I chose to add a boolean Setting called "Altered" to the project:

You leave this value as False as the Built Event utility will change it to True after it has converted the connection string.

Conclusion

This technique uses two separate parts, a Build Event utility to encrypt/obscure, and a Settings Provider to decrypt/un-obscure Together they provide a unique solution that allows you to use a clear-text Connection String at design and to keep that Connection String from prying eyes at run time.

OK, I'll admit... this technique is a bit convoluted to setup the first time, but it does solve the problem!

Downloads/Links

Download the VB.Net Source code example used in this article: SettingsProvider.zip
Read a related article on Simple Encryption/Decryption