SQL Server 2005 and Limitations to Assembly Loading

I've recently started reading "A First Look at SQL Server 2005 for Developers" in my spare time (yea, I'm not getting very far since I don't have much spare time) and I came across something rather limiting I think.  It says that you must be logged into SQL Server using an integrated security login, as opposed to a sql server account, in order to create a .NET assembly in SQL Server 2005.  The rationale given was that this was necessary in order to check if the user should have access to the file system location where the .NET assembly is to loaded from.  That does make sense, but it seems that implies that shared web hosts won't be able to easily allow us to use .NET assemblies on their SQL Servers -- am I missing something here?  Of course I'm not convinced that I would actually want a shared SQL Server on a shared web host allowing .NET use anyhow, since I don't want my data access slowed down by someone else playing with .NET stored proc, but I hadn't realized this limitation would exist either.
Published Saturday, January 08, 2005 10:36 PM by PaulWilson

Comments

# re: SQL Server 2005 and Limitations to Assembly Loading

That is correct. You cannot manually create an assembly in SQL Server 2005 when logged in with a SQL-only account. This is because these objects need to be secured, and SQL 2005 also allows you to lock down who (or what roles) can create or modify these objects.

I wrote an article for VSM that you might be interested in:

http://www.ftponline.com/vsm/2004_12/magazine/columns/databasedesign/

Sunday, January 09, 2005 11:41 AM by IlluminatiLord

# re: SQL Server 2005 assemblies, what's about hosting ?

Wednesday, January 19, 2005 1:30 PM by TrackBack

# re: SQL Server 2005 and Limitations to Assembly Loading

Actually, after reading further, it will be possible for hosters to enable this, although its a different issue about whether or not they should or will. The solution is that you can assign a Windows credentials to a Sql login -- so a hoster would need to create a Windows user account with permissions limited correctly to that user's directories and then assign those credentials to the matching Sql login. That's certainly doable, but I still have to believe that most shared hosters will not offer this functionality -- but we'll see. And I'm also still not convinced there's enough reason to allow it when you consider that opening up this door could potentially open up some security holes or hurt performance (maybe not though).

Tuesday, January 25, 2005 9:02 AM by Paul Wilson

Leave a Comment

(required) 
(required) 
(optional)
(required)