SQL Clone
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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2475 Visits: 1144
I had a thought, but I proved myself wrong, so I deleted it.
sureshreddy1.9989241627
sureshreddy1.9989241627
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
Hi,

Please attach VB.Net code

Thanks,
Suresh
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217599 Visits: 41995
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217599 Visits: 41995
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217599 Visits: 41995
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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
shesh.singh
shesh.singh
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 22
greg.rowan (8/14/2013)
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

I had same problem and compiling with Any CPU option solved the issue.
greg.rowan
greg.rowan
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 107
AnyCPU fixed my issue. Thanks for the assist.
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25647 Visits: 2746
Using encryption alone doesn't solve the credit card problem. The auditor should be asking about split knowledge/dual control of the key - I think its in section 3.6 of the PCI standard. It depends on the auditor, but many will not sign off on the technique you're using for key management. PCI also requires an annual key change, tracking of retired and destroyed keys, etc, etc. Better to encrypt than not, but just doing encryption won't guarantee PCI compliance and it won't guarantee that your data can't be compromised if someone gets access to the database.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Stan Kulp-439977
Stan Kulp-439977
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2475 Visits: 1144
I wasn't really recommending this as a rock-solid security solution. I just wanted a non-trivial application for a CLR assembly that would be easy to understand. It took me a long time to figure out how to use CLR assemblies. I wish someone had written this article for me a couple of years ago.
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25647 Visits: 2746
Stan, kudos for both figuring it out on your own and filling a void on the how-to side, that's the best part of the SQL Server community. I also get the example, I just worry that someone searching for "SQL credit card encryption" find it and thinks, wow, I'm done, rather than closer to done. Hope you'll write more articles.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
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