Using [SqlClientPermission]

 

All Input Is Evil! (Michael Howard, Writing Secure Code, Microsoft Press)

If we accept this statement, then we should be enforcing checks on database connection strings. The framework provides us with a means to do exactly this, but this capability is not often utilised. I suspect that part of the reason for this is that the capability is not well documented at all. Indeed, the class reference itself does not carry code samples (a common problem with the reference documentation in my experience).

The framework provides code access security support via the SqlClientPermissionAttribute class .

Before going any further, I would like to highlight a gotcha that unless the Unrestricted=false attribute property is not set then no security checks will be applied. Quite why this defaults to true I don't know, and why amending any other property value doesn't trigger the value to false is a little odd, but there you are… To be fair though, this is documented clearly in the reference.

The attribute can be applied to either classes or methods, but typically I would expect to attribute at class-level to minimize the effort required to implement security.

This sample specifies the permitted connection string

using System;
using System.Data;
using System.Data.SqlClient;
using System.Security;
using System.Security.Permissions;

namespace SecureDataAccessLibrary
{
  [SqlClientPermission(
    SecurityAction.PermitOnly,
    Unrestricted=false,
    ConnectionString="Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;")]
  public sealed class SecureDataAccess
  {
    private SecureDataAccess() { }
    
    public static void Connect(string connectionString)
    {
      using(SqlConnection connection = new SqlConnection(connectionString))
      {
        try
        {
          connection.Open();
        }
        catch (SecurityException)
        { }
        catch (SqlException)
        { }
        finally
        {
          connection.Close();
        }
      }
    }
  }
}

Calling this code:

// standard security
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=PASSWORD;"); // fails
// integrated security
SecureDataAccess.Connect("Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=True;"); // fails
SecureDataAccess.Connect("Data Source=.;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // fails
SecureDataAccess.Connect("Data Source=(local);Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // succeeds
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;Connection Timeout=30;"); // fails

As you can see, only the connection string that exactly compares with the permitted string succeeds. The security will not attempt to resolve the connection string, so the attempt will fail even if SERVERNAME is the (local) machine. It will not resolve equivalent connections strings either, so in the example above the Trusted_Connection call will fail even though the string is equivalent. You may decide to specify multiple permitted connection strings because of this:

specify multiple permitted connection strings

using System;
…

namespace SecureDataAccessLibrary
{
  [SqlClientPermission(
    SecurityAction.PermitOnly,
    Unrestricted=false,
    ConnectionString="Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;")]
  [SqlClientPermission(
    SecurityAction.PermitOnly,
    Unrestricted=false,
    ConnectionString="Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=True;")]
  public sealed class SecureDataAccess
  {
    …
  }
}

Calling this code:

// standard security
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=PASSWORD;"); // fails
// integrated security
SecureDataAccess.Connect("Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=True;"); // succeeds
SecureDataAccess.Connect("Data Source=.;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // fails
SecureDataAccess.Connect("Data Source=(local);Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // succeeds
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;Connection Timeout=30;"); // fails

All-in-all, just specifying connection strings is an inflexible approach. However… there are cases where this is entirely appropriate. If you have an in-house (bespoke / proprietary) web application and wish to ensure that your application is tied to a particular system configuration, then this will suit your needs. It also has the advantage that (as a side-effect) it ensures that all connection strings will match, which enables ADO.NET connection pooling to operate efficiently.

The downside of such fixed connection strings is that a change of infrastructure configuration could well require a recompilation of the application. However, this may not be too onerous for in-house applications.

A more flexible approach is offered by using the support for KeyRestrictions. Use this capability to control the verbs used by a connection string. The following example ensures that the Integrated Security=SSPI; verb is used, but does not specify the SERVERNAME or DATABASENAME to be used, which means that the application will not need to be recompiled for different infrastructure configurations. You can also specify additional verbs to be permitted.

specify additional verbs to be permitted

using System;
…

namespace SecureDataAccessLibrary
{
  [SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false,
    ConnectionString="Integrated Security=SSPI;",
    KeyRestrictionBehavior=KeyRestrictionBehavior.AllowOnly,
    KeyRestrictions="Data Source=;Initial Catalog=;Integrated Security=;Connection Timeout=;")]
  public sealed class SecureDataAccess
  {
    …
  }
}

Calling this code:

// standard security
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=PASSWORD;"); // fails
// integrated security
SecureDataAccess.Connect("Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=True;"); // fails
SecureDataAccess.Connect("Data Source=.;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // succeeds
SecureDataAccess.Connect("Data Source=(local);Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // succeeds
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // succeeds
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;Connection Timeout=30;"); // succeeds

KeyRestrictions also support the specification of verb to prevent. Generally, this is considered the wrong approach to take from the perspective of secure development (because it requires that you list all prevented verbs rather than just the verbs you want to accept) but here it is for the sake of completeness:

KeyRestrictions

using System;
…

namespace SecureDataAccessLibrary
{
  [SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false,
    KeyRestrictionBehavior=KeyRestrictionBehavior.PreventUsage,
    KeyRestrictions="Network Library=;")]
  public sealed class SecureDataAccess
  {
    …
  }
}

Calling this code:

SecureDataAccess.Connect("Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=DATABASENAME;User ID=USERNAME;Password=PASSWORD;"); // fails

To wrap up, there is the capability to prevent blank passwords by using the AllowBlankPassword=false property value. There is, however, a gotcha… At first glance, it looks like you can add the attribute to prevent blank passwords:

AllowBlankPassword

using System;
…

namespace SecureDataAccessLibrary
{
  [SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false, AllowBlankPassword=false)]
  public sealed class SecureDataAccess
  {
    …
  }
}

However, doing this doesn't work… any connection string fails with the following SecurityException message (which is not very informative):

Output

Request for the permission of type System.Data.SqlClient.SqlClientPermission, System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.

The reason why it fails in this way is that the ConnectionString property defaults to an empty string, which means that the code above is equivalent to this:

AllowBlankPassword (2)

using System;
;…

namespace SecureDataAccessLibrary
{
  [SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false, AllowBlankPassword=false, ConnectionString="")]
  public sealed class SecureDataAccess
  {
    ;…
  }
}

The result of this is to prevent any connection string that isn't an empty string! The correct way to use the AllowBlankPassword property is in conjunction with the KeyRestrictions property:

AllowBlankPassword + KeyRestrictions

using System;
…

namespace SecureDataAccessLibrary
{
  [SqlClientPermission(SecurityAction.PermitOnly, Unrestricted=false,
    ConnectionString="", AllowBlankPassword=true,
    KeyRestrictionBehavior=KeyRestrictionBehavior.AllowOnly,
    KeyRestrictions="Data Source=;Initial Catalog=;User Id=;Password=;Connection Timeout=;")]
  public sealed class SecureDataAccess
  {
    …
  }
}

Calling this code:

// standard security
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User Id=USERNAME;Password=PASSWORD;"); // succeeds
// integrated security
SecureDataAccess.Connect("Server=SERVERNAME;Database=DATABASENAME;Trusted_Connection=True;"); // fails
SecureDataAccess.Connect("Data Source=.;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // fails
SecureDataAccess.Connect("Data Source=(local);Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;"); // fails
SecureDataAccess.Connect("Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;Connection Timeout=30;"); // fails

Actually, you don't need to specify AllowBlankPassword=true because it is true by default in the 1.1 framework onwards, but it is nice to be verbose in your code.

In conclusion, the SqlClientPermissionAttribute class offers a wide degree of control when specify security restrictions declaratively. I think that Microsoft needs to document such features much better if it is to improve the overall level of quality in the .net application ecosphere. It is perhaps indicative of the failure of Microsoft to successfully evangelise secure development that the Google searches return such a lamentably small set of results.

From the MSDN topic Writing Secure ADO.NET Code, astonishingly, Code Access Security and ADO.NET has no code samples and Verify ADO.NET Code Access Using Security Permissions only shows the ConnectionString property alone. Secure ADO.NET Coding Guidelines doesn't even mention SqlClientPermission and neither does Secure ADO.NET Connections even though it has a section called Use Caution When Constructing Connection Strings from User Input.

See also: ConnectionStrings.com