Hey guys!
How are you?

In this post I would like to demonstrate how to implement access control and audit logs using the CLR (C#). This proved to be very useful during audit processes or when a problem occurred due to misuse of the CLR, especially where the user of the SQL Server service is a domain admin or has a high privilege level, being able to perform a series of actions and you end up not having control over who uses it and what each person is doing with the CLR, especially when you have routines for manipulating records, files, etc.

In order to maintain control over the use of this tool, I will demonstrate in this post how you can implement access control for all use of the CLR, so that you can identify who executed each routine and even the parameters used in each call.

Creating the CLR audit logs

To create this control, first of all we need to create the table in the database that will store the logs for auditing the use of SP's and CLR functions.

The structure I will use in this post is this:

CREATE TABLE dbo.Log_Execucao (
    Id_Log BIGINT IDENTITY(1, 1) NOT NULL,
    Dt_Log DATETIME DEFAULT GETDATE() NOT NULL,
    Ds_Procedure VARCHAR(200) NOT NULL,
    Ds_Parametros VARCHAR(MAX) NULL,
    Ds_Usuario VARCHAR(100) NULL,
    Ds_Hostname VARCHAR(100) NULL,
    Ds_Programa VARCHAR(500) NULL
)

CREATE CLUSTERED INDEX CI_Log_Execucao ON dbo.Log_Execucao(Dt_Log)
ALTER TABLE dbo.Log_Execucao ADD CONSTRAINT [PK_Log_Execucao] PRIMARY KEY NONCLUSTERED(Id_Log)

Source code for the recordingLogExecucao method

Using the code below, you can generate log information and save it to the database.

public static bool gravaLogExecucao(string parametros = "")
{

    var stackTrace = new StackTrace();

    // Evita gravar chamadas objetos dependentes (Ex: Evita gravar a chamada de uma função que é chamada por uma SP)
    if (stackTrace.FrameCount >= 4)
        return false;


    try
    {

        string nomeServidor;
        var dadosUsuarios = new string[4];

        // Utiliza a conexão de contexto para recuperar os dados do usuário que está chamando as rotinas do CLR
        using (var conn = new SqlConnection("context connection=true"))
        {

            conn.Open();

            using (var cmd = conn.CreateCommand())
            {

                cmd.CommandText = "SELECT @@SERVERNAME AS instancia, suser_name() AS usuario, host_name() AS maquina, APP_NAME() AS programa";

                using (var dr = cmd.ExecuteReader())
                {

                    dr.Read();

                    nomeServidor = dr["instancia"].ToString();

                    dadosUsuarios.SetValue(dr["instancia"].ToString(), 0);
                    dadosUsuarios.SetValue(dr["usuario"].ToString(), 1);
                    dadosUsuarios.SetValue(dr["maquina"].ToString(), 2);
                    dadosUsuarios.SetValue(dr["programa"].ToString(), 3);
                    
                }

            }


            var partes = nomeServidor.Split('\\');

            if (partes.Length > 1)
            {
                if (string.Equals(partes[0], partes[1], StringComparison.CurrentCultureIgnoreCase))
                    nomeServidor = partes[0];
            }
        }

 
        // Recupera o objeto que foi utilizado utilizando técnicas de Reflection (stackTrace)
        var objeto = stackTrace.GetFrame(1).GetMethod().Name;
        var servidorAtual = nomeServidor;
        var stringConexao = "data source=" + servidorAtual + ";initial catalog=CLR;Application Name=SQLCLR;persist security info=False;Enlist=False;packet size=4096;user id='seu_usuario';password='sua_senha'";

        
        // Agora utiliza o usuário do CLR, que vai ter permissões de escrita na tabela de log
        using (var conexao = new SqlConnection(stringConexao))
        {

            conexao.Open();

            using (var comando = new SqlCommand("INSERT INTO dbo.Log_Execucao (Ds_Procedure, Ds_Parametros, Ds_Usuario, Ds_Hostname, Ds_Programa) VALUES (@Ds_Procedure, @Ds_Parametros, @Ds_Usuario, @Ds_Hostname, @Ds_Programa)", conexao))
            {

                comando.Parameters.Add(new SqlParameter("@Ds_Procedure", SqlDbType.VarChar, 200)).Value = objeto;
                comando.Parameters.Add(new SqlParameter("@Ds_Parametros", SqlDbType.VarChar, -1)).Value = parametros;
                comando.Parameters.Add(new SqlParameter("@Ds_Usuario", SqlDbType.VarChar, -1)).Value = dadosUsuarios[1];
                comando.Parameters.Add(new SqlParameter("@Ds_Hostname", SqlDbType.VarChar, -1)).Value = dadosUsuarios[2];
                comando.Parameters.Add(new SqlParameter("@Ds_Programa", SqlDbType.VarChar, -1)).Value = dadosUsuarios[3];

                comando.ExecuteNonQuery();

                return true;
            }
        }

    }
    catch (Exception e)
    {
        return false;
    }

}

Using the audit method in your CLR

Once you have created the static method recordLogExecution in a class in your SQL CLR project, you must use this method in all your Stored Procedures and functions that you want to audit.

I will demonstrate some examples.

Implementing audit logging in Stored Procedures:

using System;
using System.Data.SqlTypes;
using System.IO;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void stpApaga_Arquivo(SqlString caminho)
    {

        // Grava o log de auditoria
        Utils.gravaLogExecucao($"caminho: {caminho}");

        
        var arquivo = new FileInfo(caminho.Value);

        if (arquivo.Exists)
            arquivo.Delete();
        
    }

};

Implementing audit logging in functions:
To implement the log recording feature in functions, we will need to include 2 special parameters in the SqlFunction object declaration, which are DataAccess and SystemDataAccess.

By default, functions in the CLR do not have access to perform database queries. For this to be possible, we will need to define the reading mode for these functions, so that they can perform queries and execute commands on the database, as I did in the example below.

using System.Data.SqlTypes;
using System.IO;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        DataAccess = DataAccessKind.Read,
        SystemDataAccess = SystemDataAccessKind.Read
    )]
    public static SqlBoolean fncArquivo_Existe(SqlString Ds_Arquivo)
    {

        // Grava o log de auditoria
        Utils.gravaLogExecucao($"Ds_Arquivo: {Ds_Arquivo}");

        return (File.Exists(Ds_Arquivo.ToString()));
    }
};

As you may have noticed, when calling the method recordLogExecution I need to inform the parameter name and its values ​​in each call. This is especially laborious, especially if you have a lot of SP's and functions, but unfortunately, I didn't find another alternative for this, not even using Reflection. Therefore, if your Stored Procedure has 10 parameters, you will need to enter them one by one, and their respective values ​​when calling the method. recordLogExecution in C#.

It is worth remembering that, unlike Transact-SQL functions, where it is not possible to execute commands and make any changes external to the function, such as executing a Procedure, changing table data, etc., in CLR functions it is possible to perform any type of action, including file operations, data manipulation, etc., which makes them especially powerful and dangerous, if used incorrectly.

Viewing the audit logs

Now that I've implemented audit logging in all my CLR routines, let's execute some objects and observe the generated execution log.

Examples of CLR commands

EXEC CLR.dbo.stpCria_Diretorio
    @Ds_Diretorio = N'C:\Teste' -- nvarchar(max)

EXEC CLR.dbo.stpCopia_Arquivo
    @origem = N'C:\Windows\System32\drivers\etc\hosts' , -- nvarchar(max)
    @destino = N'C:\Teste\hosts' , -- nvarchar(max)
    @sobrescrever = 1 -- bit

EXEC CLR.dbo.stpRenomeia_Arquivo
    @Caminho_Origem = N'C:\Teste\hosts' , -- nvarchar(max)
    @Caminho_Destino = N'C:\Teste\hosts.txt' , -- nvarchar(max)
    @Fl_Sobrescrever = 1 -- bit

EXEC CLR.dbo.stpApaga_Arquivo
    @caminho = N'C:\Teste\hosts.txt' -- nvarchar(max)

Log view

SELECT * FROM dbo.Log_Execucao

That's it, folks!
I hope you liked this tip.

I know you thought it was really cool the way I manipulated the files in a very easy, intuitive and quick way, right? If you liked it and want to know a little more about how to perform operations with files using the CLR, see more in the post SQL Server – How to list, read, write, copy, delete and move files with the CLR (C#).

Hugs!