Encryption/Decryption strategy using Symmetric Keys

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 had previously used symmetric encryption strategy using the SQL Server 2005 inbuilt functions - EncryptByPassPhrase and DecryptByPassPhrase. This strategy is fully explained in my earlier post - Encryption/DecryptionStrategy using .NET and SQL Server.

The earlier strategy of encryption by pass-phrase was not secure enough due to following reasons:-

• The encryption/decryption was dependent on a pass-phrase which needs to be stored and managed.
• The pass-phrase was stored in application configuration file. It then needs to be passed to required stored procedures as an input parameter in clear text format. This can pose a security risk.

Keeping these risks in mind, I have come up with a new encryption strategy which addresses the security risks of earlier approach. The strategy is as follows:-

• A database master key should be created and managed by database administrator.
• Create a certificate without specifying any “ENCRYPTION BY” option. In this way, the certificate will be protected in the database by database master key.
• Create a symmetric key which will be used to encrypt and decrypt the data. Encrypt the symmetric key using the certificate created above. Use encryption algorithm “RC4” or “AES256” depending on your security needs. This encryption will ensure that symmetric key is safe and not everyone can open it.
• While encrypting data, open the symmetric key using certificate and encrypt the data using the symmetric key. Close the symmetric key once data is encrypted.
• While decrypting data, open the symmetric key using certificate and decrypt the data. Close the symmetric key once data is decrypted.

Implementation

• A stored procedure has been written to open the symmetric key with the help of certificate. The stored procedure itself is encrypted while creating it using the “WITH ENCRYPTION” clause.
• While executing any procedure to encrypt/decrypt data, we will execute two procedures in a batch.
• Procedure to open the key
• Procedure to encrypt/decrypt data

Since these two steps will be performed in a database connection, the symmetric key will get closed automatically as soon as the database connection closes.

Sample Code and DB Scripts

DB-Script to create keys and certificates

--Create Database master key if it does not exist

--This is to be done by DBA

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Some complex password'

--Create a certificate which will be used to encrypt the symmetric key

IF NOT EXISTS(select 1 from sys.certificates where name = 'TestEncryptionCertificate')

      CREATE CERTIFICATE TestEncryptionCertificate

      WITH SUBJECT = 'Test Certificate for Encryption'

GO

--Create a symmetric key which will be used to encrypt the data

--This key is encrypted by certificate created above

IF NOT EXISTS(select 1 from sys.symmetric_keys where name = 'TestSymmetricKey')

      CREATE SYMMETRIC KEY TestSymmetricKey

      WITH ALGORITHM = RC4

      ENCRYPTION BY CERTIFICATE TestEncryptionCertificate

GO

Note - Since the decryption of data is dependent on the symmetric key, certificate and database master key, hence database administrator is advised to take their backup after creating them.

Stored procedure to open the key

CREATE PROCEDURE [dbo].[OpenSymmetricKey] WITH ENCRYPTION

AS

BEGIN

--Open the key only if is not open

IF NOT EXISTS

      (SELECT 1

         FROM sys.openkeys

         WHERE key_name = 'TestSymmetricKey'

         AND database_name = db_name()

    )

     

      OPEN SYMMETRIC KEY TestSymmetricKey

      DECRYPTION BY CERTIFICATE TestEncryptionCertificate;

END

GO

Method to Insert Data

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

• GUID of the symmetric key to be 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.

To get the GUID of the symmetric key, we can use the SQL Server 2005 in-built function Key_GUID. This function takes the name of the symmetric key as an input parameter.

Please note that before executing the query, we have to open the key. To do that, we will use separate stored procedure “uspOpenSymmetricKey” created above.

So our insert query will look like this:-

INSERT INTO [Employee]

           ([EmployeeName]

           ,[DateOfBirth]

           ,[SSN])

     VALUES

           (@EmployeeName,

           EncryptByKey(Key_GUID('TestSymmetricKey'), @DateOfBirth),

           EncryptByKey(Key_GUID('TestSymmetricKey'), @SSN)

           )

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

CREATE PROCEDURE [InsertEmployeeUsingKey]

(

      @EmployeeName NVARCHAR(50),

      @DateOfBirth NVARCHAR(100),

      @SSN NVARCHAR(100)

)

AS

BEGIN

INSERT INTO [Employee]

           ([EmployeeName]

           ,[DateOfBirth]

           ,[SSN])

     VALUES

           (@EmployeeName,

           EncryptByKey(Key_GUID('TestSymmetricKey'), @DateOfBirth),

           EncryptByKey(Key_GUID('TestSymmetricKey'), @SSN)

           )

          

END

Method to retrieve data

While decrypting data, we have to decrypt the fields using SQL Server 2005 in-built function DecryptByKey. This method takes just one parameter:-

• 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), DecryptByKey([DateOfBirth])) as DecryptedDOB

,CONVERT(NVARCHAR(100), DecryptByKey([SSN])) as DecryptedSSN

FROM [Employee]

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

CREATE PROCEDURE [GetEmployeesUsingKey]

AS

BEGIN

SET NOCOUNT ON;

SELECT [EmployeeId]

,[EmployeeName]

,CONVERT(NVARCHAR(100), DecryptByKey([DateOfBirth])) as DecryptedDOB

,CONVERT(NVARCHAR(100), DecryptByKey([SSN])) as DecryptedSSN

FROM [EncryptionDecryptionTest].[dbo].[Employee]

END

Please note that while decrypting data using this method, we don’t have to specify name of symmetric key. The symmetric key information is already available in the cipher-text. So, the only thing we have to do is open the key before executing the query.

Calling the stored procedures in .NET code

To actually make the encryption/decryption work using the keys, we need to call the stored procedure in a specific order.

• Create a SQL Connection
• Open the connection
• First execute the stored procedure to open the key – “OpenSymmetricKey”
• Then execute the stored procedure to encrypt/decrypt data
• Close the connection.

When connection is closed, the opened symmetric key gets closed automatically.

Sample code to insert/update data

string openKeyStoredProc = "OpenSymmetricKey";

string insertStoredProc = "InsertEmployeeUsingKey";

SqlConnection conn = new SqlConnection(connString);

conn.Open();

SqlCommand openKeyCommand = new SqlCommand(openKeyStoredProc, conn);

SqlCommand insertCommand = new SqlCommand(insertStoredProc, conn);

openKeyCommand.CommandType = CommandType.StoredProcedure;

insertCommand.CommandType = CommandType.StoredProcedure;

insertCommand.Parameters.AddWithValue("@EmployeeName", name);

insertCommand.Parameters.AddWithValue("@DateOfBirth", DOB);

insertCommand.Parameters.AddWithValue("@SSN", SSN);

openKeyCommand.ExecuteNonQuery();

insertCommand.ExecuteNonQuery();

conn.Close();

Sample code to retrieve data

DataSet ds;

string openKeyStoredProc = "OpenSymmetricKey";

string selectStoredProc = "GetEmployeesUsingKey";

SqlConnection conn = new SqlConnection(connString);

conn.Open();

SqlCommand openKeyCommand = new SqlCommand(openKeyStoredProc, conn);

SqlCommand selectCommand = new SqlCommand(selectStoredProc, conn);

openKeyCommand.CommandType = CommandType.StoredProcedure;

selectCommand.CommandType = CommandType.StoredProcedure;

openKeyCommand.ExecuteNonQuery();

SqlDataAdapter adapter = new SqlDataAdapter(selectCommand);

ds = new DataSet();

adapter.Fill(ds);

conn.Close();

Advantages

This approach have following advantages:-

• No passwords are used to encrypt/decrypt data.
• No key names are passed as input parameters to stored procedures which SQL Profiler can see.
• In procedures where data is decrypted, the name of symmetric key will not be mentioned anywhere. Hence even if a user has access to stored procedure, he cannot see data by directly executing it unless he knows which key to open and how to open it.
• We have additionally encrypted the procedure to open the symmetric key using “WITH ENCRYPTION” clause while creating it. This ensures that the name of the symmetric key and the certificate is safe from prying eyes.