Select the records from the table to confirm values , CONSTRAINT PK_tblPersonnel PRIMARY KEY CLUSTERED (ID ) ) , SocialSecurityNumberEncrypted VARBINARY (128 ) NULL , SocialSecurityNumber CHAR (11 ) NOT NULL IF OBJECT_ID ( 'DB.dbo.tblPersonnel' ) IS NOT NULL If the personnel table already exists, drop it We will store the social security number in plain-text as well as in encrypted form in this example to make it easier to confirm our underlying data. For this example we will assume our database is named “DB” and will be working within the “dbo” schema. In a future post I will discuss more details on the different types of security, but, as a basic example, let’s walk through this common implementation.ĭemonstration Preparation: For demonstration purposes, let’s create a table to store personnel information such as name and social security number (national id).

One of the most basic implementations is securing a column of data with a symmetric key. SQL Server has built-in security measures that are robust, configurable, and offer multiple layers of security.

Sometimes that level of security is warranted and sometimes it is not. Adding highly secure encryption to every aspect and byte of data in a database can be done, but it results in a correspondingly high amount of time and resources (CPU processing, etc.) to interface with the database with authorized credentials. Of course, the balance is the cost and, in the case of databases, the performance overhead. Bottom line: no security system is without weakness, but the more measures you have in place the more it deters and prevents would-be attackers, hence the more secure it is. However, even with all of that in place, all it takes is someone with two keys, the security system code, and enough force to overpower your armed guard and then your house is compromised. Think of securing your house: first you install doors, then you add a lock and key, then you might add a second lock with a second key, then a security system, then a guard, then an armed guard, and so on until you are satisfied with the security measures.

The goal is simply to make it sufficiently difficult to extract sensitive information so as to deter intrusion or delay it long enough to detect and actively counter it. Database security is like security in general: every step you take offers a degree of protection, but no single step nor combination of steps can ever offer “complete” or “guaranteed” security.

Backup service master key