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.
So… What’s so bad about connecting as sysadmin?
If you’re at all familiar with secure practices around database connectivity, you’ve probably heard that you should never connect under a sysadmin login unless you’re connecting for the express purpose of performing administrative tasks. 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.
Yikes! But, ummm… Yikes!
Hmmm… Sounds like running user instances might be just a wee bit on the risky side, doesn’t it? After a bit of a stumped initial reaction, 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, but perhaps that’s just my paranoid nutbag side talking. You decide…
Secure by design?
The main goal of user instance mode seems to be allowing applications to attach database files even when running under a limited privilege user account. That’s pretty necessary if you’re going to, say, push user instance mode SQL Server Express as a replacement for Jet. That said, might some safer design choices have been made when choosing how to implement this requirement? This would allow even dbcreator membership to be revoked when it isn’t actually needed, which could be the case if one were to configure the user instance template data files to pre-connect to a designated set of databases.
- Does the connecting user really need to be a sysadmin?
Probably not. Membership in the dbcreator role would probably have been quite sufficient for the purposes of attaching database files without invoking additional risks around control of the instance configuration and allowing code to call out of the database. However, a potentially more interesting design might allow a true sysadmin of the master SQL Express instance to designate the role membership of a user instance creator.
- Is the connecting user account really the best choice for the service account?
On the surface, choosing to run the user instance under the connecting user’s account might actually seem to be a good choice. After all, it ensures that code run within the user instance can’t do anything that the user himself can’t do (unless, of course, impersonation is used). However, if you turn things around a bit and assume that an attached database might come from a less than ideal source (say, passed around from one user to another, all of whom act as dbo and sysadmin while the database is in their hands), running with the full privileges of the connecting user all of a sudden doesn’t sound so good…
Could another choice have been made here? Granted, there are some challenges around designating the permissions granted to any alternate account. However, one obvious possibility would be to allow a master instance administrator to designate per-user service accounts for user instance mode. As with master instance service accounts, such a mechanism could automatically assign the minimal user permission set required for service operation, thereby reducing the administrative burden. A configurable design could also allow for enabling/disabling user instance mode by user (with disabled as the default state for a properly “secure by default” design).
- Do user instances really need the full functionality of stand-alone instances?
If the true purpose of user instances is to permit applications to attach local database files, why include any functionality beyond what’s needed to act as a pure database server? Do such applications really need to be able to run extended stored procedures like xp_cmdshell? If not, why include it at all?
- What CAS permissions ought to be assigned to assemblies hosted in an attached database?
Unfortunately, all assemblies hosted by the SQLCLR are assigned local zone evidence, which means that a database loaded from a remote location (either with an application loaded from that location or as an attached remote database) will be granted unrestricted CAS permissions under default CAS policy. In order to prevent remotely sourced applications from escalating their own CAS privilege via this mechanism, the SQLCLR probably ought to assign zone evidence based on database source locations in a manner similar to what the stand-alone CLR does for assemblies.
Secure by default?
Well, it looks like someone did at least give this one the old college try. For example, regardless of the master instance setting, a user instance will have xp_cmdshell use disabled by default. Unfortunately, it’s trivial to enable the option from within any application connected to a user instance since the user is running as a sysadmin, so this is essentially just a bit of cosmetic cover-up.
Given the current design, the only real “secure by default” setting that I can see would be to deploy SQL Server Express with user instance mode disabled by default. Since most machines on which the Express edition will be installed will likely never need to run user instances, it’s really rather disappointing that it’s enabled by default in the first place. Then again, this is an obvious ease of use vs. security trade-off, and it’s not exactly difficult to imagine the meeting at which the decision was made…
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.
Well, I guess we’ll see… 😉
Ouch! Band-aids, anyone?
If you need to install the SQL Server Express edition and want to protect yourself against these risks, there are a few things you can do. For starters, unless you absolutely need user instances, disabling them would probably be a really good idea. This can be done by executing sp_configure against the master SQL Express instance on a machine as follows:
sp_configure ‘user instances enabled’, ‘0’
Developers who distribute SQL Server Express edition with their applications might also want to keep this in mind. If you don’t use user instances in your application, you should probably disable them as part of the installation. Also, given the risks involved with running user instances, you might want to consider avoiding their use if at all possible. (BTW, if you’ve installed Visual Studio 2005 on your machine, there’s a good chance that SQL Express edition was also installed, and you might want to take a little break from reading this in order to run off and disable user instances.)
So, that’s all fine and dandy if you don’t need user instances at all. What happens if you really need to run an application that uses user instances? For starters, you might want to limit which users can create user instances. Unfortunately, as far as I know, the only way to do this at present would be to remove user permissions on the directory created for a user instance. In other words, for any user to whom you wish to deny user instances, you would need to create a %USERPROFILE%\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS folder, then remove the user’s NTFS permissions on the folder. Since this is a major pain in the caboose, as well as easy to miss doing for any given account, it’s the sort of thing you might want to consider automating via a default login script or similar mechanism. BTW, if you do make this permission alteration, other processes such as backups may be affected, so you might want to do some pretty thorough testing before, say, pushing this sort of thing out to your entire domain…
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) is a pretty horrible idea. Unfortunately, if you want to grant “almost unrestricted” SqlClientPermission that excludes the right to connect to user instances, the CAS permission configuration UIs won’t be of much help. Instead, you’ll need to define the permission “manually”. The XML definition for such a permission might look like this (watch out for fakey angle brackets if you copy and paste):
class=”System.Data.SqlClient.SqlClientPermission, System.Data, Version=18.104.22.168, Culture=neutral, PublicKeyToken=b77a5c561934e089″ version=”1″
‹add KeyRestrictions=”User Instance=;” KeyRestrictionBehavior=”PreventUsage” /›
If you want to grant additional permissions to a network-sourced assembly so that it can connect to a SQL Server instance running any server on your network, I’d recommend you use something like the above permission rather than an unrestricted SqlClientPermission grant. Otherwise, you might unwittingly be granting that assembly essentially unrestricted permissions over the machine on which it’s executing via code run within a user instance.
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… 😉