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

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.

REAL WORLD

Steve Stein

In another of my previous jobs (okay, I’ve had a few!), I took a position as a system administrator for a local mortgage company. My first task was to get familiar with the infrastructure of their company network. I immediately realized that basically every employee was set up with an administrator account and had access to the entire network. Although this story isn’t specific to securing connection strings, it does provide insight into how important it is to lock down your sensitive data!

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

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