CODEWITHSUNDEEP
c#asp.netsecurityencryptionsql-server-2012
DeclanMcD
DeclanMcD

Reputation: 1586

How to encrypt certain columns of certain rows in SQL Server 2012

I have an application that requires certain data columns to be obfuscated or encrypted for security reasons. Not all rows need these columns encrypted, only a certain number.

The data cannot be read by anyone with access to the database but obviously needs to be show in the application. Even then it can only be shown to certain people. All users authenticate via LDAP.

My approach is the following:

Data going in:

  1. Is the data from a data source that requires encryption?
  2. Then encrypt all relevant columns with a secure key and enter into the database

Data coming out within the application:

  1. Is the user authenticated?
  2. Does that user have the relevant security flag enabled?
  3. When showing each data row, check to see if that data row has encrypted data
  4. If it has then decrypt it with a secure key and display on screen.

This approach raises issues about where and how the key is stored and who has access to it.

Even developers or DBAs will not have access to that data unless they are authorised to do so.

I do not want to impact the performance of the application too much, especially if it has to display a couple of hundred records at a time.

The application is in C# with a SQL Server 2012 backend.

What is the best approach to implement this scenario?

Upvotes: 2

Views: 929

Answers (1)

kheld
kheld

Reputation: 792

You can store your key in the registry and encrypt it with DAPI. To make the key more secure, you should use the user and not system DAPI version. See this question. You should also set permissions on the registry key to restrict which users can read the key. You could also store your DAPI encrypted string in a file (web.config).

Be aware that you have to encrypted the string on the machine when logged in as the user. This makes publishing the key harder because you cannot simply copy the DAPI protected key from the development server.

Setup your application pool user to so that it has access to the registry key and is the same user used to encrypt the key with DAPI.

You could possibly make everything more secure by putting the authorization and encryption/decryption into a separate service. Setup the service to use the DAPI user name instead of the application pool user. The tricky part is secure communication between your service and your web site.

Finally, you can make everything more secure by using SecureString for key transport after you decrypt the key using DAPI. You might want to develop everything using String and switch to SecureString so that you can debug. Here is a question with a SecureString example.

For development, simply use a different key than in production. The developers will be able to test the encoding and decoding of information but not decode any production data. Your developers really should not have access to un-sanitized production data anyway.

Upvotes: 2

Related Questions