Code Access Security - SQL Server 2008

While T-SQL now has far more granular permissions than previously, it’s still relatively crude compared with the control available with .NET. From the point of view of security, the most important aspect of .NET–SQL Server integration is that it allows the database developer to take advantage of Code Access Security (CAS). CAS is the .NET mechanism that allows developers to state explicitly which permissions their code needs to run (for example, the permission to access unmanaged code or the permission to access some part of the file system), and also to grant or deny permission to specific code to perform some action.

Four fundamental actions can be performed with CAS permissions:

  • Assert: Asserting a permission allows a section of code to perform a given action, even if the method (which may be in a different assembly) that called the current method doesn’t have that permission. The current code itself must have permission to perform the action, or the assert will fail.
  • Deny: Denying a permission will cause any attempts further down the method call stack to perform the prohibited action or to demand the same permission to fail. However, the denial may be overridden by a subsequent call to Assert() or PermitOnly().
  • Demand: Demanding a permission signals that the code requires this permission to run. The demand will be granted only if hasn’t been denied higher up the call stack or permitted only on a different resource.
  • PermitOnly: You can grant access to only a specific resource, and refuse access to other resources that require the same permission. PermitOnly can be overridden by an assert or a deny permission, but not by another PermitOnly.

Imperative and Declarative CAS

CAS can be implemented in two ways: declaratively, using attributes to demand and assert permissions; and imperatively, by calling methods on individual permission objects (these are defined in the System.Security.Permissions namespace, and derive from the CodeAccess Permission base class). Permissions can also be configured for entire assemblies, groups of assemblies, or even the entire local machine or domain using the CAS policy management application, caspol.exe, or the .NET Configuration Wizard.

CAS permissions are slightly misnamed—in many cases, they’re actually broad groups of permissions that contain individual permissions. For example, the Security permission contains subpermissions to enable assembly execution, to allow calls to unmanaged code, and so on. To request a specific permission using imperative security, you instantiate the corresponding .NET class, passing in any parameters you need to identify precisely what your code needs permission to do, and then call the appropriate Demand(), Assert(), Deny(), or PermitOnly() method on the instance. For example, to demand Read permission on the file C: emp.txt, use this code:

FileIOPermission perm = new FileIOPermission(FileIOPermissionAccess.Read,
@"C: emp.txt");

To do the same using declarative security, place .NET attributes in front of the assembly, class, or method that requires the permission, passing in the appropriate action as a SecurityAction enumeration member:

[FileIOPermission(SecurityAction.Demand, Read=@"C: emp.txt")]
public static string ReadTempData()
// Method body...

Using CAS with SQL Server

To see how this works in practice, let’s look at a simple example. You’ll write a C# DLL called FileReader.dll that reads a list of names of sales representatives in a text file line by line, and returns the contents as a string array. This will be called by a SQL Server assembly (GetSalesAssem.dll), also written in C#, which looks up these names in the AdventureWorks database and extracts the sales figures for the year to date and last year, and returns these as a result set.

Here is the code for FileReader.cs:
using System.Collections;
using System.IO;
using System.Security.Permissions;
namespace Apress.SqlServer2008.SecurityChapter
public class FileReader
public static string[] ReadFile(string filename)
FileIOPermission perm = new FileIOPermission(
FileIOPermissionAccess.Read, filename);
ArrayList names = new ArrayList();
FileStream fs = new FileStream(filename, FileMode.Open,
StreamReader sr = new StreamReader(fs);
while (sr.Peek() >= 0)
return (string[])names.ToArray(typeof(string));

You use CAS to demand permission to read the file that’s passed in as a parameter to the method that does the file reading. If the caller doesn’t have permission to access the file, this line will throw an exception. You don’t handle this exception in this class, because you want the exception to be available to the caller. You could create a new exception with its InnerException set to the original Exception object, but this involves creating an extra object that you don’t really need.

Compile this C# file on the command line using the following statement:

csc /t:library FileReader.cs

Next comes the SQL Server assembly. This reads the names from the file specified in the parameter and uses these to construct a SQL statement that gathers the sales data for each employee. You then execute this statement against the server and return the results as a data reader to SQL Server:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Security;
using System.Security.Permissions;
using System.Text;
using Microsoft.SqlServer.Server;
namespace Apress.SqlServer2008.SecurityChapter
public class SalesFetcher
public static void GetSalesForNames(SqlString filename)
// Create a PermissionSet to hold the permissions we want to grant
PermissionSet perms = new PermissionSet(PermissionState.None);
// Ensure that only correct file can be accessed through this method
FileIOPermission ioPerm = new FileIOPermission(
FileIOPermissionAccess.Read, @"C: ames.txt");
// Permit access to SQL Server data
SqlClientPermission sqlPerm = new SqlClientPermission(
sqlPerm.Add("context connection=true", "",
// Get the names from the text file as a string array
string[] names = FileReader.ReadFile(filename.ToString());
// Build SQL statement
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT emp.EmployeeID,
sp.SalesYTD + sp.SalesLastYear AS RecentSales
FROM Sales.SalesPerson sp
INNER JOIN HumanResources.Employee emp
ON emp.EmployeeID = sp.SalesPersonID
WHERE sp.SalesPersonID IN
SELECT emp.EmployeeID
FROM HumanResources.Employee emp
INNER JOIN Person.Contact c
ON c.ContactID = emp.ContactID
WHERE c.FirstName + ' ' + c.MiddleName + ' ' +
IN (");
// Concatenate array into single string for WHERE clause
foreach (string name in names)
sb.Append("', ");
sb.Remove(sb.Length - 2, 2);
// Execute the SQL statement and get back a SqlResultSet
using (SqlConnection cn = new SqlConnection(
"context connection=true"))
SqlCommand cmd = new SqlCommand(sb.ToString(), cn);
SqlDataReader dr = cmd.ExecuteReader();
// Send success message to SQL Server and return SqlDataReader
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("Command(s) completed successfully.");
catch (Exception e)
SqlPipe pipe = SqlContext.Pipe;
pipe.Send("Error executing assembly");

This is where the power of CAS really shows itself, because you grant access to only one specific file (here, C: ames.txt) by calling PermitOnly() on your FileIOPermission object. Any attempt to access any other file through this assembly will fail. Now, in this case, you could have just hardcoded the value into the method call on the FileReader class, but CAS also lets you specify an array of files to which you want to permit access through this method; in which case, hard-coding the filename wouldn’t have been an option.

As well as the FileIOPermission, notice that you also require a SqlClientPermission, which permits you to connect to SQL Server via the context connection; otherwise, calling PermitOnly on the FileIOPermission would prevent you from accessing SQL Server data. You create a new SqlClientPermission object with no permissions to connect to any SQL Server, and then call the Add method on this permission object, allowing connection on the context connection with no exceptions. It’s not possible to call PermitOnly() individually on more than one permission in a method, so you need to create a PermissionSet object, add these two permissions to it, and then call PermitOnly() on the PermissionSet.

To compile this file, run the following command:

csc /t:library /r:FileReader.dll /r:"<path to sqlaccess.dll>" GetSalesAssem.cs

where <path to sqlaccess.dll> is

<Program Files>Microsoft SQL Server<MSSQL instance>MSSQLBinnsqlaccess.dll

For example, on our system, this is the following path:

C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnsqlaccess.dll

Now you just need to register the assembly in SQL Server and create a procedure that will call this method. The stored procedure has one parameter, of type nvarchar, which will automatically beconverted by the CLR to the corresponding .NET type (System.String), which is the type of the parameter to your .NET method:

USE AdventureWorks
FROM 'C:ApressAcceleratedSQLServer2008Chapter6GetSalesAssem.dll'
CREATE PROCEDURE uspGetSalesForNames @filename nvarchar(255)

To test this, create a new text file called names.txt in the root of the C: folder that contains the following names:

Michael G Blythe
Garrett R Vargas
Amy E Alberts

and call the new procedure:

EXEC uspGetSalesForNames 'C: ames.txt'

You should see the employee ID and sales data for each of these employees. You can check that CAS is working by making a copy of this file called names2.txt and running the procedure against that. You’ll see an error message and stack trace indicating that the attempt to demand the FileIOPermission failed.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

SQL Server 2008 Topics