Search results matching tags 'SQL Server 2008' and 'SHA-512'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=SQL+Server+2008,SHA-512&orTags=0Search results matching tags 'SQL Server 2008' and 'SHA-512'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Find a Hash Collision, Win $100http://sqlblog.com/blogs/michael_coles/archive/2010/04/17/find-a-hash-collision-win-100.aspxSat, 17 Apr 2010 21:38:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:24374Mike C<P>Margarity Kerns recently published a very nice article at SQL Server Central on <A href="http://www.sqlservercentral.com/articles/Data+Warehouse/69679/">using hash functions to detect changes</A> in rows during the data warehouse load ETL process.&nbsp; On the discussion page for the article I noticed a lot of the same old arguments against using hash functions to detect change.&nbsp; After having this same discussion several times over the past several months in public and private forums, I've decided to see if we can't put this argument to rest for a while.&nbsp; To that end I'm going to hold a little contest:&nbsp; <B>Generate an SHA-1 hash collision and win $100 and a book</B> (see bottom section for details).&nbsp; Before I get into the details of the contest I'm going to give a little background of how this came about.</P>
<P><B><U>Background Info</U></B></P>
<P><B>NOTE: If you aren't familiar with hash functions I highly recommend first reading the Wikipedia article at <A href="http://en.wikipedia.org/wiki/Cryptographic_hash_function">http://en.wikipedia.org/wiki/Cryptographic_hash_function</A>.</B></P>
<P>The idea of using a hash function for change detection is not new.&nbsp; Essentially a hash function generates a "fingerprint" of your data that you can use to compare an inbound row and an existing row.</P>
<P>Some people are wary of hash functions because they map a theoretically infinite number of large inputs to a much smaller finite set of hash values.&nbsp; Most of the arguments people make against using hash functions for change detection boil down to variations of Murphy's Law:</P>
<P><STRONG>"There's a chance of a <I>hash</I> <I>collision</I> [generating the same hash value for two different inputs], so a collision <I>will</I> happen!"</STRONG></P>
<P>People have different ways of dealing with this issue, including taking one of the following positions:</P>
<OL>
<LI>The chance of collision is negligible so no additional precautions are required.</LI>
<LI>A collision will absolutely happen so I won't use hash functions for change detection at all!</LI>
<LI>A collision may happen so I want to use hash values only to initially narrow down the number of rows I need to compare fully.</LI></OL>
<P>Positions #1 and #2 above are at different ends of the spectrum.&nbsp; Position #3 sits in the middle as a compromise solution.&nbsp; While compromises may make for good politics, they often make for terrible technical solutions, as I'll discuss below.</P>
<P><B><U>Position #1: Odds of Collision are Low Enough to be Ignored</U></B></P>
<P>As far as position #1 is concerned, it depends on which hash function you're using.&nbsp; You need to choose a true one-way <I>collision-free</I>* cryptographic hash function with a wide bit length.&nbsp; I normally recommend an SHA-2 hash function (256, 384 or 512 bit hash value), or when that's not available the SHA-1 160 bit hash function.&nbsp; The odds of generating a collision with a 160 bit hash function are 2^80.&nbsp; That is to say&nbsp;you can expect a collision after you generate hashes for 1,208,925,819,614,629,174,706,176 rows of data.</P>
<P>Of course if you're identifying rows by their natural or business keys this alternatively means you need to generate 1,208,925,819,614,629,174,706,176 variations of that single row before you'll hit a collision with SHA-1.</P>
<P>To put that number in perspective, consider that Google processes 20,000,000,000,000,000 bytes (20 petabytes) of data per day.&nbsp; If you were to store a single row in a database table for <I>every single byte</I> Google processes each day, it would take you 60,446,290 days (approximately 156,600 years) to store 1,208,925,819,614,629,174,706,176 rows in that table.</P>
<P>I personally assume position #1 on this subject, with the assumption that you have chosen a good solid hash function for the job.&nbsp; More on this later.</P>
<P>*A <I>collision-free</I> cryptographic hash function is a one-way hash function with negligible probability of generating the same hash value for two different inputs. SHA-1 and SHA-256 are examples of collision-free cryptographic hash functions.</P>
<P><B><U>Position #2: I Don't Trust Hash Functions</U></B></P>
<P>This position can't really be argued with.&nbsp; As shown above the odds of a collision with SHA-1 or another collision-free hash function are extremely low.&nbsp; But if you don't trust it, you just don't trust it.&nbsp; So the alternative is to compare every inbound column with every existing column.&nbsp; It will cost you in efficiency on wide tables, but if you're not concerned about processing power, server resources&nbsp;and execution time this classic method of change detection is well-proven to be 100% effective.</P>
<P><B><U>Position #3: The Compromise - Use Hash Values to Initially Narrow Down Results</U></B></P>
<P>This position is the compromise position that combines the implementation of #1 and #2 above.&nbsp; It sounds wonderful in theory - use a hash function to narrow down your results, eliminating rows that don't need to be compared column by column; then compare all of the columns in the remaining rows that haven't been eliminated.&nbsp; So let's look at a scenario:</P>
<UL>
<LI>You are processing Row A through your ETL process into a target table. &nbsp;Row B is the equivalent row in the target table (it has the same natural key/business key as Row A).&nbsp; This assumes we are first locating the equivalent row in the target table by natural key/business key of the incoming row.</LI></UL>
<P>There are three possible scenarios:</P>
<UL>
<LI>Row B exists in the target table, and is equal to Row A (no change).</LI>
<LI>Row B exists in the target table, but it is not equal to Row A (update).</LI>
<LI>Row B does not exist in the target table (insert Row A).</LI></UL>
<P>Let's say you've generated two hash values, h(A) is the hash for Row A and h(B) is the hash for Row B.&nbsp; Now we need to use h(A) and h(B) to eliminate rows to get rid of the extra column by column comparisons.&nbsp; Here are the rules you need to implement to use h(A) and h(B) to eliminate extra comparisons in this compromise solution:</P>
<BLOCKQUOTE>
<P>A.&nbsp; <STRONG>h(A) is equal to h(B)</STRONG>: according to the compromise, if h(A) = h(B) we need to compare all columns of the inbound row against the existing row since the belief is that the hash function can/will generate collisions.&nbsp; The idea is that h(A) may have generated the same value as h(B) even if A &lt;&gt; B.&nbsp; So we need to:</P></BLOCKQUOTE>
<BLOCKQUOTE>
<BLOCKQUOTE>
<P>(1)&nbsp; Compare all columns in A and B.&nbsp; If A = B then perform no action.</P>
<P>(2)&nbsp; Compare all columns in A and B.&nbsp; If A &lt;&gt; B then update.</P></BLOCKQUOTE></BLOCKQUOTE>
<BLOCKQUOTE>
<P>B.&nbsp; <STRONG>h(A) is not equal to h(B)</STRONG>: cryptographic hash functions guarantee that they will generate the same hash value for the exact same inputs.&nbsp; So we can eliminate full row comparisons if h(A) &lt;&gt; h(B).&nbsp; We know automatically that if h(A) &lt;&gt; h(B) then A &lt;&gt; B.&nbsp; Just perform the update.</P>
<P>C.&nbsp;&nbsp;<STRONG>h(B) is NULL</STRONG>: that is, if Row B does not exist in the target table than h(B) is NULL.&nbsp; This is a case where no further full-row comparisons are necessary.&nbsp; Just insert the row.</P></BLOCKQUOTE>
<P>Now consider a slowly changing dimension (SCD) in a datamart application.&nbsp; Many SCDs change slowly over time (hence the name <I>slowly</I> changing dimension).&nbsp; This means that new rows (updates and inserts) are far less common than receiving duplicate rows during ETL.&nbsp; So the vast majority of your inbound data will fall under rule A(1) above.&nbsp; So you're still performing comparisons of all columns for the vast majority of rows in a given table just to figure out that you don't need to update them after all!</P>
<P>If you eliminate even 90% of the inbound rows under rule A(1) above you haven't saved much processing (you're still comparing all columns for changes for 90% of your inbound rows).&nbsp; You probably actually cost yourself a lot of time and efficiency since you haven't accounted for the overhead of generating hash values for 100% of the inbound rows.</P>
<P>The only way this compromise is more efficient is if a very large percentage of your inbound rows (much greater than 50+%) are inserts under Rule C or updates under Rule B above.&nbsp; If the majority of your inbound rows are duplicates of existing rows under Rule A, you gain nothing.</P>
<P><B><U>The Contest</U></B></P>
<P>One-way collision-free cryptographic hash functions are supposed to have negligible probability of a hash collision, or two different inputs generating the same output.&nbsp; Hash collisions are what cause change detection with hashes to fail.</P>
<P>For instance, consider the following example of an MD5 hash collision:</P>
<P><STRONG>DECLARE @A varbinary(8000),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @B varbinary(8000),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @hA binary(16),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @hB binary(16);<BR><BR>SELECT @A = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab58712467eab4004583eb8fb7f8955ad340609f4b30283e488832571415a085125e8f7cdc99fd91dbdf280373c5bd8823e3156348f5bae6dacd436c919c6dd53e2b487da03fd02396306d248cda0e99f33420f577ee8ce54b67080a80d1ec69821bcb6a8839396f9652b6ff72a70,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @B = 0xd131dd02c5e6eec4693d9a0698aff95c2fcab50712467eab4004583eb8fb7f8955ad340609f4b30283e4888325f1415a085125e8f7cdc99fd91dbd7280373c5bd8823e3156348f5bae6dacd436c919c6dd53e23487da03fd02396306d248cda0e99f33420f577ee8ce54b67080280d1ec69821bcb6a8839396f965ab6ff72a70;<BR><BR>SELECT @hA = HASHBYTES('MD5', @A),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @hB = HASHBYTES('MD5', @B);<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <BR>SELECT CASE WHEN @A = @B<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN '@A Equals @B'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE '@A Is Not Equal To @B'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END AS AB_Equal,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CASE WHEN @hA = @hB<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN '@hA Equals @hB'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE '@hA Is Not Equal To @hB'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END AS Hash_Equal;</STRONG></P>
<P>The results are shown below:</P>
<P><IMG style="WIDTH:474px;HEIGHT:130px;" src="http://e60ybw.bay.livefilestore.com/y1psxBkasfIDMgAHCMabS5PLB9ude1BiCrYSrlnX0bKKCpRgSTnMBYiy4gA2uLRjr5Tpf1Feki0LZ6WmJEDJiEl46UECVjVfPpt/hash_not_equal.png" width=474 height=130></P>
<P>When you run this you'll notice that the query reports the two source varbinary strings @A and @B are not equal, yet the two MD5 hashes they generate are equal.&nbsp; This is an example of a simple hash collision with MD5.</P>
<P>Now the challenge is to populate the following script with two different binary values that generate the same hash value.&nbsp; The output should be the same as shown above in the MD5 example.</P>
<P><STRONG>--&nbsp; Begin script<BR>DECLARE @A varbinary(8000),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @B varbinary(8000),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @hA binary(20),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @hB binary(20);<BR><BR>-- Replace the <EM>?</EM> below with binary strings<BR><BR>SELECT @A = <EM>?</EM>,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @B = <EM>?</EM>;<BR><BR>SELECT @hA = HASHBYTES('SHA1', @A),<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @hB = HASHBYTES('SHA1', @B);<BR><BR>SELECT CASE WHEN @A = @B<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN '@A Equals @B'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE '@A Is Not Equal To @B'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END AS AB_Equal,<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CASE WHEN @hA = @hB<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; THEN '@hA Equals @hB'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE '@hA Is Not Equal To @hB'<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END AS Hash_Equal;<BR>-- End script</STRONG></P>
<P>The first person who sends me an example of two varbinary strings that generate the same SHA1 hash value will win $100 (US$) and a copy of my book <A href="http://www.amazon.com/T-SQL-2008-Programmer-rsquo-Guide/dp/143021001X">Pro T-SQL 2008 Programmer's Guide</A>.</P>
<P>And here are the inevitable conditions:</P>
<OL>
<LI><B>No NULLs.</B>&nbsp; @A and @B in the script above cannot be set to NULL for purposes of this contest.</LI>
<LI><B>8,000 bytes or less.</B>&nbsp; The T-SQL HASHBYTES function accepts varbinary(8000) values, so the values passed into it in this contest must be 8,000 bytes in length or less.&nbsp; The values assigned to @A and @B above must be 8,000 bytes or less in length.</LI>
<LI><B>No unnecessary changes to the script.</B>&nbsp; The only change allowed to the script above are the replacement of the question marks (?) with binary strings.&nbsp; No other changes to the script are authorized.</LI>
<LI><B>Only one person will win.</B>&nbsp; The first person who sends me a copy of the above script with two different binary values that generate an SHA-1 hash collision will win.</LI>
<LI><B>Void where prohibited.</B>&nbsp; Obviously if contests like this aren't legal in your country, state, county, city, etc. then you can't take part.&nbsp; Petition your government to make it legal :)</LI>
<LI><B>Time limits.</B>&nbsp; Entries must be received prior to midnight U.S. Eastern Standard Time on October 31, 2010.</LI>
<LI><STRONG>Decisions of the judge are final.</STRONG>&nbsp; For purposes of this contest that would be me.</LI>
<LI><STRONG>SQL Server 2005 or 2008.</STRONG>&nbsp; Entries must be runnable on&nbsp;SQL Server 2005 and SQL Server 2008 Developer Edition, and the results must be reproducible.</LI></OL>
<P>If a winning entry is received prior to the deadline, I'll post an update entry to the blog with the winning script and the name of the winner.</P>Let's Hash a BLOBhttp://sqlblog.com/blogs/michael_coles/archive/2009/04/12/let-s-hash-a-blob.aspxSun, 12 Apr 2009 20:50:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:13253Mike C<P>In my last <A class="" title="Let's Encrypt a BLOB" href="http://sqlblog.com/blogs/michael_coles/archive/2009/04/08/let-s-encrypt-a-blob.aspx">post</A> I talked about how to work around a couple of the limitations of SQL Server encryption by using SQL CLR and the .NET Framework to&nbsp;encrypt a BLOB value (up to 2.1 GB in size), using any supported algorithm you choose.&nbsp;In the example I used AES to encrypt data using a passphrase. SQL Server 2008 also allows you to generate cryptographic hashes of binary data using the MD2, MD4, MD5 or SHA-1 hash algorithms. The HashBytes function provides this functionality, as shown in the sample below:</P><FONT color=#0000ff size=2><FONT color=#0000ff size=2><CODE>
<P>SELECT</FONT></FONT><FONT size=2> </FONT><FONT color=#ff00ff size=2><FONT color=#ff00ff size=2>HashBytes</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>(</FONT></FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>N'SHA1'</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>,</FONT></FONT><FONT size=2> </FONT><FONT color=#ff0000 size=2><FONT color=#ff0000 size=2>'This is a test'</FONT></FONT><FONT color=#808080 size=2><FONT color=#808080 size=2>);</CODE></FONT></FONT></P>
<P><FONT color=#808080 size=2><FONT color=#808080 size=2><FONT color=#000000>The result is a 160-bit binary hash value:</FONT></FONT></FONT></P><CODE>
<P>0xA54D88E06612D820BC3BE72877C74F257B561B19</P></CODE>
<P><FONT color=#808080 size=2><FONT color=#808080 size=2><FONT color=#000000>The MD2, MD4 and MD5 algorithms all produce 128-bit binary hash values. There's a problem: the MD<EM>n</EM>-series of 128-bit hashes are currently considered insecure by cryptographers, and it's not advisable to use them for cryptographically secure applications. SHA-1 is considered more secure, but it's hash value length of 160 bits is considered relatively small by today's standards. The National Institute of Standards and Technology has already taken steps to require all federal agencies to <A class="" title="NIST to Feds: Stop Using SHA-1" href="http://csrc.nist.gov/groups/ST/toolkit/secure_hashing.html">stop using SHA-1 in 2010</A>. Instead they are requiring federal agencies to use SHA-2 family of algorithms, which includes SHA-256, SHA-384 and SHA-512, which generate more cryptographically secure hash values that are 256, 384 and 512 bits in length.</FONT></FONT></FONT></P>
<P>SQL Server's HashBytes function doesn't give you the ability to access these more secure hash functions, but they are available through the .NET Framework and SQL CLR. The SQL CLR GetHash function below mirrors the functionality of the built-in HashBytes function. This function accepts an Algorithm name, which can be SHA256, SHA384, or SHA512. You also need to pass the Plaintext value to be hashed to the function. While HashBytes is limited to an 8,000 byte plaintext value (it truncates everything past 8,000 bytes), the GetHash function accepts a varbinary(max) up to 2.1 GB in size.</P><CODE>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">[Microsoft.SqlServer.Server.<SPAN style="COLOR:#2b91af;">SqlFunction</SPAN>(IsDeterministic = <SPAN style="COLOR:blue;">true</SPAN>, DataAccess = <SPAN style="COLOR:#2b91af;">DataAccessKind</SPAN>.None)]<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;COLOR:blue;FONT-FAMILY:'Courier New';mso-no-proof:yes;">public</SPAN><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"> <SPAN style="COLOR:blue;">static</SPAN> <SPAN style="COLOR:#2b91af;">SqlBytes</SPAN> GetHash</SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">(</SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">&nbsp; <SPAN style="COLOR:#2b91af;">SqlString</SPAN> Algorithm, </SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">&nbsp; [<SPAN style="COLOR:#2b91af;">SqlFacet</SPAN>(MaxSize = -1)] <SPAN style="COLOR:#2b91af;">SqlBytes</SPAN> Plaintext</SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;">{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:blue;">if</SPAN> (Algorithm.IsNull || Plaintext.IsNull)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">return</SPAN> <SPAN style="COLOR:#2b91af;">SqlBytes</SPAN>.Null;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:blue;">bool</SPAN> HashDefined = <SPAN style="COLOR:blue;">true</SPAN>;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:#2b91af;">HashAlgorithm</SPAN> Hash = <SPAN style="COLOR:blue;">null</SPAN>;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:blue;">switch</SPAN> (Algorithm.Value.ToUpper())<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN>{<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">case</SPAN> <SPAN style="COLOR:#a31515;">"SHA256"</SPAN>:<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Hash = <SPAN style="COLOR:blue;">new</SPAN> <SPAN style="COLOR:#2b91af;">SHA256Managed</SPAN>();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">break</SPAN>;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">case</SPAN> <SPAN style="COLOR:#a31515;">"SHA384"</SPAN>:<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Hash = <SPAN style="COLOR:blue;">new</SPAN> <SPAN style="COLOR:#2b91af;">SHA384Managed</SPAN>();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">break</SPAN>;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">case</SPAN> <SPAN style="COLOR:#a31515;">"SHA512"</SPAN>:<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>Hash = <SPAN style="COLOR:blue;">new</SPAN> <SPAN style="COLOR:#2b91af;">SHA512Managed</SPAN>();<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">break</SPAN>;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><o:p>&nbsp;</o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">default</SPAN>:<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN>HashDefined = <SPAN style="COLOR:blue;">false</SPAN>;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">break</SPAN>;<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN>}<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:blue;">if</SPAN> (!HashDefined)<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp;&nbsp;&nbsp; </SPAN><SPAN style="COLOR:blue;">throw</SPAN> <SPAN style="COLOR:blue;">new</SPAN> <SPAN style="COLOR:#2b91af;">Exception</SPAN>(<SPAN style="COLOR:#a31515;">"Unsupported hash algorithm - use SHA256, SHA384 or SHA512"</SPAN>);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:blue;">byte</SPAN>[] HashBytes = Hash.ComputeHash(Plaintext.Value);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:green;">// Convert result to a SqlBytes value<o:p></o:p></SPAN></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 0pt;LINE-HEIGHT:normal;mso-layout-grid-align:none;"><SPAN style="FONT-SIZE:10pt;FONT-FAMILY:'Courier New';mso-no-proof:yes;"><SPAN style="mso-spacerun:yes;">&nbsp; </SPAN><SPAN style="COLOR:blue;">return</SPAN> <SPAN style="COLOR:blue;">new</SPAN> <SPAN style="COLOR:#2b91af;">SqlBytes</SPAN>(HashBytes);<o:p></o:p></SPAN></P>
<P class=MsoNormal style="MARGIN:0in 0in 10pt;"><SPAN style="FONT-SIZE:10pt;LINE-HEIGHT:115%;FONT-FAMILY:'Courier New';mso-no-proof:yes;">}</SPAN></P></CODE>
<P>As you can see, accessing .NET Framework hash functionality through the SQL CLR is very simple. In a future post I'll talk about making your hash values even more secure to protect against so-called "rainbow table" attacks.<FONT color=#808080 size=2><FONT color=#808080 size=2></P></FONT></FONT>