Secure by de...what? (User instances in SQL Server 2005 Express Edition)

In her latest post Secure by de...what? , Nicole Calinoiu talks about the security implications of User instances in the new SQL Server 2005 Express Edition

I need to do some more research on this, so I will not comment on the details, all that I will say is that it is impossible to protect what one doesn't understand, and 'features' like these are what create insecure and exploitable environments (most of the time simply because all parties involved have no idea of the security implications of their actions)

Some quotes:

“... User instances are a new capability of SQL Server 2005 (Express edition only) that are supposedly intended to allow non-admins to attach database files without requiring additional permissions. This actually works just fine and, at first glance, it probably strikes most folks as a lovely least-privilege accomodation. The unfortunate bit that might not be immediately obvious to the casual user is that this is accomplished by granting the connecting user sysadmin privilege over his user instance. This means that every connection to a user instance is a connection running as sysadmin.

....

The main reason for this is that a sysadmin login has unlimited control over the SQL Server instance, as well as being able to "climb" out of the SQL Server instance via extended stored procedures (or hosted SQLCLR code, in the case of SQL Server 2005) to affect other machine resources.

In other words, code running under a sysadmin login can fully control the SQL Server instance and can do anything on the machine or network that either the login account or the SQL Server service account can do. It's also possible to impersonate other Windows accounts when calling outside SQL Server, so the damage potential isn't necessarily limited by the privileges of the login and service account.

... the little voices in my head started evaluating the implementation against SD3+C ("secure by design, secure by default, secure in deployment, and communications") criteria, which is supposed to be an integral part of the Microsoft security development lifecycle. I can't help but feel that some less risky choices might have been made along the way ....

...

Secure in deployment?

There's little an administrator or user can do to make user instances more secure if they're enabled. There appears to be no information at all out there about the risks of their use, forget about guidance on "how to use it securely". We'll have to wait to see if updates will be easy to deploy, but updating all user instances on any given machine will certainly pose some potentially interesting challenges.

...

What about CAS permissions?

Sorry, but CAS isn't going to help much here if you allow connections to a user instance. Code with any SqlClientPermission can do anything the connecting user is allowed to do via the SQL Server instance. When connecting to a remote instance (or even a local non-user instance), the user's capabilities are usually (or so one would hope!) constrained by their NTFS permissions, SQL Server permissions, and limitations imposed by the configuration of the SQL Server instance. However, running as sysadmin on a user instance, these contraints are mostly absent. If you grant any SqlClientPermission to managed code that permits connection to a user instance, you are effectively granting permission for that code to do anything the user can do. The end result for a malicious application is the same as if you had granted unrestricted CAS permissions (aka "full trust"). In other words, you shouldn't be granting SqlClientPermission that includes the possibility to connect to a user instance to any assembly unless you would happily grant unrestricted permissions as well.

This means that granting unrestricted SqlClientPermission to any code (other than as part of a full trust grant)

....

Wrapping things up...

In my opinion, SQL Express user instances just plain don't meet the SD3+C bar, and disabling them is probably the best way for most of us to protect ourselves against the risks they introduce. Then again, I am a something of a paranoid nutbag, so your mileage may vary greatly... ;)

....

“

No Nicole, you are not paranoid at all...

--Dinis.cruz 05:56, 22 July 2006 (EDT)