Protecting Data in SQL Server by Digital Signing

The estimated reading time for this post is 1 minutes

Lets dive into one of the amazing features of SQL Server regarding data protection and security. So far the data protection is mostly handled by the application layer, developers create login page/window for users to login and manipulate their own data and records. At the other hand, all the data stored in the back end database which DBAs can view and manipulate all the data or other users can hijack each others credentials (Username/Password) to do some illegal data manipulation on behalf.

How to find out illegal data manipulation was not done by the data owner? Or how to find out illegal data manipulation straight away?

Scenario: There is a real estate company which has its own sales system, this sales system has a function called ‘Contract Store’ which users can enter contract’s key information such as the real estate price, commission percentage, and also the sales person name. In this company there is a sales person which is trying to own the new contracts sealed by other sales persons by hacking the database system and update the records manually!

In SQL Server we can protect data by digital signing, which bounds the data to the real owner. Signing data can be accomplished by private key of either certificate or asymmetric key. The private key must be only in hands of the signer and the signature can be verified by anybody that has public key. Below figure illustrates the differences between encryption and signature.

SQL Server Database Administrators can sign the data by following the below steps.

  1. The certificate and the owner must be bound together.
  2. Revoke control permission of the certificate from public role.
  3. Sign the data by SignByCert() function.
  4. Verify the signature by VerifySignedByCert() function.

Below codes show the steps of the signing data.


Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:

Leave a Comment

Notify of