SQL Server 2017 – Logon Trigger Implications and Side Effects

database-mailloginslogon-triggersql serversql-server-2017

Some of developers on my team know passwords from SQL accounts that have extended permissions
We would like to track and be alerted whenever any of developers are using any of those SQL accounts to connect

We are about to implement logon trigger that would for each log in attempt, evaluate login's properties, and send email report if they match certain criteria. Logic is following:

if original_login() in (…SQL accounts list here…) and:
a) client IP address is 192.168.x.x VPN subnet (no Production apps connecting from this subnet, only developers can) or
b) client host name in (… list of Dev's host machine names…) or
c) client application name in (SSMS,az-Data etc.)
exec sp_send_dbmail (send report over email to DBA)

Trigger would have "execute as" clause and run on behalf of a SQL login whose only permission is to send db mail emails

What can be unwanted side effects of enabling this kind of logon trigger on Production ?
Can it slow login process or cause any other issues ?

p.s. I am aware about DAC and how to use it.
Tested connecting using DAC and counting on it to help me disable the trigger if any trouble begins

Best Answer

Database mail uses a Service Broker queue and an asynchronous background process to actually send emails, so performance shouldn't be a big deal. But logon triggers can easily cause downtime, so they require a great deal of care in writing and testing. Also you could end up with thousands of emails if a developer runs a load test or somesuch.

So it's probably overkill to use a logon trigger for this. Instead use an Audit or even just an XEvent session. Write the data to an event file and processes it with a scheduled job.

Here's how to create and query an Audit:

--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);
Related Question