Click here to Skip to main content
Click here to Skip to main content
Add your own
alternative version

Securing ADO.NET Connection Strings

, 25 Aug 2006
Some possible ways to encrypt and store connection strings in an ADO.NET application.
secureconnectionstring_demo.zip
Secure ADO.NET Connection String_demo
Secure ADO.NET Connection String.exe
secureconnectionstring_src.zip
Secure ADO.NET Connection String_src
My Project
Application.myapp
Settings.settings
Imports System.IO
Imports System.Xml
Imports System.Security.Cryptography.Xml
Imports System.Security.Cryptography
Imports System.Configuration
Imports Microsoft.Win32
Imports System.Text

Public Class Form1
    Dim myDbParameters As New DateBaseParameters
    Dim ConnStr As String = ""
    Dim ProgramName As String = "MyApplication"
    Dim encryptMethod As String = ""

    Private Function GetConnectionString() As String
        'Define SQL ConnectionStringBuilder for constructing 
        'the Connection string
        Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
        With builder
            .DataSource = myDbParameters.DataSource
            .InitialCatalog = myDbParameters.InitialCatalog
            .IntegratedSecurity = myDbParameters.IntegratedSecurity
            .Password = myDbParameters.Password
            .PersistSecurityInfo = myDbParameters.PersistSecurityInfo
            .UserID = myDbParameters.UserID
            .WorkstationID = myDbParameters.WorkstationID
        End With
        GetConnectionString = builder.ConnectionString
    End Function
    Private Sub EncryptSaveInXML(ByVal ConnStrValue As String)
        'If not exists, create new CS.xml file, where you will store
        'encrypted connection string
        If File.Exists(My.Application.Info.DirectoryPath + "\CS.xml") = True Then
            File.Delete(My.Application.Info.DirectoryPath + "\CS.xml")
        End If
        Dim SW As StreamWriter
        Dim FS As FileStream
        FS = New FileStream(My.Application.Info.DirectoryPath + "\CS.xml", FileMode.Create)
        SW = New StreamWriter(FS)
        SW.WriteLine("<?xml version=" + Chr(34) + "1.0" + Chr(34) + " encoding=" + Chr(34) + "utf-8" + Chr(34) + " ?>")
        SW.WriteLine("<connectionString/>")
        SW.Close()
        FS.Close()


        Dim xmlDoc As New XmlDocument()

        ' Load an XML file into the XmlDocument object.
        Try
            xmlDoc.PreserveWhitespace = True
            xmlDoc.Load(My.Application.Info.DirectoryPath + "\CS.xml")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Dim rsaKey As New RSACryptoServiceProvider()
        rsaKey = Encrypt_Decrypt.GetKeyFromContainer("MyKeyContainer")

        Encrypt_Decrypt.Decrypt(xmlDoc, rsaKey, "rsaKey")

        'Create a new attribute.
        Dim newAttr_ConnStrValue As XmlAttribute = xmlDoc.CreateAttribute("ConnStrValue")
        newAttr_ConnStrValue.Value = ConnStr

        'Create an attribute collection and add the new attribute
        'to the collection.  
        Dim attrColl As XmlAttributeCollection = xmlDoc.DocumentElement.Attributes
        attrColl.Append(newAttr_ConnStrValue)

        ' Delete the key from the container.
        Encrypt_Decrypt.DeleteKeyFromContainer("MyKeyContainer")

        ' Create a new RSA key.  This key will encrypt a symmetric key,
        ' which will then be imbedded in the XML document.  
        rsaKey = New RSACryptoServiceProvider()

        ' Create a key and save it in a container.
        rsaKey = Encrypt_Decrypt.GenKey_SaveInContainer("MyKeyContainer")

        Try
            ' Encrypt the "connectionStrings" element.
            Encrypt_Decrypt.Encrypt(xmlDoc, "connectionString", rsaKey, "rsaKey")

            xmlDoc.Save(My.Application.Info.DirectoryPath + "\CS.xml")
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            ' Clear the RSA key.
            rsaKey.Clear()
        End Try
    End Sub
    Private Sub RetrieveDecryptFromXML()
        If System.IO.File.Exists(My.Application.Info.DirectoryPath + "\CS.xml") = False Then
            ConnStr = ""
            Exit Sub
        End If
        Dim xmlDoc As New XmlDocument()

        ' Load an XML file into the XmlDocument object.
        Try
            xmlDoc.PreserveWhitespace = True
            xmlDoc.Load(My.Application.Info.DirectoryPath + "\CS.xml")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Dim rsaKey As New RSACryptoServiceProvider()
        rsaKey = Encrypt_Decrypt.GetKeyFromContainer("MyKeyContainer")
        Encrypt_Decrypt.Decrypt(xmlDoc, rsaKey, "rsaKey")
        ConnStr = ""
        ConnStr = xmlDoc.GetElementsByTagName("connectionString").Item(0).Attributes(0).Value
    End Sub
    Private Sub EncryptSaveInConfig(ByVal ConnStrValue As String)
        Dim config As System.Configuration.Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
        Dim xmlDoc As New XmlDocument()

        ' Load an app.config file into the XmlDocument object.
        Try
            xmlDoc.PreserveWhitespace = True
            xmlDoc.Load(config.FilePath.ToString)
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try

        Dim rsaKey As New RSACryptoServiceProvider()
        rsaKey = Encrypt_Decrypt.GetKeyFromContainer("MyKeyContainer")
        Encrypt_Decrypt.Decrypt(xmlDoc, rsaKey, "rsaKey")

        Dim el As XmlElement = xmlDoc.GetElementsByTagName("connectionStrings")(0)
        If Not el Is Nothing Then
            el.RemoveAll()
        End If
        xmlDoc.Save(config.FilePath.Trim)

        ' Update the application configuration file.
        config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)

        Dim csSection As ConnectionStringsSection = config.GetSection("connectionStrings")
        csSection.ConnectionStrings.Clear()
        csSection.ConnectionStrings.Add(New ConnectionStringSettings("ConnStr", ConnStrValue, "SqlClient"))

        ' Save the configuration file.
        config.Save(ConfigurationSaveMode.Modified)

        ' Create an XmlDocument object.
        xmlDoc = New XmlDocument()

        ' Load an app.config file into the XmlDocument object.
        Try
            xmlDoc.PreserveWhitespace = True
            xmlDoc.Load(config.FilePath.Trim)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        ' Delete the key from the container.
        Encrypt_Decrypt.DeleteKeyFromContainer("MyKeyContainer")

        ' Create a new RSA key.  This key will encrypt a symmetric key,
        ' which will then be imbedded in the XML document.  
        rsaKey = New RSACryptoServiceProvider()

        ' Create a key and save it in a container.
        rsaKey = Encrypt_Decrypt.GenKey_SaveInContainer("MyKeyContainer")

        Try
            ' Encrypt the "connectionStrings" element.
            Encrypt_Decrypt.Encrypt(xmlDoc, "connectionStrings", rsaKey, "rsaKey")
            'Save an XmlDocument as app.config file
            xmlDoc.Save(config.FilePath.Trim)
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            ' Clear the RSA key.
            rsaKey.Clear()
        End Try
    End Sub
    Private Sub RetrieveDecryptFromConfig()
        Dim config As System.Configuration.Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
        Dim xmlDoc As New XmlDocument()

        ' Load an app.config file into the XmlDocument object.
        Try
            xmlDoc.PreserveWhitespace = True
            xmlDoc.Load(config.FilePath.ToString)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Dim rsaKey As New RSACryptoServiceProvider()
        rsaKey = Encrypt_Decrypt.GetKeyFromContainer("MyKeyContainer")
        Encrypt_Decrypt.Decrypt(xmlDoc, rsaKey, "rsaKey")

        ConnStr = ""
        'Retrieve connection string
        Dim i, j As Int32
        For i = 0 To xmlDoc.GetElementsByTagName("connectionStrings").Count - 1
            For j = 0 To xmlDoc.GetElementsByTagName("connectionStrings").Item(i).ChildNodes.Count - 1
                If xmlDoc.GetElementsByTagName("connectionStrings").Item(i).ChildNodes.Item(j).Name = "add" Then
                    If xmlDoc.GetElementsByTagName("connectionStrings").Item(i).ChildNodes.Item(j).Attributes(0).Value = "ConnStr" Then
                        ConnStr = xmlDoc.GetElementsByTagName("connectionStrings").Item(i).ChildNodes.Item(j).Attributes(1).Value
                    End If
                End If
            Next j
        Next i
    End Sub
    Private Sub EncryptSaveInRegistry(ByVal ConnStrValue As String)
        Dim regKey As RegistryKey
        regKey = Registry.LocalMachine.OpenSubKey("SOFTWARE", True)
        regKey.CreateSubKey(ProgramName + "\")
        regKey.Close()

        regKey = Registry.LocalMachine.OpenSubKey("Software\" + ProgramName + "\", True)
        regKey.SetValue("ConnStr", des.Encrypt(ConnStr))
        regKey.Close()
    End Sub
    Private Sub RetrieveDecryptFromRegistry()
        Dim regKey As RegistryKey
        Dim encryptedConnectionString As String
        regKey = Registry.LocalMachine.OpenSubKey("Software\" + ProgramName + "\", False)

        encryptedConnectionString = regKey.GetValue("ConnStr", "")
        regKey.Close()
        If encryptedConnectionString <> "" Then
            ConnStr = des.Decrypt(encryptedConnectionString)
        Else
            MsgBox("There is't stored connection string in the registry.", MsgBoxStyle.Information)
        End If

    End Sub
#Region "DES"
    ' define the local key and vector byte arrays
    Private ReadOnly key() As Byte = _
      {5, 4, 45, 4, 45, 51, 7, 8, 9, 56, 11, 182, 2, 12, _
      15, 16, 57, 18, 45, 20, 21, 22, 23, 3}
    Private ReadOnly iv() As Byte = {8, 3, 6, 5, 54, 2, 1, 32}

    'instantiate the class with the arrays
    Private des As New cTripleDES(Key, IV)

    Friend Class cTripleDES

        ' define the triple des provider
        Private m_des As New TripleDESCryptoServiceProvider

        ' define the string handler
        Private m_utf8 As New UTF8Encoding

        ' define the local property arrays
        Private m_key() As Byte
        Private m_iv() As Byte

        Public Sub New(ByVal key() As Byte, ByVal iv() As Byte)
            Me.m_key = Key
            Me.m_iv = IV
        End Sub

        Public Function Encrypt(ByVal input() As Byte) As Byte()
            Return Transform(input, m_des.CreateEncryptor(m_key, m_iv))
        End Function

        Public Function Decrypt(ByVal input() As Byte) As Byte()
            Return Transform(input, m_des.CreateDecryptor(m_key, m_iv))
        End Function

        Public Function Encrypt(ByVal text As String) As String
            Dim input() As Byte = m_utf8.GetBytes(text)
            Dim output() As Byte = Transform(input, _
                            m_des.CreateEncryptor(m_key, m_iv))
            Return Convert.ToBase64String(output)
        End Function

        Public Function Decrypt(ByVal text As String) As String
            Dim input() As Byte = Convert.FromBase64String(text)
            Dim output() As Byte = Transform(input, _
                             m_des.CreateDecryptor(m_key, m_iv))
            Return m_utf8.GetString(output)
        End Function

        Private Function Transform(ByVal input() As Byte, _
            ByVal CryptoTransform As ICryptoTransform) As Byte()
            ' create the necessary streams
            Dim memStream As MemoryStream = New MemoryStream
            Dim cryptStream As CryptoStream = New _
                CryptoStream(memStream, CryptoTransform, _
                CryptoStreamMode.Write)
            ' transform the bytes as requested
            cryptStream.Write(input, 0, input.Length)
            cryptStream.FlushFinalBlock()
            ' Read the memory stream and convert it back into byte array
            memStream.Position = 0
            Dim result(CType(memStream.Length - 1, System.Int32)) As Byte
            memStream.Read(result, 0, CType(result.Length, System.Int32))
            ' close and release the streams
            memStream.Close()
            cryptStream.Close()
            ' hand back the encrypted buffer
            Return result
        End Function

    End Class

#End Region


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        PropertyGrid.SelectedObject = myDbParameters
        With myDbParameters
            .DataSource = ""
            .InitialCatalog = ""
            .IntegratedSecurity = False
            .Password = ""
            .PersistSecurityInfo = False
            .UserID = ""
            .WorkstationID = ""
        End With
        PropertyGrid.Refresh()
    End Sub
    Private Sub btnEncrypt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEncrypt.Click
        ConnStr = GetConnectionString()
        If rbConfig.Checked Then
            EncryptSaveInConfig(ConnStr)
            encryptMethod = "Config"
        ElseIf rbXML.Checked Then
            EncryptSaveInXML(ConnStr)
            encryptMethod = "XML"
        ElseIf rbRegistry.Checked Then
            EncryptSaveInRegistry(ConnStr)
            encryptMethod = "Registry"
        End If
    End Sub
    Private Sub btnDecrypt_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDecrypt.Click
        If rbConfig.Checked Then
            If encryptMethod = "Config" Then
                RetrieveDecryptFromConfig()
            Else
                MsgBox("Please, encrypt and store the connection string by this method at first.", MsgBoxStyle.Information)
                Exit Sub
            End If
        ElseIf rbXML.Checked Then
            If encryptMethod = "XML" Then
                RetrieveDecryptFromXML()
            Else
                MsgBox("Please, encrypt and store the connection string by this method at first.", MsgBoxStyle.Information)
                Exit Sub
            End If
        ElseIf rbRegistry.Checked Then
            If encryptMethod = "Registry" Then
                RetrieveDecryptFromRegistry()
            Else
                MsgBox("Please, encrypt and store the connection string by this method at first.", MsgBoxStyle.Information)
                Exit Sub
            End If
        End If

        Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder(ConnStr)
        With myDbParameters
            .DataSource = builder.DataSource
            .InitialCatalog = builder.InitialCatalog
            .IntegratedSecurity = builder.IntegratedSecurity
            .Password = builder.Password
            .PersistSecurityInfo = builder.PersistSecurityInfo
            .UserID = builder.UserID
            .WorkstationID = builder.WorkstationID
        End With

        PropertyGrid.SelectedObject = myDbParameters
        PropertyGrid.Refresh()
    End Sub
    Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
        myDbParameters.DataSource = ""
        myDbParameters.InitialCatalog = ""
        myDbParameters.IntegratedSecurity = False
        myDbParameters.Password = ""
        myDbParameters.PersistSecurityInfo = False
        myDbParameters.UserID = ""
        myDbParameters.WorkstationID = ""

        PropertyGrid.SelectedObject = myDbParameters
        PropertyGrid.Refresh()
    End Sub
End Class

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

Levan Midodashvili
Web Developer
Georgia Georgia
Lecturer in Gori University (Georgia)

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 25 Aug 2006
Article Copyright 2006 by Levan Midodashvili
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid