UDT Safety and IP Security

Depending on what your CLR Executable needs to do, you might find that SQL Server and the Framework won't let you deploy it until you alter the Project Permission Level settings. I discuss the nitty-gritty details of code safety in another chapter, but I did want to include a brief description of the types of operations that might prevent your code from running as "Safe"the default setting.

Consider that all of the examples implemented so far in this chapter have been run as Safe. I expect that your DBA will appreciate you sticking to CLR executables that are "safe," but this might limit the functionality you need to implement in your CLR code. The other settings allow your application more freedom in what it can do. However, with that freedom comes more responsibility. Under some circumstances, an "Unsafe" CLR executable can bring down SQL Server and the system itself.

The three possible settings are:

• Safe(SAFE): This setting permits your CLR executable to perform only internal computation and local data access. Local data access means you can open connections only to the local context connectionnot to an external server. Yes, SAFE is the most restrictive permission set. CLR executables executed by an assembly with SAFE permissions cannot access external system resources such as files, the network, environment variables, or the Registry. In addition, you'll find that a SAFE assembly can't access many of the more sophisticated Framework classes, like encryption.

• External (EXTERNAL_ACCESS): This setting permits your CLR executable to have access to external system resources such as files, networks, environmental variables, and the Registry. It's required if you need to make a non-context connection.

• Unsafe (UNSAFE): This setting allows assemblies (almost) unrestricted access to resources, both within and outside SQL Server. Code executing from within an UNSAFE assembly can call unmanaged code. This is clearly the most dangerous of all settings and should be avoided whenever possible. Even UNSAFE prevents access to a number of .NET Framework classes. These include compilers, window managers, enterprise service libraries, and other classes where Microsoft did not think it makes sense for CLR executables to access.

I also want to quote from the documentationjust to be sure you see this warning in Microsoft's own words:

SAFE is the recommended permission setting for assemblies that perform computation and data management tasks without accessing resources outside SQL Server. EXTERNAL_ACCESS is recommended for assemblies that access resources outside SQL Server. EXTERNAL_ACCESS assemblies by default execute as the SQL Server service account. It is possible for EXTERNAL_ACCESS code to explicitly impersonate the caller's Windows Authenticated security context. Since the default is to execute as the SQL Server service account, permission to execute EXTERNAL_ACCESS should only be given to logins trusted to run as the service account. From a security perspective, EXTERNAL_ACCESS and UNSAFE assemblies are identical. However, EXTERNAL_ACCESS assemblies provide various reliability and robustness protections that are not in UNSAFE assemblies. Specifying UNSAFE allows the code in the assembly to perform illegal operations against the SQL Server process space and hence can potentially compromise the robustness and scalability of SQL Server.

To this warning, I would like to add that the designations are really backward. That is, "safe" code is really totally untrusted. Because it's untrusted, it won't be permitted to do anything, access anything, or branch anywhere that would cause damage (other than writing garbage to the database, dropping objects, and doing other harm with the scope of the rights granted the user executing the code. On the other hand "Unsafe" code must be totally trusted. Because it's trusted, it is permitted to do anything, anywhere on your system or any system in sight.

Exposing Your Intellectual Property

By default, Visual Studio 2005 saves the source code for your executable into the system catalogs along with the binary DLL. This means that anyone with access to the deployed database can do something like this (as shown in Figure 13.109):^[25]

^[25] See ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.VisualStudio.v80.en/WD_ADONET/html/c475eb89-c9e0-4f09-a2a0-bbd237434ea3.htm

Figure 13.109. Extracting the source code for a deployed CLR executable.

Okay, now that the data is returned, it's simply a matter of selecting the source from the returned content, saving it to a .VB file, and opening the .VB (or .CS) file with Visual Studio. There, you will see the entire source filecomments and all. Sigh. This means that database you deployed with your CLR executables in place has also given away your business's intellectual property. Why is this? Well, take a look at the output from a typical build (as shown in Figure 13.110).

Figure 13.110. The result of a typical build.

Notice that the source code (the .vb file), as well as the binary (.dll) and all of the other files, Visual Studio uses to manage your project are written to the database catalogs.

Protecting Your Intellectual Property

So, how do you protect your IP? Well, according to Microsoft, you can't use Visual Studio to deploy your CLR executableyou'll have to build your own deployment scripts. I'm going to leave the details about how to do this to your DBA. Let's just say that you can get started in this process by scripting the deployed assembly and executable, dropping the assembly and all dependencies, and editing the CREATE ASSEMBLY scriptremoving the ADD FILE statements, as shown in Figure 13.111.

Figure 13.111. The unaltered CREATE ASSEMBLY script.

Sure, the CREATE ASSEMBLY T-SQL function can take the name of your DLL file, so you don't have to type in the binary representation of the executableunless you're being paid by the hour.