Introduction

This article will provide a class project for encrypting and decrypting defined connection strings to access database data where the connection string may have been set up when creating classes from working with Visual Studio data wizards which create TableAdapter solutions or for solutions utilizing data access through a managed data provider (SqlClient, OleDb, Oracle).

Reasons for securing connections

A main consideration for securing connection strings is to prevent malicious access to data from a hacker or to prevent a user from manipulating data with good intent with a lack of understanding the consequences of changing, adding or removal of data in a relational database.

A standard connection string in app.config exposes all someone needs to gain access to your database. Here the server name has been exposed.


When a connection string is encrypted the common hacker or user most likely will not attempt to figure out what the data presented gets them. This will not stop a hacker who has skills for hacking.



Securing connection strings

From the provided source code
  1. Copy the project ConfigurationLibrary_vb to your Visual Studio solution. You can change the project name along with the project namespace before proceeding.
  2. Add the project to the solution from Solution Explorer
    1. Right click on solution explorer.
    2. Select add an existing project.
    3. Traverse to the project, select and press OK.
  3. For your project
    1. Add a reference to ConfigurationLibrary_vb
    2. Save changes
    3. Create a new private instance of ConnectionProtection class (as shown in figure 1)
  4. For TableAdapter projects rig Form load as shown in figure 2 a while for managed data providers see figure 2b.
Figure 1

Private operations As New ConnectionProtection(Application.ExecutablePath)

Figure 2a

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    If Not operations.IsProtected() Then
        operations.EncryptFile()
    End If
 
    operations.DecryptFile()
    Me.PeopleTableAdapter.Fill(Me.PeopleDataSet.People)
    operations.EncryptFile()
End Sub

Figure 2b

Imports System.Data.SqlClient
Imports BaseLibrary
Imports ConfigurationLibrary_vb
Public Class DataOperations
    Inherits BaseExceptionsHandler
 
    Private operations As New ConnectionProtection(Application.ExecutablePath)
    Public Sub New()
        If Not operations.IsProtected() Then
            operations.EncryptFile()
        End If
    End Sub
    ''' <summary>
    ''' Read data from SQL-Server database where the connection string has been setup
    ''' under My.Settings of type Connection String.
    ''' </summary>
    ''' <returns>A DataTable representing people data or on failure an empty DataTable</returns>
    Public Function ReadPeople() As DataTable
        Dim dt As New DataTable
        Const selectStatement = "SELECT Identifier, FirstName, LastName FROM dbo.People"
 
        operations.DecryptFile()
 
        Using cn = New SqlConnection(My.Settings.MainConnection)
            Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
                Try
                    cn.Open()
                    dt.Load(cmd.ExecuteReader())
 
                Catch ex As Exception
                    mHasException = True
                    mLastException = ex
                Finally
                    operations.EncryptFile()
                End Try
            End Using
        End Using
 
        Return dt
 
    End Function
End Class

Breaking down code provided above


IsProtected property determines if the connection string is encrypted, if not encrypt the connection string. This needs to run once prior to installing the application. To work with data e.g. read, update, insert and remove DecryptFile needs to be called to get the unencrypted connection string then once finished call EncryptFile to re-encrypt the configuration file.

Potential issues

While performing a data operations a runtime exception is thrown without proper assertion in the form of a try/catch/finally statement the configuration file will be left exposed. The worst case is power is lost and the application crashes with an exposed configuration file. No matter, it's better to have encryption than not to have encryption.

This can be circumvented by ensuring the file is encrypted (except for power lose) by checking if the config file is encrypted at program end under My Application events as the code in the article has a test to see if the connection string is encrypted.

Sample code

Source code is provided for running encrypt/decrypting connection string against SQL-Server.  Before running the samples the following needs to be performed.
  1. Create the database, table, and data by running DatabaseTableScripts.sql included with source code. This can be done using Visual Studio by running the SQL statements in a query window (Visual Studio defaults to this).
  2. In both projects change the server instance name from KARENS-PC to the name of your server or if SQL-Express use .\SQLEXPRESS.

See also

Summary

In this article, a library has been presented to encrypt connection strings in Windows forms projects which work with any managed data provider. This provides a level of security that is much better than no security at all which is what a standard connection string is, unsecured.

For even better security see the TechNet article SQL-Server database login for Windows Forms (VB.NET) which provides insight on how to create a secure login for SQL-Server. There are two options provided in the article where one may be used in any project while the other requires Framework 4.5.

Requires

Source code