I may have joined the wrong side : SQL Server

Hopping databases from the SAFE SQLCLR permission level

calinoiu — Sat, 18 Feb 2006 18:20:00 GMT

I've seen quite a few articles over the past few months that make the assumption that one can only connect to the hosting database from SQLCLR code running at the SAFE permission level. I can't seem to find any official MSDN documentation that would directly reinforce this misconception, so I'm guessing that it stems from the limitation of the SqlClientPermission at the SAFE level to only allow use of the following connection strings (with optional specification of the Type System Version parameter):

context connection=true

context connection=yes

Unfortunately, the documentation for the SqlClientPermission.Add method is a wee bit ambiguous with respect to the effect of preventing arbitrary target database specifications in the connection string, and one might easily be led into believing that preventing use of the database parameter will prevent connections to unintended databases. However, while it will prevent mucking about with the connection string, that's not enough to prevent connecting to other databases.

For starters, the SqlConnection object has a ChangeDatabase method that allows one to target another database after an initial connection has already been established.e.g.:¹

using (SqlConnection connection = new SqlConnection(@"Data Source=(local);Initial Catalog=AllowedDB;Integrated Security=True"))
{
 connection.Open();
 connection.ChangeDatabase("ForbiddenDB");

 using (SqlCommand command = connection.CreateCommand())
 {
  command.CommandType = CommandType.Text;
  command.CommandText = "SELECT DB_NAME()";
  Console.WriteLine((string)command.ExecuteScalar());
 }
}

Now, one might argue that this is actually a bug, and that ChangeDatabase method ought to demand SqlClientPermission for the target database before making the switch. However, it's quite possible to bypass the SqlClient layer entirely and make the switch inside database code, so any additional protection at the SqlClient level would only provide a false sense of security and probably isn't worth implementing.

The next approach invokes making a direct database context switch from T-SQL using the USE statement. e.g.:

using (SqlConnection connection = new SqlConnection(@"Data Source=(local);Initial Catalog=AllowedDB;Integrated Security=True"))
{
 connection.Open();

 using (SqlCommand command = connection.CreateCommand())
 {
  command.CommandType = CommandType.Text;

  command.CommandText = "USE ForbiddenDB";
  command.ExecuteNonQuery();

  command.CommandText = "SELECT DB_NAME()";
  Console.WriteLine((string)command.ExecuteScalar());
 }
}

Effectively, this means that SqlClientPermission provides no protection against using any particular database within a given SQL Server instance. You might guess that the SQLCLR might add some additional protection against database switching from within hosted code, but you'd be wrong. The above techniques work just as well against the SQLCLR context connection as they do against a plain, old vanilla connection as shown above. SAFE or not, SQLCLR assemblies can connect to any database in their host SQL Server instance assuming, of course, that user permissions also allow the connection.

¹ The DB_NAME function, when called with no parameters, returns the name of the current database. If you haven't switched the context database, the function would be expected to return the name of the database against which the connection was originally established.

Secure by de...what?

calinoiu — Sun, 04 Dec 2005 17:31:00 GMT

Surprise!

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 SD³+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.

Communications?

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'
GO

RECONFIGURE
GO

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):

‹IPermission 
 class="System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" version="1"
 AllowBlankPassword="True"›

 ‹add KeyRestrictions="User Instance=;" KeyRestrictionBehavior="PreventUsage" /›
‹/IPermission›

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 SD³+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... ;)

Speculations on the suprisingly under-documented world of SQL CLR CAS permission grants

calinoiu — Sun, 06 Nov 2005 01:57:00 GMT

I'd been hoping that the details of the SQL CLR CAS permission sets might make it into the SQL Server Books Online or other relevant documentation by the RTM timeframe. Unfortunately, I can't seem to find anything that even begins to resemble a listing of the permissions, never mind coverage of some of the pickier details of their assessment and consequences. I'd already started trying to investigate some of this on my own during the beta and, after spending a bit more time with the RTM build (i.e.: pretty much wasting a perfectly good Saturday), here's what I think I've discovered so far... (Click here to read the whole kit and kaboodle.)