Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Prevent and Log Certain SQL Server Login Attempts


By:   |   Last Updated: 2015-04-03   |   Comments (3)   |   Related Tips: More > 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
GO

But, 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:

Msg 2745, Level 16, State 2, Line 1
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;
GO

Now, 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
GO

Note 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;
GO

In 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


Last Updated: 2015-04-03


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, 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 serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, December 14, 2018 - 2:32:50 AM - Eric Back To Top

 Hi Aaron,

unfortunatly, i've got this issue on live server...imagine how many peoples calling me..., is this a Microsoft's bug referenced ?

i must complete a document to explain the reason why all connections have been broken..

many thanks

Eric


Tuesday, April 07, 2015 - 3:54:38 PM - QuinnMcIlvain Back To Top

Friday, April 03, 2015 - 10:11:39 AM - Don Kolenda Back To Top

Thanks so much, Aaron!  I've been running a job that explicitily searches for certain Host Names and kills the sessions, but this is way better.  I'm going to add this to more of my Production machines, as this is a problem in our shop.  Thanks again!


Learn more about SQL Server tools