SQL Server - Como implementar auditoria e controle de logins (Trigger de Logon)

Visualizações: 8.285 views

Neste artigo

Esse post é a parte 4 de 21 da série Segurança e Auditoria

Auditoria no SQL Server (Server Audit)
Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
SQL Server – Trigger para prevenir e impedir alterações em tabelas
SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
SQL Server – Como evitar ataques de força bruta no seu banco de dados
SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
SQL Server – Como saber a data do último login de um usuário
SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
SQL Server – Como criar uma auditoria de logins utilizando os logs da instância

Tempo de Leitura: 8 minutos

Olá pessoal,
Boa tarde!

Neste post irei demonstrar pra vocês, alguns recursos legais de triggers de logon como criar um log de auditoria para cada usuário que se conecta no seu banco de dados, bloquear conexões vindas de um usuário/IP/Hostname e bloquear conexões em um determinado horário.

AVISO

Antes de mais nada, gostaria de alertá-los sobre o PERIGO ao se utilizar trigger de logon. Tenha em mente, que essa trigger será executada toda vez que uma nova conexão for aberta no banco de dados. Além da possibilidade de deixar o processo de logon mais lento, de acordo com o código da sua trigger, caso o usuário não tenha permissão para executar alguma operação da trigger ou mesmo caso a trigger tenha algum erro, você pode impedir que TODOS os usuários da instância se conectem ao SQL Server.

Ou seja, TOMEM MUITO CUIDADO, pois você pode parar a instância caso ative uma trigger sem antes testá-la muito bem. Antes de habilitar uma trigger de logon, sempre recomendo ativar a conexão DAC, para garantir que você consiga se conectar na instância caso a trigger apresente problemas. Uma outra dica, é sempre deixar um usuário na lista de exceções (como o “sa” do exemplo) para o caso da trigger ter algum erro, ser mais fácil conectar na instância para dropar a trigger.

Para saber fazer isso, visite o meu artigo Habilitando e utilizando a conexão remota dedicada para administrador (DAC) no SQL Server.

Se você já criou a trigger, ela está impedindo a conexão de usuários, você não ativou a conexão DAC e já está com problemas para se logar na instância, uma alternativa é adicionar o parâmetro -f na inicialização do serviço do SQL Server para iniciar o SQL Server com configuração mínima, dropar a trigger e reiniciar o serviço sem o -f.

Auditando e registrando Logins realizados

Neste trecho de código irei demonstrar como criar um log de conexões para auditoria dos usuários que se conectam na sua base de dados.

Adicionei alguns filtros para evitar logar os usuários de sistema (Ex: SA), conexões provenientes de softwares que ficam constantemente conectando no banco (Ex: RedGate SQL Prompt e o Intellisense do Managment Studio).

Adicionei também um recurso para tentar identificar o nome do usuário do AD que está logando no banco de dados usando um usuário SQL. Não conheço uma maneira de fazer isso de forma 100%, então eu recupero o último usuário AD que se conectou nesse hostname em que está o usuário SQL.

Por último, adicionei um outro filtro para evitar gravar várias linhas repetidas, verificando se já foi gravado em um intervalo de 1h, uma conexão com o mesmo usuário, hostname e SPID.

Implementação:
Visualizar código-fonte

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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

USE [master]

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

CREATE TRIGGER [trgAudit_Login] ON ALL SERVER

FOR LOGON

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

ENABLE TRIGGER [trgAudit_Login] ON ALL SERVER

USE [Auditoria]

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

Resultados:

Lembrem-se de revisar muito bem o nome das tabelas dessa trigger quando for implementar no seu ambiente. Caso contrário, você provavelmente vai criar uma trigger “bugada” e vai impedir o logon dos usuários na sua instância, conforme a mensagem de erro abaixo:

Uma outra observação nesse código, é que essa trigger grava dados em algumas tabelas no banco, ou seja, o usuário que for conectar no banco precisará de permissões para gravar os dados nessa tabela, além de ter o usuário criado no database dessa tabela. Por este motivo, adicionei o comando de grant na tabela para a role public.

Uma outra forma de contornar isso, é utilizar a cláusula EXECUTE AS ‘login_com_permissao’, de modo que a trigger será executada com a permissão desse usuário do EXECUTE AS, mas vai gravar os dados do usuário real que está se contando, evitando a necessidade de ter que criar todos os usuários no database e liberar as permissões, ficando desta forma:

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

CREATE TRIGGER [trgAudit_Login] ON ALL SERVER

WITH EXECUTE AS 'dirceu.resende'

FOR LOGON

[...]

PS: Se for seguir essa abordagem, lembre-se de escolher um usuário com todas as permissões necessárias pelas operações dessa trigger (ex: usuário membro da role sysadmin). Caso contrário, sua trigger irá apresentar erros e impedir novas conexões ao banco de dados (leia-se CAOS)

Impedindo o Login de determinados usuários

Agora vou demonstrar a vocês, como limitar o acesso de alguns usuários específicos no banco de dados. Isso pode ser aplicado também, a IP’s ou Hostnames específicos, criando uma lista de permitidos ou de negações.

Implementação:
Visualizar código-fonte

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

USE [master]

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

CREATE TRIGGER [trgBloquear_Login] ON ALL SERVER

FOR LOGON

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

ENABLE TRIGGER [trgBloquear_Login] ON ALL SERVER

Resultados:

Impedindo login em um determinado horário

Neste trecho abaixo, vou demonstrar como bloquear conexões fora de horários comerciais.

Implementação:
Visualizar código-fonte

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

USE [master]

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

CREATE TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER

FOR LOGON

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

ENABLE TRIGGER [trgBloquear_Login_Horario] ON ALL SERVER

Resultados:

Limitando o numero de conexões máximas do usuário

No trecho de código abaixo, irei demonstrar como limitar o número de conexões simultâneas dos usuários. Você pode alterar o código para limitar apenas para determinados usuários, dependendo da sua necessidade.

Reparem que nessa trigger, eu coloquei uma excessão para não limitar as conexões de usuários que sejam sysadmin, que é o caso dos DBA’s. Isso pode ser utilizado nas outras triggers também, se você achar interessante.

Implementação:
Visualizar código-fonte

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

USE [master]

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

CREATE TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER

WITH EXECUTE AS SELF

FOR LOGON

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

ENABLE TRIGGER [trgBloquear_Login_Sessoes] ON ALL SERVER

Resultados:

Impedindo o Login de usuários SQL com SSMS

Agora vou demonstrar a vocês, como impedir que usuários com autenticação SQL Server se conecte no ambiente utilizando o SQL Server Management Studio ou o SQLCMD. Isso é muito útil em ambientes onde os desenvolvedores e DBA’s acessam o banco utilizando usuários com autenticação Windows e as aplicações utilizam usuários com autenticação SQL Server.

Nesse cenário, é muito comum que os desenvolvedores utilizem o usuário da aplicação para aplicar comandos em ambientes de produção, onde o usuário deles não possui permissões de escrita. Essa trigger pode ser uma boa pedida para impedir que eles tentem utilizar essas alterações utilizando o usuário da aplicação ao invés de solicitar ao DBA.

Implementação:
Visualizar código-fonte

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

USE [master]

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

CREATE TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER

WITH EXECUTE AS 'dirceu.resende'

FOR LOGON

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

ENABLE TRIGGER [trgEvita_Conexao_SQL] ON ALL SERVER

É isso aí, pessoal!
Até o próximo post!

Sinval Pereira disse:

25 de fevereiro de 2021 às 11:48

Fala grande Dirceu!
Primeiramente obrigado pelo seu tempo disponibilizado para realizar esse trabalho de compartilhar conhecimento, sensacional!

Antes de implementar a trigger de logon, geralmente eu crio uma trigger que somente monitora, quem e como estão chegando na instância, assim, eu consigo ter uma percepção mais ampla do que ocorre antes que a trigger que irá bloquear os acessos seja de fato implementada!

O que me ocorre, é que, em alguns casos, quando eu implemento a trigger de monitoração ela gera alguns bloqueios em alguns acessos, e essa trigger só monitora e armazena as informações do logon em uma tabela!

Por qual motivo isso ocorre?

Responder
Fernando Akira Tajima disse:

6 de setembro de 2019 às 22:11

Obrigado, pelo post.

Responder
- Dirceu Resende disse:
  
  18 de setembro de 2019 às 22:57
  
  Valeu, Akira!! Obrigado
  
  Responder
Gibson Eric disse:

20 de abril de 2017 às 13:17

Obrigado amigo, encontrei a solução perfeita para o que precisava.

Responder
Alex Jose Silva disse:

15 de abril de 2016 às 12:51

Olá esta aparecendo erros no código SQL

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘ON’.
Server: Msg 156, Level 15, State 1, Procedure trgAudit_Login, Line 2
Incorrect syntax near the keyword ‘ALL’.
Server: Msg 195, Level 15, State 1, Procedure trgAudit_Login, Line 10
‘ORIGINAL_LOGIN’ is not a recognized function name.
Server: Msg 195, Level 15, State 1, Procedure trgAudit_Login, Line 50
‘EVENTDATA’ is not a recognized function name.
Server: Msg 170, Level 15, State 1, Procedure trgAudit_Login, Line 53
Line 53: Incorrect syntax near ‘.’.
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword ‘TRIGGER’.

Qual a correção ?

Responder
- Dirceu Resende disse:
  
  15 de abril de 2016 às 16:25
  
  Alex, obrigado pela visita. O comando CREATE TRIGGER pode ser utilizado a partir do SQL Server 2008. Caso a sua versão seja anterior a esta, não vai funcionar mesmo.. 🙁
  
  Responder

Assinar blog por e-mail

Visualizações do Blog

Microsoft MVP

Arquivo de Posts

Categorias

Posts recentes

SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)

AVISO

Auditando e registrando Logins realizados

Impedindo o Login de determinados usuários

Impedindo login em um determinado horário

Limitando o numero de conexões máximas do usuário

Impedindo o Login de usuários SQL com SSMS

You may also like...

6 Responses

Deixe uma respostaCancelar resposta

AVISO

Auditando e registrando Logins realizados

Impedindo o Login de determinados usuários

Impedindo login em um determinado horário

Limitando o numero de conexões máximas do usuário

Impedindo o Login de usuários SQL com SSMS

You may also like...

Como utilizar JSON no SQL Server 2008, 2012 e 2014 – Lendo strings JSON, importando para o banco e exportando para XML

SQL Server – Como apagar um usuário de uma instância

SQL Server – Quebrando strings em sub-strings utilizando separador (Split string)

6 Responses

Deixe uma respostaCancelar resposta