Hola, chicos,
¡Buenas tardes!

En esta publicación, le demostraré algunas características interesantes de los activadores de inicio de sesión, como crear un registro de auditoría para cada usuario que se conecta a su base de datos, bloquear conexiones provenientes de un usuario/IP/nombre de host y bloquear conexiones en un momento determinado.

AVISO

En primer lugar, me gustaría advertirle sobre el PELIGRO al utilizar un activador de inicio de sesión. Tenga en cuenta que este disparador se ejecutará cada vez que se abra una nueva conexión en la base de datos. Además de la posibilidad de ralentizar el proceso de inicio de sesión, según su código de activación, si el usuario no tiene permiso para realizar ninguna operación de activación o incluso si el activador tiene un error, puede evitar que TODOS los usuarios de la instancia se conecten a SQL Server.

En otras palabras, TEN MUCHO CUIDADO, ya que puedes detener la instancia si activas un disparador sin antes probarlo muy bien. Antes de habilitar un activador de inicio de sesión, siempre recomiendo activar la conexión DAC para garantizar que pueda conectarse a la instancia si el activador tiene problemas. Otro consejo es dejar siempre un usuario en la lista de excepciones (como “sa” en el ejemplo) en caso de que el disparador tenga un error, será más fácil conectarse a la instancia para eliminar el disparador.

Para saber cómo hacer esto, visita mi artículo. Habilitación y uso de una conexión de administrador remoto (DAC) dedicada en SQL Server.

Si ya creaste el disparador, está impidiendo que los usuarios se conecten, no has activado la conexión DAC y ya estás teniendo problemas para iniciar sesión en la instancia, una alternativa es agregar el parámetro -f al iniciar el servicio SQL Server para Inicie SQL Server con una configuración mínima., suelte el disparador y reinicie el servicio sin -f.

Auditoría y registro de inicios de sesión realizados

En este fragmento de código, demostraré cómo crear un registro de conexión para auditar a los usuarios que se conectan a su base de datos.

Agregué algunos filtros para evitar el inicio de sesión de usuarios del sistema (Ej: SA), conexiones provenientes de software que se conectan constantemente a la base de datos (Ej: RedGate SQL Prompt e Intellisense de Managment Studio).

También agregué una función para intentar identificar el nombre del usuario de AD que inicia sesión en la base de datos utilizando un usuario de SQL. No conozco una manera de hacer esto al 100%, así que recupero el último usuario de AD que se conectó a ese nombre de host donde está el usuario de SQL.

Finalmente, agregué otro filtro para evitar registrar múltiples líneas repetidas, verificando si ya se ha registrado una conexión con el mismo usuario, nombre de host y SPID en un intervalo de 1h.

Implementación:
Ver código fuente

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgAudit_Login') > 0) DROP TRIGGER [trgAudit_Login] ON ALL SERVER
GO

CREATE TRIGGER [trgAudit_Login] ON ALL SERVER 
FOR LOGON 
AS
BEGIN


    SET NOCOUNT ON
    
    
    -- Não loga conexões de usuários de sistema
    IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM') OR ORIGINAL_LOGIN() LIKE '%SQLServerAgent')
        RETURN
        
        
    -- Não loga conexões de softwares que ficam se conectando constantemente
    IF (PROGRAM_NAME() LIKE 'Red Gate%' OR PROGRAM_NAME() LIKE '%IntelliSense%' OR PROGRAM_NAME() = 'Microsoft SQL Server')
        RETURN


    IF (OBJECT_ID('Auditoria.dbo.Logins') IS NULL)
    BEGIN
    
        -- DROP TABLE Auditoria.dbo.Logins
        CREATE TABLE Auditoria.dbo.Logins (
            Id_Auditoria INT IDENTITY(1,1),
            Dt_Evento DATETIME,
            SPID SMALLINT,
            Ds_Usuario VARCHAR(100) NULL,
            Ds_Usuario_Original VARCHAR(100) NULL,
            Ds_Tipo_Usuario VARCHAR(30) NULL,
            Ds_Ip VARCHAR(30) NULL,
            Ds_Hostname VARCHAR(100) NULL,
            Ds_Software VARCHAR(500) NULL
        )

        CREATE CLUSTERED INDEX SK01 ON Auditoria.dbo.Logins(Id_Auditoria)

    END


    
    DECLARE 
        @Evento XML, 
        @Dt_Evento DATETIME,
        @Ds_Usuario VARCHAR(100),
        @Ds_Usuario_Original VARCHAR(100),
        @Ds_Tipo_Usuario VARCHAR(30),
        @Ds_Ip VARCHAR(30),
        @SPID SMALLINT,
        @Ds_Hostname VARCHAR(100),
        @Ds_Software VARCHAR(100)
        


    SET @Evento = EVENTDATA()

    
    SELECT 
        @Dt_Evento = @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime'),
        @Ds_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(100)'),
        @Ds_Tipo_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginType/text())[1]','varchar(30)'),
        @Ds_Hostname = HOST_NAME(),
        @Ds_Ip = @Evento.value('(/EVENT_INSTANCE/ClientHost/text())[1]','varchar(100)'),
        @SPID = @Evento.value('(/EVENT_INSTANCE/SPID/text())[1]','smallint'),
        @Ds_Software = PROGRAM_NAME()
         

    -- Identifica o usuário original caso seja um usuário SQL
    IF (LEFT(@Ds_Tipo_Usuario, 7) != 'Windows')
    BEGIN
            
        SELECT @Ds_Usuario_Original = (
            SELECT 
                A.Ds_Usuario
            FROM 
                Auditoria.dbo.Logins A
                JOIN (
                    SELECT Ds_Hostname, MAX(Id_Auditoria) AS Id_MAX
                    FROM Auditoria.dbo.Logins	WITH(NOLOCK)
                    WHERE Ds_Tipo_Usuario LIKE 'Windows%'
                    GROUP BY Ds_Hostname
                ) B ON A.Ds_Hostname = B.Ds_Hostname AND A.Id_Auditoria = B.Id_MAX
        )

    END	


    -- Evita gravar várias vezes um mesmo login
    DECLARE @Dt_Ultima_Data DATETIME
    SELECT @Dt_Ultima_Data = MAX(Dt_Evento)
    FROM Auditoria.dbo.Logins
    WHERE Ds_Usuario = @Ds_Usuario
    AND SPID = @SPID
    

    
    IF (DATEDIFF(SECOND, ISNULL(@Dt_Ultima_Data, '1990-01-01'), @Dt_Evento) > 1)
    BEGIN
    
        INSERT INTO Auditoria.dbo.Logins
        SELECT 
            GETDATE(),
            @SPID,
            @Ds_Usuario,
            @Ds_Usuario_Original,
            @Ds_Tipo_Usuario,
            @Ds_Ip,
            @Ds_Hostname,
            @Ds_Software

    END
            

END
GO

ENABLE TRIGGER [trgAudit_Login] ON ALL SERVER  
GO

USE [Auditoria]
GO

GRANT SELECT, INSERT ON dbo.Logins TO [public]
GO

Resultados:

trgAudit_Login
trgAudit_Login

Recuerde revisar cuidadosamente el nombre de las tablas en este activador cuando lo implemente en su entorno. De lo contrario, probablemente creará un activador "con errores" e impedirá que los usuarios inicien sesión en su instancia, como se ve en el siguiente mensaje de error:

Otra observación en este código es que este disparador escribe datos en algunas tablas de la base de datos, es decir, el usuario que se conecta a la base de datos necesitará permisos para escribir datos en esta tabla, además de tener el usuario creado en la base de datos de esa tabla. Por este motivo, agregué el comando de concesión a la tabla para el rol público.

Otra forma de evitar esto es usar la cláusula EXECUTE AS ‘login_com_permissao’, de modo que el disparador se ejecutará con el permiso de ese usuario EXECUTE AS, pero registrará los datos del usuario real que está contando, evitando la necesidad de crear todos los usuarios en la base de datos y liberar los permisos, quedando así:

CREATE TRIGGER [trgAudit_Login] ON ALL SERVER 
WITH EXECUTE AS 'dirceu.resende'
FOR LOGON
[...]

PD: si va a seguir este enfoque, recuerde elegir un usuario con todos los permisos necesarios para las operaciones de este activador (por ejemplo, usuario miembro del rol de administrador de sistemas). De lo contrario, su disparador presentará errores e impedirá nuevas conexiones a la base de datos (lea CAOS)

Evitar que ciertos usuarios inicien sesión

Ahora les demostraré cómo limitar el acceso de algunos usuarios específicos a la base de datos. Esto también se puede aplicar a IP o nombres de host específicos, creando una lista de permitidos o denegados.

Implementación:
Ver código fuente

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgBloquear_Login') > 0) DROP TRIGGER [trgBloquear_Login] ON ALL SERVER
GO

CREATE TRIGGER [trgBloquear_Login] ON ALL SERVER
FOR LOGON 
AS
BEGIN


    -- Não elimina conexões de usuários de sistema
    IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM'))
        RETURN
    
    
    DECLARE 
        @Evento XML, 
        @Dt_Evento DATETIME,
        @Ds_Usuario VARCHAR(100),
        @Ds_Usuario_Original VARCHAR(100),
        @Ds_Tipo_Usuario VARCHAR(30),
        @Ds_Ip VARCHAR(30),
        @SPID SMALLINT,
        @Ds_Hostname VARCHAR(100),
        @Ds_Software VARCHAR(100)
        


    SET @Evento = EVENTDATA()

    
    SELECT 
        @Dt_Evento = @Evento.value('(/EVENT_INSTANCE/PostTime/text())[1]','datetime'),
        @Ds_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginName/text())[1]','varchar(100)'),
        @Ds_Tipo_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginType/text())[1]','varchar(30)'),
        @Ds_Hostname = HOST_NAME(),
        @Ds_Ip = @Evento.value('(/EVENT_INSTANCE/ClientHost/text())[1]','varchar(100)'),
        @SPID = @Evento.value('(/EVENT_INSTANCE/SPID/text())[1]','smallint'),
        @Ds_Software = PROGRAM_NAME()
         
         

    IF (@Ds_Usuario IN ('Usuario_Teste'))
    BEGIN
        PRINT 'Usuário não permitido para logar neste servidor. Favor entrar em contato com a equipe de Banco de Dados'
        ROLLBACK
    END
    
    
    IF (@Ds_Tipo_Usuario = 'SQL Login')
    BEGIN
        PRINT 'Usuários SQL não são permitidos nesse servidor. Favor entrar em contato com a equipe de Banco de Dados'
        ROLLBACK
    END
    
            

END
GO

ENABLE TRIGGER [trgBloquear_Login] ON ALL SERVER  
GO

Resultados:

trgAudit_Login5
trgAudit_Login5

trgAudit_Login2
trgAudit_Login2

Impedir el inicio de sesión en un momento determinado

En este extracto a continuación, demostraré cómo bloquear conexiones fuera del horario comercial.

Implementación:
Ver código fuente

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgBloquear_Login_Horario') > 0) DROP TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER
GO

CREATE TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER
FOR LOGON 
AS
BEGIN


    -- Não elimina conexões de usuários de sistema
    IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM'))
        RETURN
    
    
    IF (DATEPART(WEEKDAY, GETDATE()) IN (0, 7))
    BEGIN
        PRINT 'Conexões aos fins de semana não são permitidas neste servidor'
        ROLLBACK
        RETURN
    END
    
    
    IF (DATEPART(HOUR, GETDATE()) >= 18 OR DATEPART(HOUR, GETDATE()) < 8)
    BEGIN
        PRINT 'Conexões antes das 8h e depois das 18h não são permitidas neste servidor'
        ROLLBACK
        RETURN
    END
       

END
GO

ENABLE TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER  
GO

Resultados:

trgAudit_Login5
trgAudit_Login5

trgAudit_Login3
trgAudit_Login3

Limitar el número máximo de conexiones de usuario

En el fragmento de código a continuación, demostraré cómo limitar la cantidad de conexiones simultáneas de los usuarios. Puede cambiar el código para limitarlo solo a ciertos usuarios según sus necesidades.

Tenga en cuenta que en este activador, agregué una excepción para no limitar las conexiones de los usuarios que son administradores de sistemas, como es el caso de los administradores de bases de datos. Esto también se puede utilizar en otros desencadenantes, si lo encuentra interesante.

Implementación:
Ver código fuente

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgBloquear_Login_Sessoes') > 0) DROP TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER
GO

CREATE TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER
WITH EXECUTE AS SELF
FOR LOGON
AS
BEGIN


    -- Não elimina conexões de usuários de sistema
    IF (ORIGINAL_LOGIN() IN ('sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM'))
        RETURN
        
        
    -- Verifica se o usuário é sysadmin
    DECLARE @IsSysAdmin int
    EXECUTE AS CALLER
    SET @IsSysAdmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)
    REVERT
        
        
    IF (@IsSysAdmin = 0)
    BEGIN
        
        IF ((
            SELECT COUNT(*) 
            FROM sys.dm_exec_sessions 
            WHERE is_user_process = 1 
            AND login_name = ORIGINAL_LOGIN()
            AND [program_name] NOT LIKE 'Red Gate%'
            AND [program_name] NOT LIKE '%IntelliSense%'
        ) > 2)
        BEGIN
            PRINT 'Número máximo de conexões atingidas para este usuário neste servidor'
            ROLLBACK
            RETURN
        END
        
    END
       

END
GO

ENABLE TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER  
GO

Resultados:

trgAudit_Login5
trgAudit_Login5

trgAudit_Login6
trgAudit_Login6

Evitar que los usuarios de SQL inicien sesión con SSMS

Ahora le demostraré cómo evitar que los usuarios con autenticación de SQL Server se conecten al entorno mediante SQL Server Management Studio o SQLCMD. Esto es muy útil en entornos donde los desarrolladores y DBA acceden a la base de datos utilizando usuarios con autenticación de Windows y las aplicaciones usan usuarios con autenticación de SQL Server.

En este escenario, es muy común que los desarrolladores utilicen al usuario de la aplicación para aplicar comandos en entornos de producción, donde su usuario no tiene permisos de escritura. Este activador podría ser una buena opción para evitar que intenten utilizar estos cambios utilizando el usuario de la aplicación en lugar de preguntarle al DBA.

Implementación:
Ver código fuente

USE [master]
GO

IF ((SELECT COUNT(*) FROM sys.server_triggers WHERE name = 'trgEvita_Conexao_SQL') > 0) DROP TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER
GO

CREATE TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER 
WITH EXECUTE AS 'dirceu.resende'
FOR LOGON 
AS
BEGIN


    SET NOCOUNT ON
    
    
    -- Não loga conexões de usuários de sistema
    IF (ORIGINAL_LOGIN() IN ('dirceu.resende', 'sa', 'AUTORIDADE NT\SISTEMA', 'NT AUTHORITY\SYSTEM') OR ORIGINAL_LOGIN() LIKE '%SQLServerAgent')
        RETURN
        
        
    -- Não loga conexões de softwares que ficam se conectando constantemente
    IF (PROGRAM_NAME() LIKE 'Red Gate%' OR PROGRAM_NAME() LIKE '%IntelliSense%' OR PROGRAM_NAME() = 'Microsoft SQL Server')
        RETURN

    
    DECLARE 
        @Evento XML, 
        @Ds_Tipo_Usuario VARCHAR(30)
        
    SET @Evento = EVENTDATA()
    SET @Ds_Tipo_Usuario = @Evento.value('(/EVENT_INSTANCE/LoginType/text())[1]','varchar(30)')


    -- Identifica o usuário original caso seja um usuário SQL
    IF (LEFT(@Ds_Tipo_Usuario, 7) != 'Windows' AND (PROGRAM_NAME() LIKE 'Microsoft SQL Server Management Studio%' OR program_name() LIKE 'sqlcmd%'))
    BEGIN
            
        SELECT 1/0

    END	

    
END
GO

ENABLE TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER  
GO

¡Eso es todo, amigos!
¡Hasta el próximo post!