Encrypt and Decrypt Column Value In SQL Server 2012 Table

/* First Create table */
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Password](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Password] [varbinary](100) NOT NULL,
[Application] [varchar](100) NULL,
[CreateDate] [datetime2](7) NOT NULL,
CONSTRAINT [PK_Password] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Password] ADD CONSTRAINT [DF__Password__Create__173876EA] DEFAULT (getdate()) FOR [CreateDate]
GO

 

/* Second create store procedure
Encrypt data with a passphrase using the TRIPLE DES algorithm with a 128 key bit length.
A passphrase is a password that includes spaces. The advantage of using a passphrase is that it is easier to remember a meaningful phrase or sentence than to remember a comparably long string of characters.
In this example below our Pass Phrase is ‘ZBetterNoiZ’
*/

USE [BaseLineData]
GO

/****** Object: StoredProcedure [dbo].[uspAddPasswords] Script Date: 6/7/2017 9:30:02 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[uspAddPasswords]
@Password NVARCHAR(50) ,
@Application NVARCHAR(200)
AS
BEGIN
SET NOCOUNT ON

INSERT INTO dbo.Password (Password,Application)
VALUES (EncryptByPassPhrase(‘ZBetterNoiZ’,@Password),@Application)
RETURN
END
GO

* 3. Execute Store Procedure */

EXEC dbo.uspAddPasswords
@Password=N’InsertPasswordHere’,
@Application=N’IL-SQL1′;

 

/* Query Table */

SELECT * FROM [dbo].[Password]

 

 
/* Decrypt data that was encrypted with a passphrase
must use the Pass Phrase in our example ‘zBetterNoiz’
*/

SELECT ID,convert (nvarchar(50),DECRYPTBYPASSPHRASE(‘ZBetterNoiZ’,Password)) AS DECRYPTEDPASSWORD,
Application,CONVERT(date,CreateDate) AS [CREATE DATE]
FROM Password;