Hopping databases from the SAFE SQLCLR permission level

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
or
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.:1

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.






1 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.

Leave a Reply

Your email address will not be published. Required fields are marked *