SQL Server : ONE-WAY ENCRYPTION (part 1) - How One-Way Encryption Works, Benefits and Disadvantages of One-Way Encryption

2/22/2014 3:34:55 AM

1. How One-Way Encryption Works

As noted in the introduction, disclosure of the secret value, encrypted using one-way encryption, is achieved through comparing the stored hash value with a second hash value, or search value. This search value is generated using the same algorithm that created the stored hash value. When a positive match is verified between the stored hash value and the search value, the stored hash value's original plain text value is indirectly revealed, as illustrated in Figure 1.

Figure 1. Searching for a plain text value among protected data.

In SQL Server, one-way encryption is accomplished through use of the Hashbytes method. This method uses a selected algorithm to generate a hash. Unlike the cell-level encryption methods, which produce a unique hash each time a plain text value is encrypted, the Hashbytes method will return the identical hash for a given text regardless to how many times the method is executed.

For example, when the plain text value of "1234567890" is hashed with the SHA1 algorithm, it will reliably return a hash value of 0x01B307ACBA4F54F55AAFC33BB06BBBF6CA803E9A each time Hashbytes is executed for that plain text value. Below is the syntax for this method:

HashBytes([Algorithm], [Plain Text])

This method's arguments are:

  • Algorithm: The algorithm used to create the cipher text. The options for this argument are: MD2, MD4, MD5, SHA and SHA1. 

  • Plain Text: The plain text that is being converted into cipher text.

The Hashbytes method will be used extensively in our implementation example of one-way encryption for our HomeLending database.

2. Benefits and Disadvantages of One-Way Encryption

The severe performance impact of searching on data that has been encrypted with cell-level encryption. While the strength of cell-level encryption and the granular level of control it provides to the security administrator are definite advantages, it limits the usability of some of the basic functionality of the database. One-way encryption is not as strong as cell-level encryption, but it does offer a layer of protection, while maintaining database and query performance.

As noted previously, there are many who will argue that one-way encryption should not be considered as an option to protect sensitive data due to its vulnerabilities and weaknesses. However, with a clear understanding of the benefits of the technique, its disadvantages, and the methods available to mitigate these disadvantages, some of the myths and warnings that exist regarding one-way encryption can be overcome.

This is not to say that one-way encryption is the answer for all of your sensitive data, or that it is so fool-proof. To determine if one-way encryption should be part of your protection efforts for sensitive data, consider the following benefits and disadvantages.


  • No key maintenance – data that has been encrypted through one-way encryption is not decrypted; therefore there are no keys generated that require maintenance.

  • Negligible impact on database and query performance – one-way encryption avoids the need to decrypt data, and uses a lightweight encryption algorithm, based on hash values. Please note that, optimally, any one-way encrypted field in a query should reside in the WHERE clause. If the plain text equivalent is presented in the SELECT clause, performance will be negatively affected.


  • Weaker algorithms – the algorithms available for one-way encryption in SQL Server are considered weaker than the algorithms used in cell-level encryption or transparent data encryption.

  • May require schema modification – the Hashbytes method returns a varbinary data type. Storage of this value, without conversion, will require a column of the same data type.

  • Security vulnerabilities of data in transit – the Hashbytes method requires the passing of plain text into its arguments. This plain text value can be disclosed through using SQL Server Profiler, or any other database transaction monitoring tool.

  •  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