How to Compile, Deploy and Consume a SQL Server CLR Assembly

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9942

    Comments posted to this topic are about the item How to Compile, Deploy and Consume a SQL Server CLR Assembly

  • avibooks

    SSC Enthusiast

    Points: 100

    Very interesting article that provides an easy method of encrypting / decrypting sensitive information stored in the database. Thanks for the post.

  • Jeff Moden

    SSC Guru

    Points: 993788

    Outstanding step by step article but (to cover the headline) I'm pretty sure that encryption and decryption Is possible in SQL Server. 🙂

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • HildaJ

    SSCarpal Tunnel

    Points: 4357

    I'm glad I ran into your article, we have several assemblies in our sql server it was nice to see how the process is captured step by step.

    Thank you.

  • Chris Harshman

    SSC-Forever

    Points: 41641

    Jeff Moden (8/13/2013)


    Outstanding step by step article but (to cover the headline) I'm pretty sure that encryption and decryption Is possible in SQL Server. 🙂

    While it's possible to encrypt data stored in SQL Server, a problem that frequently comes up in an application is that data needs to be encrypted before it's passed to the database. For example, at the company I work for, we use a simmilar CLR procedure to do this so that the web server which is located outside of our datacenter can encrypt sensitive data and send it to the middle tier program which then saves the encrypted value to the database. This way the data doesn't need to be decrypted and then re-encrypted in the middle tier program, since the programs and database are now using the same algorithm.

  • John Rees

    Mr or Mrs. 500

    Points: 509

    Excellent guide. Thank you.

    Normally I find such detailed step-by-step instructions boring, but I think in this case it is ideal. Not knowing anything about Visual Studio (like many DBAs?), it's great to have a dummy's guide to that, otherwise I'm sure I would have wasted many hours just trying to wire all this stuff together.

  • chillsdon

    SSC Enthusiast

    Points: 138

    Great article.

    It is also worth noting that once an assembly is loaded in to SQL Server, the actual DLL file is no longer used because loading an assembly really does load the whole assembly in to SQL Server. Because of this, you can script the assembly just like any other object (in SSMS expand the database, then Programmability, then Assemblies -> right click your assembly and choose "Script Assembly as".

    You will end up with a script that contains all the T-SQL required to deploy the assembly to another database (without needing the DLL file), including the encoded binary contents of the assembly itself.

    It's pretty handy for deployment to customer databases as you only need to deploy and run a script, not a DLL file and a script.

  • greg.rowan

    Valued Member

    Points: 59

    I followed this article step by step, but when I go to execute the Functions, I get the following error:

    An error occurred in the Microsoft .NET Framework while trying to load assembly id 65579. 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. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

    I tried dropping Assembly and re-adding as UNSAFE, but I get the same error. Any ideas what I may be doing wrong?

    p.s. running on Sql Svr 2012 instance

  • chillsdon

    SSC Enthusiast

    Points: 138

    Try making the database trustworthy first:

    ALTER DATABASE <DBName> SET TRUSTWORTHY ON

    I had to do this when creating a CLR procedure that accessed the file system, and so then also had to create the assembly with PERMISSION_SET = EXTERNAL_ACCESS.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9942

    I know that encryption is natively available in SQL Server.

    I just wanted to demonstrate a non-trivial use of a CLR assembly, and I just happened to have some C# encryption code handy.

  • Stan Kulp-439977

    SSCrazy Eights

    Points: 9942

    I had a thought, but I proved myself wrong, so I deleted it.

  • sureshreddy1.9989241627

    Grasshopper

    Points: 15

    Hi,

    Please attach VB.Net code

    Thanks,

    Suresh

  • Jeff Moden

    SSC Guru

    Points: 993788

    Chris Harshman (8/13/2013)


    Jeff Moden (8/13/2013)


    Outstanding step by step article but (to cover the headline) I'm pretty sure that encryption and decryption Is possible in SQL Server. 🙂

    While it's possible to encrypt data stored in SQL Server, a problem that frequently comes up in an application is that data needs to be encrypted before it's passed to the database. For example, at the company I work for, we use a simmilar CLR procedure to do this so that the web server which is located outside of our datacenter can encrypt sensitive data and send it to the middle tier program which then saves the encrypted value to the database. This way the data doesn't need to be decrypted and then re-encrypted in the middle tier program, since the programs and database are now using the same algorithm.

    Agreed. We do similar and we don't even decrypt in SQL Server. The app does it all. That way we don't have to worry about someone doing an intercept on that part of the pipe.

    My only point was that the headline made it sound like you couldn't do encryption/decryption in SQL.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993788

    Stan Kulp-439977 (8/14/2013)


    I know that encryption is natively available in SQL Server.

    I just wanted to demonstrate a non-trivial use of a CLR assembly, and I just happened to have some C# encryption code handy.

    Ah! Got it. Thanks, Stan. And just in case I haven't said it, yet... nice article. Well done!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993788

    chillsdon (8/14/2013)


    Try making the database trustworthy first:

    ALTER DATABASE <DBName> SET TRUSTWORTHY ON

    I had to do this when creating a CLR procedure that accessed the file system, and so then also had to create the assembly with PERMISSION_SET = EXTERNAL_ACCESS.

    I don't know why but implicit privs between databases give me the shivers.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 20 total)

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