July 24, 2011 at 11:38 am
Comments posted to this topic are about the item How to create a CLR assembly on a remote server with limited permissions
July 25, 2011 at 3:40 am
I think this article could also be listed as "how to compromise the databaseserver, when you have compromised the webserver" 😉
July 25, 2011 at 4:05 am
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
Change is inevitable... Change for the better is not.
July 25, 2011 at 4:39 am
Thanks, I learned some useful things from that article.
July 25, 2011 at 4:43 am
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.
Charles Southey
www.totallysql.com
July 25, 2011 at 8:05 am
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.
August 8, 2011 at 10:47 pm
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.
August 9, 2011 at 2:51 am
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
Charles Southey
www.totallysql.com
August 9, 2011 at 5:15 pm
Thanks Charles. Now I understand.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply