Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

How to implement SHA-2 in SQL Server 2005 or 2008 with a CLR assembly Expand / Collapse
Author
Message
Posted Sunday, October 6, 2013 9:28 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:05 AM
Points: 128, Visits: 920
Comments posted to this topic are about the item How to implement SHA-2 in SQL Server 2005 or 2008 with a CLR assembly
Post #1501924
Posted Monday, October 7, 2013 3:35 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 3:46 AM
Points: 843, Visits: 333
Absolute thorough walk through with no corners cut.
Post #1502052
Posted Monday, October 7, 2013 4:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 2:06 AM
Points: 542, Visits: 2,125
Nice way to get your feet wet with getting a CLR to work.

Just a few comments:
On my W7 system, the location of sn.exe is not in the path. Thus I had to navigate to C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin.
On SS2005 after sp_configure and reconfigure, the service also had to be re-started. On SS2008R2, reconfigure was sufficient.

Then I tried:

select dbo.sha256Hash(o1.name)
from sys.all_objects o1,sys.all_objects o2

which took 46 seconds and extracted 4 million rows, followed by

select dbo.sha256Hash(dbo.sha256Hash(o1.name))
from sys.all_objects o1,sys.all_objects o2

which took 93 seconds, as expected.

Will anyone beat this writing the function in SQL?
Post #1502058
Posted Monday, October 7, 2013 7:03 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:51 PM
Points: 6,790, Visits: 1,902
Nice write up Stan!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Post #1502114
Posted Monday, October 7, 2013 7:42 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, March 5, 2014 1:54 AM
Points: 45, Visits: 36
yes, 15 seconds for

select hashbytes('sha2_256',o1.name)
from sys.all_objects o1,sys.all_objects o2

i agree in advance sha2_256 (and 512) needs sql 2012

kr/Werner
Post #1502134
Posted Monday, October 7, 2013 8:25 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 12:34 PM
Points: 31,181, Visits: 15,626
Michael Meierruth (10/7/2013)


Then I tried:

select dbo.sha256Hash(o1.name)
from sys.all_objects o1,sys.all_objects o2

which took 46 seconds and extracted 4 million rows, followed by

select dbo.sha256Hash(dbo.sha256Hash(o1.name))
from sys.all_objects o1,sys.all_objects o2

which took 93 seconds, as expected.

Will anyone beat this writing the function in SQL?


There's a few things to note here. First, we typically wouldn't hash millions of rows regularly. You'd use this in spots for a few values, so some of the inefficiencies here are fine.

Second, you can't implemen SHA-3 (or strong SHA-2 algorithms pre SQL2K12) any other way. SHA-11 has been attacked, so I'd recommend this moving forward for that data that you want to protect and use in a hashing situation.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1502164
Posted Monday, October 7, 2013 10:54 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, October 20, 2014 10:05 AM
Points: 128, Visits: 920
"Absolute thorough walk through with no corners cut."

I try to write for the accidental developer, such as the DBA who who is not interested in programming but needs to get something done, such as having an SHA-256 hash function available on a 2005/2008 SQL Server.

The detail is probably annoying to seasoned programmers, but seasoned programmers probably already know how to do what I am writing about anyway.
Post #1502270
Posted Monday, October 7, 2013 1:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:50 AM
Points: 2, Visits: 4
Thank you Stan for the walk through. A couple of comments:
1. Adding VS Command Prompt to external tools
Post #1502343
Posted Monday, October 7, 2013 1:50 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 11, 2013 7:50 AM
Points: 2, Visits: 4
Thank you Stan for the walk through.
A couple of comments...
1. Adding VS Command Prompt to external tools, use
Arguments: /k "\path\to\Microsoft Visual Studio 10.0\VC\vcvarsall.bat"
I used: /k "C:\Program Files (x86)\Microsoft Visual Studio 11.0\VC\vcvarsall.bat"
2. In my implementation of SQL Server 2005, before I enabled CLR, I had to show advanced options first:
EXEC sp_CONFIGURE 'show advanced options' , '1';
GO
RECONFIGURE;
GO
EXEC sp_CONFIGURE 'clr enabled' , '1'
GO
RECONFIGURE;
GO

Thanks again!!
David
Post #1502347
Posted Tuesday, October 8, 2013 4:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 9:44 PM
Points: 1, Visits: 4
Thanks....
Post #1502546
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse