Using CLR to replace xp_cmdshell for specific tasks

Article
05/20/2015

As we have discussed before, xp_cmdshell is a mechanism to execute arbitrary calls into the system and because of the flexibility of its nature, it is typically abused and leads to serious security problems in the system.

In most cases, what the sysadmin really wants to do is to enable only a handful of specific tasks on the system, without the whole flexibility that comes from running xp_cmdshell directly.

One approach to achieve this constraint access to specific tasks on the system is to enable xp_cmdshell through a signed module. For detailed information on this approach, we have some articles in the SQL Server Security blog, but Erland Sommarskog also has a very nice article on the subject that I would recommend: https://www.sommarskog.se/grantperm.html.

An alternative that I personally prefer is to create SQL CLR modules that help to accomplish the specific tasks. Using SQL CLR modules it is possible to create a finely targeted escalation path that enables users to do exactly what they need, enabling at the same time ease to write parameter check verification and a clear parameterization that would help you avoid command injections.

For example, let’s create a library that help to copy & delete files in the OS, you could easily add checks for specific paths and make sure that the behavior of the module is exactly what you would expect:

  using System;
 using System.Collections.Generic;
 using System.Linq;
 using System.Text;
 using System.Threading.Tasks;
 using System.Data.SqlTypes;
 using Microsoft.SqlServer.Server;
 using System.IO;
 
 public class SqlClrUserDefinedModules
 {
 readonly private static string _approvedDirectory = @"C:\temp";
 
 [SqlProcedure()]
 public static void DeleteFile(SqlString filename)
 {
 FileInfo fi = new FileInfo(filename.Value);
 
 if(!fi.DirectoryName.Equals(_approvedDirectory, StringComparison.InvariantCultureIgnoreCase ))
 {
 throw new Exception(@"File is not located in an approved directory");
 }
 
 File.Delete(filename.Value);
 }
 
 public static void CopyFile( SqlString filename, SqlString destinationFilename)
 {
 FileInfo fi = new FileInfo(filename.Value);
 if(!fi.DirectoryName.Equals(_approvedDirectory, StringComparison.InvariantCultureIgnoreCase))
 {
 throw new Exception(@"Source file is not located in an approved directory");
 }
 
 fi = new FileInfo(destinationFilename.Value);
 if (!fi.DirectoryName.Equals(_approvedDirectory, StringComparison.InvariantCultureIgnoreCase))
 {
 throw new Exception(@"Destination file is not located in an approved directory");
 }
 
 File.Copy(filename.Value, destinationFilename.Value);
 }
 }

Because it is accessing the OS file system, in order to use it on your database, you will need to use EXTERNAL ACCESS permission. This gives you two choices:

1) Trust the DB where you are storing it

2) Trust the module via a strong name or Authenticode

In our example, I decided to use a strong name, so I will create an ASYMMETRIC KEY in master DB by extracting it from the DLL file itself, grant the right permission (EXTERNAL ACCESS ASSEMBLY) and enable CLR in case it was not enabled:

 USE [master]
 GO
 
 CREATE ASYMMETRIC KEY [snk_external_access_clr] FROM EXECUTABLE FILE = 'E:\temp\SqlUserDefinedModules.dll'
 go
 
 CREATE LOGIN [snk_external_access_clr] FROM ASYMMETRIC KEY [snk_external_access_clr]
 GO
 
 GRANT EXTERNAL ACCESS ASSEMBLY TO [snk_external_access_clr] 
 go
 
 EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
 go

The next step would be to access the DB where we are going to host the assembly and create it.

NOTE: because we are trusting the module via a digital signature (Strong Name), we are trusting the module as a whole in the system, regardless of the database where it is hosted. Make sure to take this into account when using EXTERNAL ACCESS or UNSAFE assemblies.

 CREATE ASSEMBLY [SqlUserDefinedModules] FROM 'E:\temp\SqlUserDefinedModules.dll'
 WITH PERMISSION_SET = EXTERNAL_ACCESS
 go
 
 CREATE SCHEMA [SqlClrUserDefinedModules]
 go
 
 CREATE PROCEDURE [SqlClrUserDefinedModules].[DeleteFile]
 (@filename nvarchar(2048))
 AS EXTERNAL NAME [SqlUserDefinedModules].[SqlClrUserDefinedModules].[DeleteFile];
 go
 
 CREATE PROCEDURE [SqlClrUserDefinedModules].[CopyFile]
 (@filename nvarchar(2048), @destinationFilename nvarchar(2048))
 AS EXTERNAL NAME [SqlUserDefinedModules].[SqlClrUserDefinedModules].[CopyFile];
 go

At this point, you could simply grant access to execute the module to normal users in the database. For example:

 CREATE USER [clr_test_user] WITHOUT LOGIN
 go
 
 GRANT EXECUTE ON SCHEMA::[SqlClrUserDefinedModules] TO [clr_test_user]
 go

Hopefully this example will be useful to customize CLR modules that can be used to replace any xp_cmdshell usage you may be using in such a way that the CLR modules are more secure and targeted.

Using CLR to replace xp_cmdshell for specific tasks

Additional resources