SQL Server Logon Trigger Implications - are there any side effects possible in my scenario?

Question

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

Answer 1

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

Answer 2

One aspect that might not be the type of implication you are asking for, but IMO equally important is:

Application Name and HostName are set by the client app. So your developers can easily connect to your SQL Server using SSMS and in the connection string specify something like below:

Application Name=MyAppName;Workstation ID=MickeyMouse

(You can specify connection string attributes in the SSMS "Connect" dialog.)

I can't say for sure about the IP adress, whether it is "safe" to use.

Answer 3

You can try this trigger:

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