By: Aaron Bertrand | Updated: 2015-04-03 | Comments (3) | Related: > Security
Problem
Recently at SQL Bits a colleague, Chirag Roy, wanted to prevent developers from attempting to log into production databases using application logins (and to log any such attempts). He asked me if there was a more elegant way to do this, both because the trigger appears to sever the connection upon rollback, and because a rollback in a logon trigger gives a very explicit message about why the login attempt was unsuccessful:
Cannot connect to WINDOWS10\SQL14. Logon failed for login 'ApplicationAccount' due to trigger execution. Changed database context to 'master'. Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)
Outside of a firewall preventing access to the server at all, I don't think there is a straightforward way to deny the connection without revealing how it was enforced ("due to trigger execution"), which of course goes against all kinds of security disclosure principles. But I did have some ideas about how to continue preventing access via a logon trigger and to log the events seamlessly.
Solution
Let's say we have an audit log table, like this:
USE AuditDB; GO CREATE TABLE dbo.AuditLog ( [Login] NVARCHAR(4000), App NVARCHAR(4000), IP VARCHAR(48), HostName NVARCHAR(4000), EventTime DATETIME2 );
Chirag's trigger basically looked something like this, with data about the login pulled into local variables so that they could be logged after the rollback (this example just uses a single login name and application name pattern, but in reality there could be many):
CREATE TRIGGER LogonTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@login SYSNAME = ORIGINAL_LOGIN(),
@app SYSNAME = APP_NAME(),
@ip VARCHAR(48),
@hostname SYSNAME = HOST_NAME();
IF @login = N'ApplicationAccount' AND @app LIKE N'%Management Studio%'
BEGIN
ROLLBACK TRANSACTION;
SELECT @ip = CONVERT(varchar(48), CONNECTIONPROPERTY('client_net_address'));
INSERT AuditDB.dbo.AuditLog([Login],App,IP,HostName,EventTime)
VALUES(@login,@app,@ip,@hostname,SYSUTCDATETIME());
END
END
GOBut, as I suggested above, the logging to the audit table never actually happens, presumably because the logon trigger has severed the connection through an error with a severity of 20 (something you can see in the SQL Server error log, but not in the dialog in Management Studio):
Error: 17892, Severity: 20, State: 1. Logon failed for login 'ApplicationAccount' due to trigger execution. [CLIENT:]
You can see why this is a problem if you raise your own error of severity 20 (you must be an explicit or implicit member of sysadmin to run this):
RAISERROR('Uh oh!', 20, 1) WITH LOG;You'll notice that your tab in SSMS now says "not connected" and the messages pane has a series of errors:
Process ID 60 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Line 1
Uh oh!
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Enter Another Brain
As luck would have it, not five minutes after Chirag explained the problem to me, my good friend Jonathan Kehayias walked up, and we started chatting about the issue. He was quick to prove to me that the auditing could be handled by Extended Events or Event Notifications, while still preventing the developers from accessing the server in this context. I'm going to just handle Extended Events for now.
This solution has two parts. One is the event session to track the failed login attempts with the specific error message 17892; we're not interested in all the other 18456 failures. The other is a background job to poll the file target and populate the audit table when it finds new data.
First, the event session:
CREATE EVENT SESSION [Log17892] ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION
(
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.session_server_principal_name
)
WHERE
(
[error_number] = 17892
AND severity = 20
AND sqlserver.session_server_principal_name = N'ApplicationAccount'
AND sqlserver.like_i_sql_unicode_string(sqlserver.client_app_name,
N'%Management Studio%')
)
)
ADD TARGET package0.event_file(SET filename = N'C:\Temp\Log17892.xel')
WITH (EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);
ALTER EVENT SESSION [Log17892] ON SERVER STATE = START;
GONow, put this code in a stored procedure or directly in a job step, and schedule it for a reasonable frequency:
DECLARE @max DATETIME2 = (SELECT MAX(EventTime) FROM dbo.AuditLog);
SELECT @max = COALESCE(@max, '20000101');
;WITH f(x) AS
(
SELECT CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file
(N'c:\temp\Log17892*.xel',NULL,NULL,NULL)
),
x AS
(
SELECT
[login] = x.value(N'(event/action[@name="session_server_principal_name"]/value)[1]',
N'nvarchar(4000)'),
[app] = x.value(N'(event/action[@name="client_app_name"]/value)[1]',
N'nvarchar(4000)'),
[msg] = x.value(N'(event/data[@name="message"]/value)[1]',
N'nvarchar(4000)'),
[host] = x.value(N'(event/action[@name="client_hostname"]/value)[1]',
N'nvarchar(4000)'),
[time] = x.value(N'(event/@timestamp)[1]', N'datetime2')
FROM f
WHERE x.value(N'(event/@timestamp)[1]', N'datetime2') > @max
)
INSERT AuditDB.dbo.AuditLog([Login],App,IP,HostName,EventTime)
SELECT
[login],
app,
ip = COALESCE(SUBSTRING(msg, CHARINDEX(N'CLIENT: ', msg) + 8, 15), N''),
host,
[time]
FROM x; Now, if you try to connect through Management Studio using the ApplicationAccount login, and wait for the job to run, you should see a row in the AuditLog table.
At which point, you can remove the extraneous code in the logon trigger, so it becomes:
CREATE TRIGGER LogonTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@login SYSNAME = ORIGINAL_LOGIN(),
@app SYSNAME = APP_NAME();
IF @login = N'ApplicationAccount' AND @app LIKE N'%Management Studio%'
BEGIN
ROLLBACK TRANSACTION;
END
END
GONote that I have not yet performed any testing to assess the performance impact of implementing this solution - perhaps in a future tip or blog post. In the meantime, you can feel free to use it if security trumps performance, which it often does.
But Wait, I'm Running SQL Server 2008...
In SQL Server 2008, many of the actions and predicate filters were not available yet, and the functions to access Extended Events data had different signatures. So the above session will not work there as written. (And while you can capture severity 20 (and above) errors from the system health event session, it doesn't capture host name or application name, so it doesn't give a complete picture.)
The following session will run on SQL Server 2008 and 2008 R2, however note that it will cast a wider net, since many of the predicates simply weren't available. So on newer versions, the above session will likely be more preferable.
CREATE EVENT SESSION [Log17892] ON SERVER
ADD EVENT sqlserver.error_reported
(
ACTION
(
sqlserver.client_app_name,
sqlserver.client_hostname
)
WHERE (severity = 20)
)
ADD TARGET package0.asynchronous_file_target
(SET filename = N'C:\Temp\Log17892.xel')
WITH (EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);
ALTER EVENT SESSION [Log17892] ON SERVER STATE = START;
GOIn the query that polls for new errors, there are a few differences, most notably the need to parse the login name out of the error message:
DECLARE @max DATETIME2 = (SELECT MAX(EventTime) FROM dbo.AuditLog);
SELECT @max = COALESCE(@max, '20000101');
;WITH f(x) AS
(
SELECT CONVERT(XML, event_data)
FROM sys.fn_xe_file_target_read_file
(N'c:\temp\Log17892*.xel',N'c:\temp\Log17892*.xem',NULL,NULL)
),
x AS
(
SELECT
[app] = x.value(N'(event/action[@name="client_app_name"]/value)[1]',
N'nvarchar(4000)'),
[msg] = x.value(N'(event/data[@name="message"]/value)[1]',
N'nvarchar(4000)'),
[host] = x.value(N'(event/action[@name="client_hostname"]/value)[1]',
N'nvarchar(4000)'),
[time] = x.value(N'(event/@timestamp)[1]', N'datetime2')
FROM f
WHERE x.value(N'(event/@timestamp)[1]', N'datetime2') > @max
)
INSERT AuditDB.dbo.AuditLog([Login],App,IP,HostName,EventTime)
SELECT
[login] = COALESCE(SUBSTRING(msg, CHARINDEX(N'''', msg) + 1,
CHARINDEX(N'due to trigger execution', msg) - 1 - (CHARINDEX(N'''', msg))), N''),
app,
ip = COALESCE(SUBSTRING(msg, CHARINDEX(N'CLIENT: ', msg) + 8, 15), N''),
host,
[time]
FROM x;Next Steps
- Test this logon trigger, Extended Events session and polling code to see if it will work for your failed login auditing requirements.
- See these tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2015-04-03
