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

Question

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

Stan Kulp-439977

SSChampion

Points: 10062
More actions
August 12, 2013 at 11:07 pm

#296913

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

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

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

avibooks SSC Enthusiast Points: 133 More actions · Answer 1

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: 1003851 More actions · Answer 2

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

HildaJ SSCarpal Tunnel Points: 4516 More actions · Answer 3

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: 42192 More actions · Answer 4

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: 571 More actions · Answer 5

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 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

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 SSChampion Points: 10062 More actions · Answer 9

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 SSChampion Points: 10062 More actions · Answer 10

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

sureshreddy1.9989241627 Grasshopper Points: 15 More actions · Answer 11

sureshreddy1.9989241627

Grasshopper

Points: 15

August 15, 2013 at 1:06 am

#1641661

Hi,

Please attach VB.Net code

Thanks,

Suresh

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 12

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 14

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)