Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Stan Kulp-439977
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
Comments posted to this topic are about the item How to Compile, Deploy and Consume a SQL Server CLR Assembly
avibooks
avibooks
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 559
Very interesting article that provides an easy method of encrypting / decrypting sensitive information stored in the database. Thanks for the post.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44778 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
HildaJ
HildaJ
SSC-Enthusiastic
SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)SSC-Enthusiastic (177 reputation)

Group: General Forum Members
Points: 177 Visits: 571
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
Chris Harshman
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2659 Visits: 3229
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
John Rees
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 539
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
chillsdon
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 48
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
greg.rowan
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 105
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
chillsdon
Grasshopper
Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)Grasshopper (14 reputation)

Group: General Forum Members
Points: 14 Visits: 48
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
Stan Kulp-439977
SSC Veteran
SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)SSC Veteran (254 reputation)

Group: General Forum Members
Points: 254 Visits: 1093
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search