How to Encrypt and Decrypt Text in SQL Server (2008+)

Here’s a simple example on how to encrypt (and decrypt) values in SQL Server:

 

--How to Encrypt and Decrypt Text in SQL Server (2008+)
--Example by Clint Huijbers

DECLARE
@Key1 NVARCHAR(50) = N'8g87g8ag4r8hinsg^%$#F&^F&^F^F&ÛIBOUG(%*^R&$%&#%^C5'
,@Key2 NVARCHAR(50) = N'&*^%R%^**F$F*OUYBYUB*F%74d654d7f685f65f56f6v6vc88d'
,@ValueToEncrypt VARCHAR(150) = 'This is the value that will be encrypted by the built-in function of SQL Server with two keys and 128-bits encryption :) blablablablablablablablablabl'
,@EncryptedValue VARBINARY(350) --Increase the VARBINARY size when you increase the lengh of @ValueToEncrypt!

SELECT @EncryptedValue = ENCRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@ValueToEncrypt)
SELECT @EncryptedValue

SELECT CONVERT(VARCHAR(150),DECRYPTBYPASSPHRASE((@Key1+N'||'+@Key2),@EncryptedValue))

 


 


 

SQL Server 2012 Encryption (Security)

SQL Server database encryption the 2012 way…
How to fully secure your sensitive data? Have a look!

Am I still missing anything? Reply!

Query:

Results:


SET NOCOUNT ON;

DECLARE
@Pass1 NVARCHAR(25),
@Pass2 NVARCHAR(25),
@Salt NVARCHAR(25)

SET @Pass1 = '23543'
SET @Pass2 = '45663'
SET @Salt = 'R@nd0mS!a6lTValue'

SELECT TOP 10
FirstName
--Encrypted, 2 times to prevent the use of rainbow tables:
,EncryptByPassPhrase(@Pass2,
EncryptByPassPhrase(@Pass1,FirstName)
) AS Encrypted
--Decrypted:
,Convert(NVARCHAR(50),
DecryptByPassPhrase (@Pass1,
DecryptByPassPhrase (@Pass2,
EncryptByPassPhrase(@Pass2,EncryptByPassPhrase(@Pass1,FirstName))
)
)
) AS Decrypted
--One-way hashing on multiple columns:
,HashedMultipleValues =
HashBytes('SHA2_512',
IsNull(FirstName,'NA')+'|'+IsNull(LastName,'NA') --'|' = seperator
)
--One-way hashing on a single column (like creditcard numbers of passwords):
--Don't forget to use HashBytes() multiple times using the same @Salt value!
,HashedSingleValue =
HashBytes('SHA2_512',
IsNull(FirstName,'NA')+@Salt --Added @Salt to prevent the use of rainbow tables
)
FROM AdventureWorksDW2008R2.dbo.DimCustomer


 

—>>> ToDo’s (don’t forget!):
– Save all encrypt/decrypt coding (SP/FN) with the ‘WITH ENCRYPTION’-statement,
or you can also use a certificate and key to encrypt/decrypt data:
http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/
– Enable TDE (Enterprise Edition of SQL Server 2012) to prevent any user to access the data on disk level:
http://msdn.microsoft.com/en-us/library/bb934049.aspx
– Make sure to secure all inbound and outbound connections by using Certificates (for example by Symantec / Verisign):
http://www.symantec.com/products-solutions/families/?fid=ssl-certificates#tabs
– More info on SQL Server 2012 Encryption:
http://msdn.microsoft.com/en-us/library/bb510663.aspx
– DO NOT store your certificates on the same server….use an encrypted USB disk and store it in a physical safe for example
– Are there any applications using your SQL Server? Hire a hacker to perform a security check (like SQL Injection)