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

  • Comments posted to this topic are about the item How to create a CLR assembly on a remote server with limited permissions

  • I think this article could also be listed as "how to compromise the databaseserver, when you have compromised the webserver" 😉

  • 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.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

  • Thanks, I learned some useful things from that article.

  • 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

  • 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.

  • 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.

  • 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

  • Thanks Charles. Now I understand.

Viewing 9 posts - 1 through 9 (of 9 total)

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