Not all cipher texts area unit needed to be regenerate back to plain texts. ideal is "passwords". All we want with non reversible encryption is, store them in encrypted format and perform comparison once needed. What's the most effective method of implementing this with MSSQL 2014?

SQL Server provides variety of functions that may be used for encrypting plain texts using totally different mechanisms. Most of the functions enable you to write and so rewrite them back to plain text. Examples for these functions are "ENCRYPTBYKEY" and "ENCRYPTBYCERT". If decryption isn't needed or the requirment is non reversible encryption, then the most effective function to be used is "HASHBYTES".

HASHBYTES returns the hash of given clear text supported the algorithm used. Algorithms supported are: MD2, MD4, and MD5 (128 bits (16 bytes)); SHA and SHA1 (160 bits (20 bytes)); SHA2_256 (256 bits (32 bytes)), and SHA2_512 (512 bits (64 bytes)). SHA2_256 and SHA2_512 available only with SQL Server 2012 and higher than.

Though we've been given several algorithms for this, most of them are vulnerable for many attacks and not thought-about as secured cryptography algorithm. Some of them a number of identified to "collisions" that generate same output for various inputs. If you're using a version before 2012, best is SHA1 even though it's been marked for "collisions". If the version of SQL Server is 2012 or higher than, best is either SHA2_256 or SHA2_512.

Here could be a sample code that shows the usage of HASHBYTES;
-- Creating table
IF OBJECT_ID('dbo.UserCredential', 'U') IS NOT NULL
 DROP TABLE dbo.UserCredential
GO
CREATE TABLE dbo.UserCredential
(
 UserId int identity(1,1) PRIMARY KEY
 , UserName varchar(20) NOT NULL
 , Password binary(64) NOT NULL
)
GO
-- Inserting records
INSERT INTO dbo.UserCredential
 (UserName, Password)
VALUES
 ('Peter', HASHBYTES('SHA2_512', 'Pa$$w0rd'))
 , ('Scott', HASHBYTES('SHA2_512', 'P@$$w0rD'))
-- Checking records inserted
SELECT * FROM dbo.UserCredential;

Since the cipher text cannot be reverted back with HASHBYTES, here is the way of doing the comparison.
-- Validating user
IF EXISTS (SELECT * FROM dbo.UserCredential
   WHERE UserName = 'Peter'
    AND Password = HASHBYTES('SHA2_512', 'P@$$w0rD'))
 Print 'User authenticated'
ELSE
 Print 'Invalid user!'

HostForLIFE.eu SQL Server 2014 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.