Encryption/Decryption strategy using .NET and SQL Server

In one of my projects, we had requirement of storing certain confidential fields in database in an encrypted manner. This will ensure that data stored in database will not be accessible to prying eyes and even if someone have access to database directly, they cannot read the data directly unless it is decrypted.

Approach

For implementing the encryption/decryption strategy, we are using the SQL Server inbuilt functions - EncryptByPassPhrase and DecryptByPassPhrase. These functions employ the symmetric encryption strategy. The function requires a Pass-Phrase to encrypt the data. Same pass-phrase is used to decrypt the data also. If pass-phrase used to decrypt data is not exactly same as one used for encryption, then data will not get decrypted.

Following are the steps to be followed to implement the same:-

1. Decide on a pass-phrase which needs to be used for encryption/decryption.
2. Store this pass-phrase in some configuration file preferably web.config/ app.config.
3. Optional - For keeping the pass-phrase secure, encrypt the pass-phrase using the ASP.NET IIS Registration (aspnet_regiis.exe) tool. This is explained in my another post – “Encrypting sections of .NET configuration files”.
4. Decide on the fields which need to be kept in an encrypted manner in database.
5. All these fields should have data-type of VARBINARY(MAX).
6. While inserting/updating data in database, encrypt the required fields using function EncryptByPassPhrase.
7. While retrieving data from database, decrypt the required fields using function DecryptByPassPhrase.

All these steps are explained below.

Storage of Pass-Phrase

Store the pass-phrase in application configuration file (web.config / app.config) as an app-setting key. E.g.

  <appSettings>

    <add key="EncryptionPassPhrase" value="Some complex pass phrase" />

  </appSettings>

Decide on fields to be encrypted

Let’s say, we want to create an employee table with following fields:-

• EmployeeID
• EmployeeName
• DateOfBirth
• SSN (Social Security Number)

Out of these four fields, the last two contains confidential information and hence we want to encrypt them in our database. These fields should have data-type of VARBINARY(MAX) in that case.

Here is how our table should look like:-

Method to Insert Data

While inserting data, we have to encrypt the fields using SQL Server 2005 in-built function EncryptByPassPhrase. This method takes two parameters:-

• Pass-phrase used for encryption
• Actual data which needs to be encrypted.

The output of this function is encrypted string in form of VARBINARY(MAX) which can be directly stored in our database column.

So our insert query will look like this:-

INSERT INTO [Employee]

           ([EmployeeName]

           ,[DateOfBirth]

           ,[SSN])

     VALUES

           (@EmployeeName,

           EncryptByPassPhrase(@PassPhrase, @DateOfBirth),

           EncryptByPassPhrase(@PassPhrase, @SSN)

           )

The complete stored procedure to insert data is shown below:-

CREATE PROCEDURE [dbo].[InsertEmployee]

(

      @EmployeeName NVARCHAR(50),

      @DateOfBirth NVARCHAR(100),

      @SSN NVARCHAR(100),

      @PassPhrase NVARCHAR(100)

)

AS

BEGIN

INSERT INTO [Employee]

           ([EmployeeName]

           ,[DateOfBirth]

           ,[SSN])

     VALUES

           (@EmployeeName,

           EncryptByPassPhrase(@PassPhrase, @DateOfBirth),

           EncryptByPassPhrase(@PassPhrase, @SSN)

           )

          

END

Here, all the employee data viz EmployeeName, DateOfBirth, SSN is passed as input parameter to stored procedure. The pass-phrase is also passed as an input parameter which is subsequently used to encrypt the data before inserting it.

Method to retrieve data

While decrypting data, we have to decrypt the fields using SQL Server 2005 in-built function DecryptByPassPhrase. This method takes two parameters:-

• Pass-phrase used for decryption
• Actual data which needs to be decrypted.

This method returns decrypted data in form of VARBINARY(MAX). This can be type-casted to VARCHAR to make it readable.

Hence our select query will look like this:-

SELECT

[EmployeeId]

,[EmployeeName]

,CONVERT(NVARCHAR(100)
,DecryptByPassPhrase(@PassPhrase, [DateOfBirth])) as DecryptedDOB

,CONVERT(NVARCHAR(100)
,DecryptByPassPhrase(@PassPhrase, [SSN])) as DecryptedSSN

FROM [Employee]

The complete stored procedure to retrieve data in decrypted manner is shown below:-

CREATE PROCEDURE [dbo].[GetEmployees]

      @PassPhrase NVARCHAR(100)

AS

BEGIN

      SET NOCOUNT ON;

SELECT 

[EmployeeId]

,[EmployeeName]

,CONVERT(NVARCHAR(100), DecryptByPassPhrase(@PassPhrase, [DateOfBirth])) as DecryptedDOB

,CONVERT(NVARCHAR(100), DecryptByPassPhrase(@PassPhrase, [SSN])) as DecryptedSSN

FROM [Employee]

END

The stored procedure takes a pass-phrase as an input parameter which is subsequently used to decrypt the data. The pass-phrase which is used to decrypt the data should be same as the one which is used to encrypt it; otherwise the data will not get decrypted.

Disadvantage

Since, this method involve passing pass-phrase as an input parameter to stored procedures, hence it’s possible to see that pass-phrase using SQL Profiler or some other tools. If this seems unsafe, the pass-phrase can be either hard-coded in stored procedures or stored somewhere in database itself. There is another strategy to handle this security flaw by using symmetric key encryption. I will discuss this out in my next post.