3

The goal is to audit sysadmin logons as efficiently and safely as possible without auditing logons of other users, so as not to be logging excessive data.

I have found the information on auditing sysadmin logons by using a logon trigger, and in general that would accommodate our needs. However it does have the drawback of being synchronous, which means that users can't log on if the trigger fails for some reason, and it will slow down their logon a tiny bit.

With the logon trigger, I can use IF IS_SRVROLEMEMBER ('sysadmin') = 1 to dynamically determine if the user is a sysadmin, but something like this does not appear to be possible with audit objects. It appears that audit filters have to be a simple server_principal_name = 'domain\username' (i.e., you can't use any SELECTs or functions to determine if the user is a sysadmin).

So the only thing left that might even be possible, as far as I've found, is using Extended Events, so I wanted to ask the community if this is something I should bother looking into. The event would probably have to capture all logons (again, as the filters can't run code), and then we'd have to have something that consumes the event to filter and log the relevant activity.

6
  • Security audit uses XE behind the scene. Auditing member of sysadmin role should give you the same result. Am I correct? If yes, you can filter for those server_principal_name. Jun 19, 2017 at 20:07
  • Can't audit role logins, only user logins and an audit filter can only have a string or a number after the operator, so WHERE ([server_principal_name] = 'CORP\ADMIN_1' OR [server_principal_name] = 'CORP\ADMIN_2') is about as complex as you can get. You would have to specify all of the sysadmins by name, which is not impossible, but will require maintenance when staff changes are made. Jun 19, 2017 at 20:15
  • I agree. Just curious if you already gave them SA privilege why do you want to audit them in first place? Jun 19, 2017 at 20:23
  • This is something that security consulting firms are advising now. If login activity for an account deviates from the norm, it could indicate that the account has been compromised. Jun 19, 2017 at 20:30
  • Just the login. Jun 19, 2017 at 20:38

1 Answer 1

1

This may work for you, but by no means do I consider it elegant (or even good for that matter).

First, configure the server to log all successful and failed logins.

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3
GO

Now, to audit those logins that are in the SYSADMIN fixed server role, you can run through the following process:

CREATE TABLE #ErrorLog
(
    LogDate DATETIME NULL,
    ProcessInfo VARCHAR(20) NULL,
    [Text] VARCHAR(MAX) NULL 
)

INSERT INTO #ErrorLog
exec xp_readerrorlog 0, 1, N'Login succeeded', NULL, NULL, NULL, N'DESC'

SELECT *
FROM (
    SELECT IS_SRVROLEMEMBER('sysadmin', SUBSTRING([Text], CHARINDEX(CHAR(39), [Text]) + 1, CHARINDEX(CHAR(39), [Text], CHARINDEX(CHAR(39), [Text]) + 1) - CHARINDEX(CHAR(39), [Text]) - 1)) AS isSysAdmin
    , SUBSTRING([Text], CHARINDEX(CHAR(39), [Text]) + 1, CHARINDEX(CHAR(39), [Text], CHARINDEX(CHAR(39), [Text]) + 1) - CHARINDEX(CHAR(39), [Text]) - 1) AS loginName
    , *
    FROM #ErrorLog
) InstanceLogins
WHERE isSysAdmin = 1

DROP TABLE #ErrorLog

What this does is pull all successful logins from the current error log, identify the login name, and pass it into the IS_SRVROLEMEMBER function you were already tying into with your Login Trigger approach.

This will potentially balloon your ERRORLOG, but it won't block/slow logins like a failing trigger may cause. It will also provide login times and if you have some SQL Auditing in place, you may be able to correlate these with activity, etc. At any rate, I think this will give you what you're looking for. Final note: you don't have to audit the failed logins, but I find that information more useful than not.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.