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)

Advertisements

One thought on “SQL Server 2012 Encryption (Security)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s