SQL Server : ONE-WAY ENCRYPTION (part 3) - Reducing Vulnerability: Salting a Hash

2/22/2014 3:39:31 AM

Reducing Vulnerability: Salting a Hash

In culinary circles, salt is used as a preservative and a flavor enhancer. In the days before refrigeration meat was heavily salted for extended storage. The salt slowed the deterioration of the meat and prevented mold and bacteria from contaminating it. This protected the integrity of the meat so that its quality could be assured for a reasonable length of time.

Salt in cryptography has a similar effect. A one-way encrypted hash value is vulnerable to dictionary and rainbow table attacks; but adding a salt to the plain text, before it is encrypted, results in a hash value that is very resilient to these attacks. Salting renders the underlying plain text more complex, and breaks expected patterns that can be anticipated by the attacker.

For example, an attacker who is executing a dictionary attack against a table that contains unsalted hash values of Social Security Numbers will anticipate that the patterns of the plain text will be "000-00-0000" or "000000000". This known pattern provides the attacker with a finite combination of approximately one billion (109) possible values. However, if the Social Security Number is salted with a seven character alphanumeric value, for example, then the possible combinations for the plain text values skyrockets to over seventy eight quintillion (78 x 1018). Therefore, salting is a highly effective way of strengthening one-way encryption.

In the HomeLending database we will create a scalar-valued user defined function, called GetHashSalt, which is designed to return a seven character value, which will be used as the salt portion of a one-way encryption process.

Scalar-valued user defined function:

... is a function in which the value that is returned from its execution is a single value.

Listing 1 shows the script to create our GetHashSalt function. We will offer six variations of salt values designated with the values "L01" through "L06". These variations will provide a deeper level of protection to items that are salted throughout our database. These are the values that will be passed through the @Type argument of this user defined function.

Listing 1. The GetHashSalt UDF.

Inclusion of the WITHENCRYPTION option prevents the revelation of these salt values by viewing the definition of the user defined function, as well as preventing its modification. This renders the code of the user defined function invisible through catalog views, unencrypted backup files and through SSMS.

With this user defined function, we can salt our plain text values before they are encrypted. The process of doing this involves the following steps:

  • Call the GetHashSalt user defined function and assign it to a variable.

  • Concatenate the variable to the plain text of the data that is to be encrypted.

  • Place the resulting concatenated value in the plain text argument of the Hashbytes function.

For example, an original plain text of "555-37-0143" and a salt value being "HYz5#4555", the resulting concatenated value will be "HYz5#45555-370143". Using the "SHA1" algorithm, the resulting salted hash value will be 0xD544F25AC44F6CBC108DA211D2A48990A343359C.

Listing 2 will grant EXECUTE permissions on the GetHashSalt UDF to the Sensitive_high and Sensitive_meduim database roles.

Listing 2. Granting permissions to the GetHashSalt UDF.

Specific examples of the application of a salt, with the HomeLending database, will be illustrated in the following one-way encryption demonstration.

  •  SQL Server 2012 : Measuring SQL Server Performance (part 7) - Viewing the Data Collector Data - Query Statistics History
  •  SQL Server 2012 : Measuring SQL Server Performance (part 6) - Viewing the Data Collector Data - Disk Usage Summary
  •  SQL Server 2012 : Measuring SQL Server Performance (part 5) - Viewing the Data Collector Data - Server Activity History
  •  SQL Server 2012 : Measuring SQL Server Performance (part 4) - Setting Up the Data Collector
  •  SQL Server 2012 : Measuring SQL Server Performance (part 3) - Dynamic Management Objects, Data Collector
  •  SQL Server 2012 : Measuring SQL Server Performance (part 2) - Performance Monitor
  •  SQL Server 2012 : Measuring SQL Server Performance (part 1) - Understanding Performance Counters
  •  SQL Server 2012 Security : How Hackers Attack SQL Server
  •  SQL Server 2012 Security : Partially Contained Databases
  •  SQL Server 2012 : SQL Server Audit (part 3) - Viewing Audited Events,Querying Audit Catalog Views
    Top 10
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th
    The latest Audi TT : New angles for TT
    Era of million-dollar luxury cars
    Game Review : Hearthstone - Blackrock Mountain
    Game Review : Battlefield Hardline
    Google Chromecast
    Keyboards for Apple iPad Air 2 (part 3) - Logitech Ultrathin Keyboard Cover for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 2) - Zagg Slim Book for iPad Air 2
    Keyboards for Apple iPad Air 2 (part 1) - Belkin Qode Ultimate Pro Keyboard Case for iPad Air 2
    Michael Kors Designs Stylish Tech Products for Women
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    Popular Tags
    Video Tutorail Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Exchange Server Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe Photoshop CorelDRAW X5 CorelDraw 10 windows Phone 7 windows Phone 8 Iphone