News Stay informed about the latest enterprise technology news and product updates.

What can .NET code do from within SQL Server: Safety levels

Learn what .NET code can do from within SQL Server in this excerpt from "A First Look at SQL Server 2005 for Developers."

A First Look at SQL Server 2005 for Developers The following excerpt, courtesy of Addison-Wesley, is from Chapter 6 of the book "A First Look at SQL Server 2005 for Developers" written by Bob Beauchemin, Niels Berglund and Dan Sullivan. Click for the complete book excerpt series or purchase the book.

What can .NET code do from within SQL Server: Safety levels

SQL Server permissions take care of dealing with security from a SQL Server–centric point of view. But if a .NET stored procedure can load arbitrary assemblies from the file system or the Internet, the security of the SQL Server process could be compromised. The first concern is taken care of by the new .NET hosting APIs. Aside from a specific subset of the .NET base class libraries, SQL Server handles all assembly loading requests. You cannot instruct SQL Server to load arbitrary assemblies from the local file system or the Internet. In addition, the IL code in each .NET assembly is checked for validity when CREATE ASSEMBLY is run. On a more granular level, .NET not only uses SQL Server user-based permissions, but also .NET code access security.

Introduction to Code Access Security

.NET code access security is meant to check the permissions of code before executing it, rather than checking the permissions of the user principal that executes the code. Code access security determines how trustworthy code is by mapping pieces of evidence -- such as where the code was loaded from, whether the code was signed with a digital signature, and even which company wrote the code -- to permissions. This evidence is collected and inspected when the code is loaded. Code access security matches evidence against the security policy, to produce a set of permissions. Security policy is a combination of enterprise security policy, machine policy, user-specific policy, and AppDomain security policy. The general concept of determining permissions from evidence and security policy is discussed in the .NET documentation.

In most ordinary .NET programs, code access security is used when code is loaded, to determine the location (most likely, the file system or network) of the code. .NET assemblies loaded from SQL Server, however, can only be loaded from two places:

  • The SQL Server database itself (user code must be cataloged and stored in the database)

  • The global assembly cache (Framework class libraries, FX, only)

When CREATE ASSEMBLY is run, the code is analyzed and any outside code that it calls (dependent assemblies) is also cataloged and stored inside SQL Server. Code location evidence means very little for SQL Server assemblies, because .NET code is never loaded from the Internet or the local file system. SQL Server enforces a stronger security policy, using HPAs as well as three levels of security that are declared when the assembly is cataloged. If SQL Server determines that the assembly contains code it shouldn't be allowed to execute, CREATE ASSEMBLY simply fails. The .NET Framework class libraries are the only code loaded from the global assembly cache, and they are subject to strong constraints, which we will discuss shortly.

Code access security enforces permission-based security through HPAs at execution time as well. With each access to any resource that requires a permission (such as a file or DNS resolver), the CAS access security inspects the call stack to ensure that every piece of code, up to the original caller, has the appropriate permission. This is known as the stack walk.

Between code analysis at create assembly time and the execution-time stack walk, the .NET code access security system and SQL Server's extensions to strengthen it ensure that no code is called that could compromise the stability and security of the system in unforeseen ways. This is a big improvement over pre–SQL Server 2005 compiled code, which consisted of extended stored procedures and COM-based components.

Code Access Security and .NET Assemblies

Because SQL Server controls assembly loading, as well as facets of .NET code execution, it can also assign a custom "safety level" to an assembly. Safety levels determine what non–SQL Server resources .NET assemblies can access. There are three safety levels: SAFE, EXTERNAL_ACCESS, and UNSAFE. These are specified on CREATE ASSEMBLY and changed by using ALTER ASSEMBLY under the control of the database administrator. The different safety levels approximately correspond to the following.

  • SAFE -- Can access computational .NET classes. Safety is equivalent to a T-SQL procedure.

  • EXTERNAL_ACCESS -- Can access all code that SAFE mode can and, in addition, items like the file system and other databases through ADO.NET. Approximately equivalent to a T-SQL procedure that can access some of the system extended stored procedures.

  • UNSAFE -- Can access most (but not all) code in a subset of the FX assemblies. Approximately equivalent to a user-written extended stored procedure without the bad pointer and memory buffer overflow problems.

What these different levels can do is enforced by a permission set. Each assembly author indicates which classes and methods might threaten the stability of SQL Server by decorating classes and methods with Host Protection Attributes. These Host Protection Attributes are enforced at execution time (or possibly at JIT-compile time), based on the code's security level. Because SQL Server HPAs and permission sets are documented, third-party library writers are free to instrument their libraries to be sensitive to SQL Server's permissions. Table 6-2 shows a summary of the general behavior of each of the named permission sets.

In addition, SQL Server 2005 will load only a certain subset of the Framework class libraries at runtime. The list consists of the following:

  • mscorlib

  • System

  • System.Data

  • System.Data.SqlXml

  • System.Xml

  • System.Security

  • System.Web.Services

  • Microsoft.VisualBasic

  • Microsoft.VisualC

  • CustomMarshalers

  • System.Runtime.Remoting

  • System.Runtime.Serialization.Formatters.Soap

In addition to these libraries, SQL Server 2005 may load libraries that are referenced by these libraries. For example, System.Data.dll may have a reference to System.Drawing.dll, which may be loaded, but System.Data.dll will be coded not to reference certain entry points in System.Drawing.dll. The static list of Framework class libraries is enforced at runtime rather than "catalog time;" attempting to load a library not on the list will not throw an exception at runtime.

Let's go through an example of how safety levels would work in practice. The following short program accesses a search service on the Web. This code uses System.Web.

public static String WebSearch(String subject) {
String url =

//Submit Web request and get response
url = String.Concat(url, subject);
WebRequest req = WebRequest.Create(url);
WebResponse result = req.GetResponse();

Stream ReceiveStream = result.GetResponseStream();
String outstring = "";

//Load response stream into string
Byte[] read = new Byte[1024];
int bytes = ReceiveStream.Read(read, 0, 1023);

while (bytes > 0)
  outstring = String.Concat(outstring,
    System.Text.Encoding.ASCII.GetString(read, 0, bytes));
  bytes = ReceiveStream.Read(read, 0, 512);
return outstring;

We'll use it as part of a class that is compiled into an assembly. Now, we define the assembly to SQL Server, using two CREATE ASSEMBLY statements and two symbolic names with different safety levels.

—Register the unrestricted access privileged assembly
— Create assembly with external access
create assembly searchEA
from 'c:typessearchEA.dll'
with permission_set = external_access
— Create assembly without external access
create assembly searchSafe
from 'c:typessearchSafe.dll'
with permission_set = safe

Then, use the symbolic names to define two versions of the same user-defined function.

— Create function on assembly with external access
create function WebSearchEA(@sym nvarchar(10))
returns real
external name searchEA:SearchEngine::WebSearch

— Create function on assembly with no external access
create function WebSearchSafe(@sym nvarchar(10))
returns real
external name searchSafe.SearchEngine.WebSearch

— now, attempt to use them
declare @a REAL

— this will work properly
SET @a = dbo.GoogleSearchEA('SQL+Server+Yukon')

— this fails with a code access security violation
SET @a = dbo.WebSearchSafe('SQL+Server+Yukon')

What happens when a stored procedure that has limited access (SAFE) attempts to call a stored procedure that is declared as UNSAFE? Because of the way the stack walk works, this enforces security in a way that is even more restrictive than SQL Server ownership chains. Remember that an ownership chain is only checked when it is broken -- that is, when the caller is not the owner of the object she attempts to access. The stack walk checks permissions all the way up the stack; failing permission at any level will cause the call to fail. Because System.Security.dll is not allowed, there is no way for an assembly to do an Assert and subvert the stack walk. This may also have some performance impact since it implies that every stack walk goes all the way up to the top. Code access security provides an additional level of security, regardless of the user principal that executes the code.

Click to go back to the first excerpt in this series: New security features in SQL Server 2005

Click for the book excerpt series or visit here to obtain the complete book.

Dig Deeper on SQL Server Security

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.