SENSITIVE DATA! It’s an attention-grabbing matter! On this submit I’m making an attempt to clarify methods to hash information to extend safety throughout ETL. Assume that now we have delicate information saved in a number of secured supply methods. The supply methods are positioned in several nations and completely different areas. Because the supply methods themselves are secured, how we are able to cowl information safety wants throughout ETL course of to learn information from supply methods and cargo into staging space? Other than utilizing secured community infrastructure, VPN, community tunnelling and so forth. we have to cowl information layer safety to extract delicate information. Top-of-the-line methods is hashing information when it’s extracting from supply databases. Hashbytes is a T-SQL operate that’s out there in SQL Server 2005 and later. As you would possibly know there are lots of hashing algorithms, however, completely different SQL Server variations are supporting completely different vary of hashing algorithms. For example SHA1 is supported by SQL Server 2005 and later, however, if you’re trying safer hashing methods like SHA2, 256 (32 bytes) or 512 (64 bytes), you need to use SQL Server 2012. Truly the hashbytes operate will return null in earlier variations of SQL Server. If you’re searching for a better stage of safety like SHA3 that’s initially often known as “Keccak” you need to look ahead to it for a very long time as based mostly on my investigations it’s not supported even in SQL Server 2014 OR you may write your individual SHA3 code OR simply depend on some third occasion codes out there on the Web! So let’s get our palms soiled with utilizing hashbytes in several variations of SQL Server.

SQL Server 2005:

SELECT  @@model [SQL Server Version]

            , hashbytes(‘SHA1’, ‘123456’) [SHA1]

            , hashbytes(‘SHA2_256’, ‘123456’) [SHA2_256]

            , hashbytes(‘SHA2_512’, ‘123456’) [SHA2_512]

Outcomes:

Let’s run the identical question in SQL Server 2008 and see the outcomes:

Once more the end result for SHA2 is null.

And know we’re testing SQL Server 2012:

We’ll see the identical outcomes retrieved from SQL server 2014.

So, the concept is DO NOT LOADING SENSITIVE DATA AT ALL. Consequently, it appears the one method that the info would possibly leak is that anyone sniffs the SQL codes which are retrieving information in reminiscence (notice that our assumption is now we have a safe community infrastructure). Now we are able to put our T-SQL code into an “OLE DB Supply” element in SQL Server Integration Companies (SSIS) and we could have the hashed information (VarBinary) within the staging space.