O Common Language Runtime é o coração do .NET Framework. O SQL Server fornece recursos que permitem a incorporação de componentes CLR ao seu ambiente de execução, desta forma podemos construir stored procedures, triggers, user-defined functions, user-defined types e user-defined aggregates utilizando código gerenciado.

Este post descreve os passos necessários para criação de um componente SQL CLR para o registro de mensagens no event log do Windows a partir da execução de stored procedures.

Os passos para criação deste componente SQL CLR são:

1 – Crie um projeto Class Library.

2 – Crie uma classe chamada UserDefinedFunctions e adicione o código apresentado abaixo. Lendo este código podemos notar um atributo SqlProcedure decorando o método WriteLogMessage, isso indica ao SQL que este é um método compatível com a execução de stored procedures. E, no escopo do método, podemos notar que existe a verificação da existência do Source do event log, além da execução efetiva do registro da mensagem no log do Windows.

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

public partial class UserDefinedFunctions {

    [SqlProcedure()]
    public static void WriteLogMessage(SqlString message) {

        const string sourceName = "SQL Custom Event Log";

        if (!EventLog.SourceExists(sourceName))
            EventLog.CreateEventSource(sourceName, "Application");

        EventLog.WriteEntry(sourceName, message.ToString(), EventLogEntryType.Warning);
    }
}

3 – Assine o assembly construído. Acesse as propriedades do projeto, acesse a guia Signing, marque a opção “Sign the assembly” e defina um Strong name key. A imagem abaixo demonstra esse passo.

4 – Compile o projeto, copie sua DLL para alguma pasta e abra o SQL Management Studio.

5 – Rode o script abaixo para registrar o assembly no SQL, criar uma procedure que referencie o assembly e o método CLR criado para registro das mensagens no log.

Detalhes importantes do script:

SP_CONFIGURE ‘clr enabled’, 1; – Habilita a execução de código gerenciado pelo SQL.

ALTER DATABASE LoadTest SET TRUSTWORTHY ON – Chave de configuração utilizada para que o SQL confie em seu contéudo.

CREATE ASSEMBLY CustomEventLogger FROM ‘D:\Shared\CustomEventLogger.dll’ WITH PERMISSION_SET = EXTERNAL_ACCESS; – Registra o assembly localizado no caminho ‘D:\Shared\CustomEventLogger.dll’ com o nome CustomEventLogger e permissão EXTERNAL_ACCESS.

CREATE PROCEDURE WriteLogMessage(@message nvarchar(max)) AS EXTERNAL NAME CustomEventLogger.UserDefinedFunctions.WriteLogMessage; – Cria uma procedure chamada WriteLogMessage relacionada ao método WriteLogMessage da classe UserDefinedFunctions, contida no assembly CustomEventLogger.

Observação: minha DLL está localizada na pasta ‘’D:\Shared\’, seu nome é ‘CustomEventLogger.dll’ e o nome do meu banco de dados é LoadTest. Todos estes são aspectos que precisam ser adequados ao seu cenário e banco de dados.

USE LoadTest
GO

SP_CONFIGURE 'clr enabled', 1;
GO

RECONFIGURE;
GO

ALTER DATABASE LoadTest SET TRUSTWORTHY ON
GO

IF EXISTS(SELECT TOP 1 1 FROM sys.objects WHERE NAME = 'WriteLogMessage')
	DROP PROCEDURE WriteLogMessage 
GO

IF EXISTS(SELECT TOP 1 1 FROM sys.assemblies WHERE NAME = 'CustomEventLogger')
	DROP ASSEMBLY CustomEventLogger 
GO

CREATE ASSEMBLY CustomEventLogger 
FROM 'D:\Shared\CustomEventLogger.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS ;
GO

CREATE PROCEDURE WriteLogMessage(@message nvarchar(max))
AS EXTERNAL NAME CustomEventLogger.UserDefinedFunctions.WriteLogMessage;  
GO

Para testar a procedure criada basta executar o comando abaixo:

EXEC WriteLogMessage 'Message'

Provavelmente essa execução resultará na exception: System.Security.SecurityException: The source was not found, but some or all event logs could not be searched.  Inaccessible logs: Security.

Para resolver isso, identifique o usuário que está executando o serviço do SQL e adicione-o ao grupo de usuário do Windows chamado “Event Log Readers”.

Depois de adicionado o usuário ao grupo do Windows, reinicie o SQL pelo SQL Management Studio, como demonstrado a seguir e reexecute a stored procedure de registro de logs.

 

 

FH

Referências:

http://technet.microsoft.com/en-us/library/ms345101.aspx

http://msdn.microsoft.com/en-us/library/w2kae45k(v=vs.90).aspx

http://msdn.microsoft.com/en-us/library/ms254498(v=vs.90).aspx

http://technet.microsoft.com/en-us/library/ms345101.aspx

http://msdn.microsoft.com/en-us/library/ms254498(v=vs.110).aspx