Início / dba / Perguntas / 290923
Accepted
Aleksey Vitsko
Asked: 2021-05-04 08:06:22 +0800 CST

Implicações do gatilho de logon do SQL Server - existem efeitos colaterais possíveis no meu cenário?

  • 772

Alguns dos desenvolvedores da minha equipe sabem senhas de contas SQL que têm permissões estendidas
Gostaríamos de rastrear e ser alertados sempre que algum desenvolvedor estiver usando qualquer uma dessas contas SQL para se conectar

Estamos prestes a implementar logon triggerisso para cada tentativa de login, avaliar as propriedades do login e enviar um relatório por e-mail se eles corresponderem a determinados critérios. A lógica é a seguinte:

se original_login() em (...lista de contas SQL aqui...) e:
a) o endereço IP do cliente é 192.168.xx sub-rede VPN (nenhum aplicativo de produção se conectando a partir desta sub-rede, somente desenvolvedores podem) ou
b) nome do host do cliente em (... lista de nomes de máquinas host do Dev...) ou
c) nome do aplicativo cliente em (SSMS, az-Data etc.)
exec sp_send_dbmail (enviar relatório por e-mail para o DBA)

O gatilho teria a cláusula "execute as" e seria executado em nome de um logon SQL cuja única permissão é enviar e-mails de db mail

Quais podem ser os efeitos colaterais indesejados de habilitar esse tipo de acionador de logon na produção?
Pode retardar o processo de login ou causar outros problemas?

ps Estou ciente DACe como usá-lo.
Testado conectando usando DACe contando com ele para me ajudar a desativar o gatilho se algum problema começar

sql-server sql-server-2017

3 respostas

  1. Best Answer

    O correio de banco de dados usa uma fila do Service Broker e um processo em segundo plano assíncrono para realmente enviar emails, portanto, o desempenho não deve ser um grande problema. Mas os acionadores de logon podem facilmente causar tempo de inatividade, por isso exigem muito cuidado ao escrever e testar. Além disso, você pode acabar com milhares de e-mails se um desenvolvedor executar um teste de carga ou algo assim.

    Portanto, provavelmente é um exagero usar um gatilho de logon para isso. Em vez disso, use uma auditoria ou mesmo apenas uma sessão XEvent. Grave os dados em um arquivo de evento e os processe com um trabalho agendado.

    Veja como criar e consultar uma auditoria:

    --ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = OFF);
--DROP SERVER AUDIT [sa_successful_logon]
go

declare @logFolder nvarchar(255) = ( select [path]  from sys.dm_os_server_diagnostics_log_configurations )
exec('
CREATE SERVER AUDIT [sa_successful_logon]
TO  FILE ( FILEPATH = '''+@logFolder+''' )
WHERE ([server_principal_name]=''sa'');
ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = ON);
')
GO

CREATE SERVER AUDIT SPECIFICATION [sa_successful_logon_logon_spec]
FOR SERVER AUDIT [sa_successful_logon]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)

go

declare @log nvarchar(255) = ( select [path] + 'sa_successful_logon*.sqlaudit' from sys.dm_os_server_diagnostics_log_configurations ) 
SELECT event_time, client_ip, application_name
FROM sys.fn_get_audit_file (@log,default,default);
    • 13

  2. Um aspecto que pode não ser o tipo de implicação que você está pedindo, mas a IMO é igualmente importante:

    O nome do aplicativo e o nome do host são definidos pelo aplicativo cliente. Assim, seus desenvolvedores podem se conectar facilmente ao seu SQL Server usando o SSMS e na string de conexão especificar algo como abaixo:

    Nome do aplicativo=MyAppName;ID da estação de trabalho=MickeyMouse

    (Você pode especificar os atributos da cadeia de conexão na caixa de diálogo "Conectar" do SSMS.)

    Não posso dizer com certeza sobre o endereço IP, se é "seguro" de usar.

    • 1

  3. Você pode tentar este gatilho:

    CREATE TRIGGER [logon_trigger_not_from_SSMS] 
ON ALL SERVER FOR LOGON
AS
  BEGIN
    IF APP_NAME() LIKE '%Microsoft SQL Server Management Studio%' --app name check
      BEGIN
      --the only persons allowed to use SSMS
        IF suser_name() not IN ('sa','DBA_LOGINS') 
        and suser_name() not like 'dev[_]%' 
                        
             BEGIN
                ROLLBACK --Logins other than above mention logins are not allowed to connect through SSMS
             END 
      END 
                  
       
  END
    • 0

relate perguntas