Post reply

How to implement SHA-2 in SQL Server 2005 or 2008 with a CLR assembly

  • October 6, 2013 at 9:28 am

    #280589

    Comments posted to this topic are about the item How to implement SHA-2 in SQL Server 2005 or 2008 with a CLR assembly

  • October 7, 2013 at 3:35 am

    #1656270

    Absolute thorough walk through with no corners cut.

  • October 7, 2013 at 4:04 am

    #1656275

    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? :w00t:

  • October 7, 2013 at 7:03 am

    #1656315

    Nice write up Stan!

    Andy
    SQLAndy - My Blog!
    Connect with me on LinkedIn
    Follow me on Twitter

  • October 7, 2013 at 7:42 am

    #1656332

    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

  • October 7, 2013 at 8:25 am

    #1656358

    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? :w00t:

    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.

  • October 7, 2013 at 10:54 am

    #1656446

    "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.

  • October 7, 2013 at 1:44 pm

    #1656510

    Thank you Stan for the walk through. A couple of comments:

    1. Adding VS Command Prompt to external tools

  • October 7, 2013 at 1:50 pm

    #1656514

    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

  • October 8, 2013 at 4:59 am

    #1656676

    Thanks....

  • October 17, 2013 at 2:26 pm

    #1659490

    When we deployed the assembly to our 64-bit server, we got the following error message when trying to use the hashing function:

    Msg 10314, Level 16, State 11, Line 5

    An error occurred in the Microsoft .NET Framework while trying to load assembly id 65537. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:

    System.IO.FileLoadException: Could not load file or assembly 'sha256hash, Version=1.0.0.0, Culture=neutral, PublicKeyToken=4b8ce953b34e96e6' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

    System.IO.FileLoadException:

    at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

    at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

    at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

    at System.Reflection.Assembly.Load(String assemblyString)

    We resolved the issue by recompiling the assembly on a 64-bit workstation with the x64 CPU option, since the original assembly had been compiled on a 32-bit workstation.

  • July 24, 2015 at 12:57 pm

    #1814891

    The best tutorial ever !! Everything is explained in so much detail ! Helped me a lot.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply