Configuring Connections and Connecting to Data in Microsoft .NET Framework 3.5

  • 2/25/2009

Lesson 6: Securing Sensitive Connection String Data

Because of the sensitive nature of most data in real-world scenarios, it is extremely important to protect your servers and databases from unauthorized access. To ensure limited access to your data source, it is a best practice to secure information like user IDs, data source names, and, of course, passwords. Storing this type of information as plain text is not recommended because of the obvious security risk. It is also worth noting that plain text saved in compiled applications is easily decompiled, rendering your data accessible by persons with questionable intent.

The suggested method of implementing security in applications that access data is to use Windows Authentication (also known as Integrated Security). To further protect sensitive connection information when using Integrated Security, it is also recommended that you set the Persist Security Information keyword to False in the connection string. This ensures that the credentials used to open the connection are discarded and not stored where someone might be able to retrieve them.

Table 5-12 provides the key/value pairs to set in the connection string for implementing Integrated Security in the four .NET Framework Data Providers.

Table 5-12 Connection String Keywords for Turning On Integrated Security

Data Provider

Key/Value Pair

SqlClient

Integrated Security=True

SqlClient and OleDb

Integrated Security=SSPI

Odbc

Trusted_Connection=Yes

OracleClient

Integrated Security=Yes

As stated earlier, if you absolutely must use a connection string that contains sensitive information, do not store the connection string in the compiled application. As an alternative, you can use the application configuration file (app.config). The app.config file stores connection strings as Extensible Markup Language (XML), and your application gets its connection information by querying this file at run time (as opposed to compiling the connection string into the application itself). By default, the application configuration file stores its information unencrypted, as shown in Figure 5-4.

Figure 5-4

Figure 5-4 An unencrypted configuration file

Securing Data in Configuration Files

Now that you’ve moved your sensitive connection string data out of the compiled application and into the application’s configuration file, the connection string is still unencrypted and can be read by anyone with permission to open the configuration file. Therefore, you still need a way to prevent unauthorized personnel from viewing the connection information if they somehow gain access to your configuration file. The suggested method of securing configuration files is to encrypt the sections that contain sensitive information, as shown in Figure 5-5.

Figure 5-5

Figure 5-5 An encrypted configuration file

The suggested approach to encrypting configuration data is to use a protected-configuration provider. Two protected-configuration providers are available in the .NET Framework, as well as a base class that you can use to implement your own if the two available providers are not sufficient for your application.

Lab: Securing a Configuration File

In this lab you will practice encrypting and decrypting a configuration file.

EXERCISE 1: Encrypting and Decrypting a Configuration File

In this lesson you will see how to use the DpapiProtectedConfigurationProvider to encrypt and decrypt the ConnectionStrings section of the app.config file.

  1. Create a new Windows Application and name it SecuringConnectionStrings.

  2. Add a reference to the System.Configuration namespace.

  3. Add two buttons to the form, setting the Name and Text properties to the following:

    Name Property

    Text Property

    EncryptButton

    Encrypt

    DecryptButton

    Decrypt

  4. Create a data source and add a connection string to the application configuration file by running the Data Source Configuration Wizard.

  5. Create event handlers for the button-click events.

  6. Switch to code view and paste the following code into the editor:

    The following code locates the connection string setting in the application’s configuration file. The connection string setting is marked for encryption by calling the Protect-Section method. Setting the ForceSave property to True ensures the configuration file is saved whether changes are made or not; and the Configuration.Save call saves the file once it has been encrypted.

    ' VB
    Imports System
    Imports System.Configuration
    
    Public Class Form1
        Private Sub EncryptConnectionString()
            ' Get the configuration file
            Dim config As System.Configuration.Configuration = _
                ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
    
            ' Create the provider name
            Dim provider As String = _
                "DataProtectionConfigurationProvider"
    
            ' Encrypt the ConnectionStrings
            Dim connStrings As ConfigurationSection = _
                config.ConnectionStrings
            connStrings.SectionInformation.ProtectSection(provider)
            connStrings.SectionInformation.ForceSave = True
            config.Save(ConfigurationSaveMode.Full)
        End Sub
    
        Private Sub DecryptConnectionString()
            ' Get the configuration file
            Dim config As System.Configuration.Configuration = _
                ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
    
            ' Decrypt the ConnectionStrings
            Dim connStrings As ConfigurationSection = _
                config.ConnectionStrings
            connStrings.SectionInformation.UnprotectSection()
            connStrings.SectionInformation.ForceSave = True
            config.Save(ConfigurationSaveMode.Full)
        End Sub
    
        Private Sub EncryptButton_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles EncryptButton.Click
            EncryptConnectionString()
        End Sub
    
        Private Sub DecryptButton_Click(ByVal sender As System.Object, _
            ByVal e As System.EventArgs) Handles DecryptButton.Click
            DecryptConnectionString()
        End Sub
    End Class
    
    // C#
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Configuration;
    
    namespace SecuringConnectionStrings
    {
        public partial class Form1 : Form
        {
           public Form1()
           {
               InitializeComponent();
           }
    
           private void EncryptConnectionString()
           {
               // Get the configuration file
               System.Configuration.Configuration config =
                   ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.
    None);
    
               // Create the provider name
               string provider = "DataProtectionConfigurationProvider";
    
               //Encrypt the connectionStrings
               ConfigurationSection connstrings = config.ConnectionStrings;
               connstrings.SectionInformation.ProtectSection(provider);
               connstrings.SectionInformation.ForceSave = true;
               config.Save(ConfigurationSaveMode.Full);
           }
    
           private void DecryptConnectionString()
           {
               //Get the configuration file
               System.Configuration.Configuration config =
                   ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.
    None);
    
               // Decrypt the connectionStrings
               ConfigurationSection connstrings = config.ConnectionStrings;
               connstrings.SectionInformation.UnprotectSection();
               connstrings.SectionInformation.ForceSave = true;
               config.Save(ConfigurationSaveMode.Full);
           }
    
           private void EncryptButton_Click(object sender, EventArgs e)
           {
               EncryptConnectionString();
           }
    
           private void DecryptButton_Click(object sender, EventArgs e)
           {
               DecryptConnectionString();
           }
    
        }
    
    }
  7. Run the application and click the Encrypt button.

  8. While the application is running, navigate to the project’s folder and locate the configuration file (SecuringConnectionStrings.vshost.exe.config).

  9. Open the file and verify that the ConnectionStrings section is encrypted.

  10. Go back to the form and click the Decrypt button.

  11. Reopen the .config file and notice that the connection string has reverted back to plain text.

Lesson Summary

  • Windows Authentication (also called Integrated Security) is the suggested method for connecting to data securely.

  • Store connection strings that contain sensitive information in the application configuration file and encrypt all settings that contain confidential information.

Lesson Review

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

  1. What is the connection string’s key/value pair for using Windows Authentication in SQL Server 2000 and SQL Server 2005? (Choose all that apply.)

    1. Integrated Security = yes

    2. Integrated Security =SSPI

    3. Integrated Security = True

    4. Trusted_Connection = Yes

  2. If you must use a user name and password to connect to a database, where should you store the sensitive information?

    1. Compiled in the application

    2. In an encrypted application configuration file

    3. In a resource file deployed with the application

    4. In the registry

  3. What is the recommended method for securing sensitive connection string information?

    1. Encrypting the data in the application configuration file

    2. Using a code obfuscator

    3. Using Integrated Security (Windows Authentication)

    4. Querying the user for his or her credentials at run time