All we need is an easy explanation of the problem, so here it is.
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
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
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 = '''[email protected]+''' ) 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);
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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂