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.
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.
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!