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 Wednesday, August 14, 2013 6:09 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 10:05 AM
Points: 128, Visits: 920
I had a thought, but I proved myself wrong, so I deleted it.
Post #1484548
Posted Thursday, August 15, 2013 1:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, August 15, 2013 7:02 AM
Points: 1, Visits: 1
Hi,

Please attach VB.Net code

Thanks,
Suresh
Post #1484609
Posted Thursday, August 15, 2013 6:42 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1484732
Posted Thursday, August 15, 2013 6:43 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1484734
Posted Thursday, August 15, 2013 6:50 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(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 #1484740
Posted Friday, August 16, 2013 3:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:19 PM
Points: 36, 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.
Post #1485055
Posted Friday, August 16, 2013 8:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 29, 2014 7:18 PM
Points: 5, Visits: 57
AnyCPU fixed my issue. Thanks for the assist.
Post #1485430
Posted Thursday, August 22, 2013 7:32 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 2:51 PM
Points: 6,790, Visits: 1,902
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
Post #1487254
Posted Thursday, August 22, 2013 6:29 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: 2 days ago @ 10:05 AM
Points: 128, Visits: 920
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.
Post #1487600
Posted Friday, August 23, 2013 5:12 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Today @ 2:51 PM
Points: 6,790, Visits: 1,902
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
Post #1487740
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse