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 Compile, Deploy and Consume a SQL Server CLR Assembly Expand / Collapse
Author
Message
Posted Monday, August 12, 2013 11:07 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 124, Visits: 894
Comments posted to this topic are about the item How to Compile, Deploy and Consume a SQL Server CLR Assembly
Post #1483559
Posted Monday, August 12, 2013 11:08 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 10:39 PM
Points: 14, Visits: 299
Very interesting article that provides an easy method of encrypting / decrypting sensitive information stored in the database. Thanks for the post.
Post #1483561
Posted Tuesday, August 13, 2013 7:20 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 2:58 PM
Points: 36,760, Visits: 31,215
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1483720
Posted Tuesday, August 13, 2013 9:33 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 155, Visits: 456
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.
Post #1483819
Posted Tuesday, August 13, 2013 9:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, July 25, 2014 10:03 AM
Points: 1,851, Visits: 2,010
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.
Post #1483845
Posted Tuesday, August 13, 2013 3:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:42 PM
Points: 29, Visits: 453
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.
Post #1484009
Posted Wednesday, August 14, 2013 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:58 AM
Points: 6, Visits: 22
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.
Post #1484117
Posted Wednesday, August 14, 2013 6:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 2:33 PM
Points: 3, Visits: 52
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
Post #1484243
Posted Wednesday, August 14, 2013 7:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 8:58 AM
Points: 6, Visits: 22
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.
Post #1484293
Posted Wednesday, August 14, 2013 4:01 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Today @ 12:59 PM
Points: 124, Visits: 894
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.
Post #1484534
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse