I am surprised at the number of SQL Server DBA's that I meet who refuse to look at SQLCLR, claiming that it can cause security and stability issues on their production servers. Yet these same DBAs often have xp_cmdshell enabled on their production servers to perform various tasks.

This is the first article in a series that will show how to use the SQLCLR to create a safer, more secure environment rather than enabling xp_cmdshell in SQL Server 2005/2008. The first thing that you have to understand about using CLR inside of SQL Server is that it can be as safe or as dangerous as you would like. (see CLR Hosted Environment in BOL) There are three different safety levels can be applied to SQLCLR assemblies:

1. SAFE: allows local data access and internal computational access only.
2. EXTERNAL_ACCESS: has all the permissions of SAFE but with the added ability to access external system resources such as files, the network, environment variables, and the registry.
3. UNSAFE: allows unrestricted access to resources, both inside and outside of SQL Server. UNSAFE assemblies can call unmanaged code and affect the SQL Server process.

Assemblies requiring EXTERNAL_ACCESS or UNSAFE permissions have to be signed by a key which can then be used to create a login and database user in SQL Server to grant EXTERNAL ACCESS or UNSAFE ASSEMBLY rights to, or the database must be set TRUSTWORTHY ON. Using signed assemblies requires more effort to setup, but is more secure than setting TRUSTWORTHY ON.

For further coverage of this topic please see Setting Database Trustworthy On or Signing Assemblies with Certificates. To learn how to sign an assembly with a key please read Signing an Assembly with a Certificate (Visual Studio)

Now that the basics have been covered to set the stage, why exactly is SQLCLR a better choice than xp_cmdshell?

There are a few reasons why SQLCLR should be considered over xp_cmdshell. The primary reason is that SQLCLR functions and procedures have very specific operations that they can do, whereas xp_cmdshell provides full access to the command prompt with whatever level of permissions that the SQL Service Account has if they are a sysadmin, or whatever proxy permissions have been setup if they are not a sysadmin. This is why enabling xp_cmdshell is risky, versus SQLCLR where the risk is mitigated to the operation that the CLR object performs. Lets look at some code and how it works.

The goal for this article is to replace TSQL code similar to the following:

DECLARE @cmd varchar(100)
DECLARE @path varchar(100)
SET @Path = 'c:\Windows\'
SET @cmd = 'dir ' + @path + ' /A /OS /-C'
CREATE Table #MyTable
(Results varchar(500))
INSERT INTO #MyTAble
EXEC XP_CMDSHELL @cmd
SELECT LEFT(Results, 20) [create_date], 
 CASE LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) WHEN '<DIR>' THEN 1 ELSE 0 END as is_directory,
 CASE LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) WHEN '<DIR>' THEN NULL ELSE CAST(LTRIM(RTRIM(SUBSTRING(Results, 21, 18))) AS BIGINT) END as size_in_bytes,
 SUBSTRING(Results, 40, Len(Results)) AS [name]
FROM #MyTable
WHERE ISNULL(PATINDEX('%__/__/____%', Results), 0) != 0
DROP TABLE #MyTable

One of the drawbacks of the above TSQL code is that since it calls EXEC xp_cmdshell, it cannot be created as a table valued function, and instead has to be created as a stored procedure. The problem with this is that you can't call the stored procedure and insert its output table into a table because a nested INSERT INTO is not allowed in SQL Server. SQLCLR doesn't have any of these limitations, and creating a table valued function is really quite simple.

Table Valued Functions in SQLCLR have two parts, a base method which outputs a object collection to its fillrow method that actually breaks each object down into the output columns to be returned to the TSQL stack. To make our function both flexible and useful, it will have two parameters, the path to get the Directory Information from, and a filter string that can be used to filter the results before they are returned to SQL.

To get the directory information in .NET, we will need to use the System.IO namespace with this statement.

using System.IO;

The DirectoryInfo class can then be used to get the FileSystemInfo collection based on the path provided as an input parameter as follows.

[SqlFunction(
 Name = "os_directory_info",
 FillRowMethodName = "os_directory_info_FillRow",
 TableDefinition = @"name nvarchar(4000), is_directory bit, size_in_bytes bigint, create_date datetime,
 last_written_to datetime, last_accessed datetime, attributes nvarchar(4000)")]
 public static IEnumerable os_directory_info(SqlString path, SqlString filter)
 {
 DirectoryInfo di = new DirectoryInfo(path.Value);
 if (filter.IsNull || filter.Value == string.Empty)
 return di.GetFileSystemInfos();
 else
 return di.GetFileSystemInfos(filter.Value);
 }

The .GetFileSystemInfos() method can have no input parameters or filtering string input parameter if one is provided to the TVF from TSQL. One thing about CLR TVF's, as previously mentioned, there is the collector method. In this case os_directory_info, which creates a collection that is returned to its FillRow method, is specified by the FillRowMethodName attribute above. Since the return is a IEnumerable, the class file for the TVF must also inherit the System.Collections namespace and requires this statement.

using System.Collections;

Once the collection returns from the collector method, it is then passed one entry at a time to the FillRow method, which has a single input object and will have a output object for each column defined in the TableDefinition attribute of the collector method.

private static void os_directory_info_FillRow(object obj, out SqlString altname, out SqlBoolean is_directory,
 out SqlInt64 size, out SqlDateTime createdatetime, out SqlDateTime lastwritedatetime, 
 out SqlDateTime lastaccessdatetime, out SqlString attrib)
 {
 if (obj is FileInfo)
 {
 FileInfo fsi = (FileInfo)obj;
 altname = fsi.Name;
 is_directory = SqlBoolean.False;
 size = fsi.Length;
 createdatetime = fsi.CreationTime;
 lastwritedatetime = fsi.LastWriteTime;
 lastaccessdatetime = fsi.LastAccessTime;
 attrib = fsi.Attributes.ToString();
 }
 else
 {
 FileSystemInfo fsi = (FileSystemInfo)obj;
 altname = fsi.Name;
 is_directory = SqlBoolean.True;
 size = SqlInt64.Null;
 createdatetime = fsi.CreationTime;
 lastwritedatetime = fsi.LastWriteTime;
 lastaccessdatetime = fsi.LastAccessTime;
 attrib = fsi.Attributes.ToString();
 }
 }

Since directories can have files and sub-directories, two logic paths are used to consume the collection created by the collector method. One is for files which have a size property, and one for directories, which don't have a size property. The output columns are set to the object properties in the appropriate code block.

With the function coded, it can be built into a DLL that can be manually loaded into SQL Server and maintained in version control, or it can be auto-deployed if you have Visual Studio Professional. Since this Assembly requires EXTERNAL_ACCESS to run, and we have signed it with a key, it is often easiest to create the DLL, and then load it manually into SQL Server after creating the ASSYMETTRIC KEY in SQL, a LOGIN from the key, and a database user from the login with the EXTERNAL ACCESS ASSEMBLY right granted to it.

For simplicity, attached to this article is a single install script that creates the ASSEMBLY from its binary representation, loads the key from the ASSEMBLY, creates a login from the key, grants the EXTERNAL ACCESS ASSEMBLY right to the login, creates a database user, alters the ASSEMBLY to use EXTERNAL_ACCESS rights, and then creates the TSQL function wrapper around the ASSEMBLY method. I chose to create the function in master so that it can be used universally. The original source solution and C# files are also attached in a separate zip file.

To use the function once installed you call it like any other table valued function:

SELECT *
 FROM master.dbo.os_directory_info('c:\', default)

to perform filtering, you simply pass a string just as you would use as a part of the dir command at the command prompt:

SELECT *
 FROM master.dbo.os_directory_info('c:\', '*.txt')

Hopefully this article gets you thinking about SQLCLR can be used to improve security and stability rather than reduce it as commonly perceived. In future editions I'll build upon this to demonstrate how additional items that SQLCLR can be used for to further eliminate the need for xp_cmdshell.