SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to create a CLR assembly on a remote server with limited permissions


How to create a CLR assembly on a remote server with limited permissions

Author
Message
Pavel Sinkevich
Pavel Sinkevich
SSC Rookie
SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)SSC Rookie (45 reputation)

Group: General Forum Members
Points: 45 Visits: 181
Comments posted to this topic are about the item How to create a CLR assembly on a remote server with limited permissions
lenne_dk
lenne_dk
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 65
I think this article could also be listed as "how to compromise the databaseserver, when you have compromised the webserver" ;-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)SSC Guru (338K reputation)

Group: General Forum Members
Points: 338659 Visits: 42606
lenne_dk (7/25/2011)
I think this article could also be listed as "how to compromise the databaseserver, when you have compromised the webserver" ;-)


Agreed. Add to that the subtitle of "while bypassing a code review and without your DBA knowing".

Taking nothing away from the author, the usefulness of this article is that I have to find a way to keep this from happening. :-) I suspect it will have to do with what I've always believed in... only read permissions on non-Development boxes for everyone except designated DBA's.

--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
tamir 60492
tamir 60492
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: 18
Thanks, I learned some useful things from that article.
charles.southey
charles.southey
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 34
Better management of CLR assemblies
Installing, managing and removing CLR assemblies is generally awkward with the standard tools provided by Microsoft: particularly if you have several assemblies with a relatively large number of functions etc. defined in each one (e.g. since there is no easy way to tell which functions belong to which assemblies, and you can't remove an installed assembly without first dropping all the objects defined by it). Keeping consistency and managing version control across multiple servers can easily become a headache. Working with EXTERNAL and UNSAFE permissions sets becomes even more complex unless you're prepared to set TRUSTWORTHY to ON - which is not ideal.

It was to address these sorts of problems - which I encountered regularly while building a set of commercial CLR function libraries, that I created the Assembly Manager tool (www.totallysql.com/Products/AssemblyManager). It started out as a install/remove manager - but I soon found a bunch of other things I wanted to be able to do quickly and easily (e.g. provide in-place version upgrades without having to drop dependent views etc.), so it grew into more comprehensive tool. It's offered as a commercial tool with a 30-day free trial period - if you work with CLR assemblies to any extent please do go and give it a try and let me have your views on it: if you're willing to help us out with a case study on it I can offer a complementary license.

www.totallysql.com

Charles Southey
www.totallysql.com
gajapathi.kannan
gajapathi.kannan
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 5
Author could supplement a use case for this article, it would provide some context. Currently I dont see a use for this setup in our environment.
Siderite
Siderite
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 34
Does anybody have any advice on the "best" way to install the dlls that support CLR based stored procs and UDF's? What i mean is where should these .dll files be located? On the same server as Sql Server? If so, in a certain folder? also, what permissions should be applied to to this folder? I am sure there will be many answers, however some general guidellines for a start would be helpful.
charles.southey
charles.southey
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 34
Siderite (8/8/2011)
Does anybody have any advice on the "best" way to install the dlls that support CLR based stored procs and UDF's? What i mean is where should these .dll files be located? On the same server as Sql Server? If so, in a certain folder? also, what permissions should be applied to to this folder? I am sure there will be many answers, however some general guidellines for a start would be helpful.


All managed code assemblies installed using CREATE ASSEMBLY are actually stored in binary form within the SQL database itself: when you execute a CREATE ASSEMBLY statement using a file path (or load an assembly using the Sql Server Management Studio dialogues) it actually creates an internal copy of that file, so it doesn't matter where you put the original file because it won't be referenced once the assembly is installed.

Provided they are all 100% managed code (.Net) you can load multiple assemblies into SQL Server and have them reference each other: for example if you have a general-purpose .NET library which isn't SQL-specific, and want to add some SQL CLR wrappers in another assembly to expose the library's function within SQL, you need to load the original library first, and can then add a reference to it from within Visual Studio when writing your wrapper (once you've set up the database connection to the database containing the original library). Thus an assembly does not need to be written specifically for SQL CLR in order to use its functionality within SQL.

The one exception to all this is if you need to call unmanaged code using P/Invoke (e.g. a third-party library) from a .Net assembly marked for UNSAFE access (best avoided if possible!). In this case you cannot install the library into SQL Server itself as it will not allow you to install unmanaged code. Instead in that case you need to put the assembly somewhere SQL Server can see it - e.g. c:\Program Files\Microsoft SQL Server\100\shared\ (for SQL Server 2008). The server already has read access to that folder, which is all you need. That will work - but note that once you've invoked a function within such a library you will need to re-start SQL server if you want to replace the file (it opens a file lock on it and never seems to relinquish it).


Charles Southey
www.totallysql.com

Charles Southey
www.totallysql.com
Siderite
Siderite
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 34
Thanks Charles. Now I understand.
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