Leon's Weblog

October 20, 2013

Encrypting Data in SQL Server Table

Filed under: Software Dev — Leon @ 4:07 pm

Encryption I was working on a project where I needed to store connection strings in a database table for use inside of a reporting engine. SQL Server offers many encryption options from encrypting entire databases to hashing individual strings so it took me some time to come up with an ideal solution. Hashing was not an option for me because I needed to be able to read back the connection strings in clear text before passing them to the reporting engine. Encrypting the entire database seemed like overkill and would create an unnecessary performance hit. Ultimately, I was designing a method to protect the data in one field of a table from users who may potentially have read-access to the database.

The solution that I settled on was to use the built-in ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE functions in SQL Server and storing the connection strings encrypted in the table. I also considered performing the encryption on the application end (in .NET) but realized that it would limit my ability to read the encrypt data from a different front-end. Using SQL Server’s built in functions provided the most flexibility and it is also a good practice to secure data as close to where it is stored as possible (to avoid the potential of exposing the string in clear text). The key to using these functions is to choose a good Pass Phrase and to store it securely. It is convenient to make a user defined function on the SQL Server that will get the Pass Phase somehow (e.g. hard coded in the function, stored in the registry etc…) but this is a very bad idea. Anyone with access to the user defined function on the SQL Server will be able to read the data so you might as well not bother encrypting it (security by obscurity is weak). My approach was to store the Pass Phrase in the application configuration file and then to encrypt that section of the configuration file. My web.config file looks like this.

  <configSections>
    <section name="secureAppSettings" type="System.Configuration.NameValueSectionHandler, 
      System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"/>
  </configSections>
  <appSettings>
    <add key="ClearTextSetting1" value="value 1"/>
  </appSettings>
  <secureAppSettings>
    <add key="PassPhrase" value="My Application Pass Phrase...HT6YB4MO=2GFR&F"/>
  </secureAppSettings>

The benefit to this approach is that most of the configuration section is split into two parts. One is clear text and is easy to modify. The other is encrypted using aspnet_regiis and can only be read by an administrator on the host server. To look-up the Pass Phrase from the configuration file use ConfigurationManager as follows (if this line gives you an error make sure you add the reference to System.Configuration in your project):

Dim PassPhrase As String = DirectCast(ConfigurationManager.GetSection("secureAppSettings"), NameValueCollection)("PassPhrase")

Then to decrypt the data from the database, build a SQL command like this:

Dim SQL As String = "SELECT ReportFile, CONVERT(VARCHAR(50), DECRYPTBYPASSPHRASE('" & _
                    PassPhrase & "', Connection)) Connection " & _
                    "FROM tblReportDefinition WHERE ReportID=" & ReportID

Now you can sleep better at night knowing that your application is a little more secure.

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a comment