While I am working on Human Resource Solutions, security is always the most critical issue and also customer's first concern. HR system contains all of employees' personal and sensitive data such as salary.

These could be controlled via two level:

  1. Application/End user level
    • In this level, we would like control:
      • What could user do in system?
      • What could user see from system?
      • Whose record could user manage from system?

      Generally, we would implement menu, form, field and row security in application. These security features manage user access right by hiding certain menus, forms or control from screen so that users would not able to access and see them. Records are automatically filtered by adding additional filter confition to data layer SQL statements/stored procedure or business object. We could get these features ready if we are using application framework such as VPM, VPM, MereMortals, Strataframe.NET and etc.
  2. Database level
    • All of the features mentioned above just to manage user access right from user interface aspect. This is not enough because user who can access to that database would able to view all of the informations. Especially, for whom may have some programming knowledge, know how to retrieve data using SQL Server management studio/enterprise manager/Offices. That is why database level come into place.

      In order to implement data encryption in our database, we may need to write our own encryption library (of course we can download from internet too) or get thrid party tool to help. One of the best data encryption tool I know is DBEncrypt from Application Security Inc. Some articles about DBEnrypt could be found here and here. DBEncrypt provides very robust features and also UI to manage database security. Of cource, it is a bit costly since it has very rich features.

      Microsoft added new data encryption framework to SQL Server 2005. The framework provides us fundamental to implement data encryption. SQL Server 2005 manages security of database encryption key for us. It also provides functions to encrypt/decrypt data. As SQL Server 2005 doesn't provide native data encryption. Developers/DBA got to do something to their database, either call encryption functions from application level or database level.

After the long story written above, I am going to share some sample code to implement data encryption at database level after some research. The reason I built this at database level are data would be encrypted even though data is imported via DTS. I also want to minimize changes made to my application. I don't think I am going to change all INSERT/UPDATE/SELECT-SQL statements in my application.

1. Create database master key with password protected. This key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. It would be encrypted by SQL Server service key which encrypted with the Windows DP API.

CREATE master key ENCRYPTION BY PASSWORD= 'MyPassword'

2. Create asymmetric key and authorized user to access it. An asymmetric key is made up of a private key and the corresponding public key. Each key can decrypt data encrypted by the other. Asymmetric encryption and decryption are relatively resource-intensive, but they provide a higher level of security than symmetric encryption. An asymmetric key can be used to encrypt a symmetric key for storage in a database.

CREATE asymmetric key MyAKey authorization MyUser
WITH algorithm = RSA_1024
ENCRYPTION BY PASSWORD ='MyPassword2'

3. Create an symmetic key. A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database.

CREATE symmetric key MySKey
WITH algorithm = DES
ENCRYPTION BY asymmetric key MyAKey

4. After created all necessary key, we are ready to encrypt our data.

5. Of course, we got to OPEN those keys specified above to utilize it. Make sure you are using MyUser to login or EXEC AS myUser.

OPEN master key DECRYPTION BY PASSWORD='MyPassword'

OPEN symmetric key MySKey
DECRYPTION BY asymmetric key MyAKey WITH PASSWORD='MyPassword2'

6. Now, we can start to encrypt our data. Added an extra field to your table as VARBINARY(max) to store your encrypted field. Let's assume we are going to encrypt field ySalary (money) and encrypted field called ySalary_enc (varbinary(max)).

UPDATE myTable SET ySalary_enc = EncryptByKey(Key_GUID('MySKey'), cast(ySalary AS VARBINARY(max)))
7. Try to SELECT myTable records, ySalary_enc is shown in hexadecimal format. Drop the original field if desired.
8. In order to return ySalary in decrypted format without break our application, we would need to create a view to "encapsulate" base table. 
SELECT myFields, CAST(DecryptByKey(ySalary_enc) AS money) AS ySalary FROM <<table>>_base

9. Try to SELECT records using this newly created view. It shows decrypted data.

10. Next thing is to write INSTEAD OF INSERT, UPDATE trigger for this view so that we can update base table via this view.

/****** Object:  Trigger [T_U_dservice]    Script Date: 01/18/2007 16:31:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [T_U_myView]
   ON  [dbo].[myView]
   INSTEAD OF UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	UPDATE <<table>>_base SET myFields = inserted.myFields, 
        ySalary_enc = EncryptByKey(Key_GUID('MySKey'), CAST(inserted.ySalary AS VARBINARY(max))) 
        FROM inserted WHERE <<table>>_base.PK = inserted.PK

END
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE TRIGGER [T_I_myView]
   ON  [dbo].[myView]
   INSTEAD OF INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO <<table>>_base
	SELECT myFields, EncryptByKey(Key_GUID('MySKey'), cast(ySalary AS VARBINARY(max))) AS ySalary_enc
	FROM inserted

END

11. OK, we are done. Try to logout and login to SQL Server again. Execute SELECT statement to retrieve records from either base table or myView. What do you see? Our data is protected.

12. The final step is to integrate to our application by executing code shown in step 5 at application startup. We can also create a stored procedure to "encapsulate" the code and pass password as parameter.


The approach mentioned above is borrowed from DBEncrypt with simplified. It seems should work fine. However, I noticed some drawbacks/limitation :

1. Performance - Performance would be decreased when we update records via updatable view. In my testing, it takes 2-3 mins to update 97000 records which update directly to base table only take 10-20 secs. The drawback is not caused by encryption. Instead, mostly is because of the correlated update statement in INSTEAD OF UPDATE trigger. I posted question to MSDN and UT forum but couldn't get any response. Do you have any suggestions?

2. Prevent hacking from Admin - This approach couldn't prevent administrator to "hack" the data since he is the one who "own" the machine, can run trace and view memory of server.

 

This is what I got from my research recently. Please don't hesitate to comment/email me if I do it wrongly or any other better suggestions.

 

Updated: We should create view by named it using the original table name so that our application can access it as before. Otherwise, it will break our existing code.