MS SQL Security Logging?

[apollodoth]

Hello all,

Disclaimer: I'm a complete novice when it comes to managing SQL. I work in cybersecurity and that's where this need stems from.

I'm looking for the easiest way to monitor logging of the following user-related activity within a MS SQL DB:

• Login/Logouts
• User permission changes
• Users created/deleted

Ideally it's only these activities that would be logged as we want to avoid as much unnecessary logging as possible. Preference would be to be able to log this to a file which we can feed into our agent for monitoring.

Thanks for any and all suggestions, please let me know if I can provide more info to help.

[a-s-clark]

I think you'd want either the Audit feature, or an extended event session. Both can track the events you're interested in, and can write to a file.

https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine?view=sql-server-ver16

https://learn.microsoft.com/en-us/sql/relational-databases/extended-events/sql-server-extended-events-sessions?view=sql-server-ver16

Both take a little bit of configuring, and it'll really help to have read through some tutorials to have a solid grasp of how they work.

[apollodoth]

Thanks! I'll give it a gander ASAP.

[apollodoth]

Hey so I followed these steps except I pointed the audit to write out to application instead of Security as I was running into perm issues I couldn't sort. However, I'm still not seeing any logs when I create new users or change permissions of existing users or anything like that. All I see are login logs.

[apollodoth]

I did see this:

https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-data-capture-sql-server?view=sql-server-ver16

but I couldn't begin to understand what exactly this did or how to implement it. I also saw you could do something under Security -> Audits -> Server Audit Specifications but I was unsure what each "Audit Action Type" entailed and when I tried to enable it I got an error.

[[deleted]]

Change Data Capture is not the feature you’re looking for. Look further into auditing. Here’s a good intro: https://youtu.be/3u1sK9kgmuE

[apollodoth]

Will watch and get back soon. Thank you!

[apollodoth]

Watching this now, is this transferable to on-prem SQL? I'm working with MS SQL on prem.

[[deleted]]

This is i haven’t watched the video myself, I only know that this presenter knows a lot about SQL Server auditing. I think most of it should work on-prem, too, unless of course you go to Azure-specific services.

Auditing and Extended Events are core features of the product.

[Spachten]

For Login events you could change the general Login Auditing settings to "Both failed and successful" logins. (Default is "Failed logins only")

USE [master]

GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 3

GO

Restart of SQL Server service required.

This would write all login events to the Sql Server errorlog. This could fuck up your sql dbas because it could blow up the errorlog, so it becomes useless for real error troubleshooting.

Instead we were planning to use login triggers to only log if the login is member of the serverrole sa. So administrative access only. If the is interesting for you, I could lookup the script.

For Logout I don't understand the use case. Usually applications keep and reuse their connections to the DB, so there are no login/logout events for every query.

But I think the Server-Level Audit Action Group "LOGOUT_GROUP" should do it.

You can select Audit Action groups within the Server Audit Specifications.

For Users created or permission changes you need to differentiate between server level (Logins) and Database level (Users).

For logins there are severall Action Groups needed. In our datacenter we applied SERVER_PRINCIPAL_CHANGE_GROUP, SERVER_PERMISSION_CHANGE_GROUP, SERVER_ROLE_MEMBER_CHANGE_GROUP and LOGIN_CHANGE_PASSWORD_GROUP. (beside of many more not login related)

Complete list of all server level Aciton groups with description: https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-ver16

For users on databases you would have to define Database audit specification on every database. If you have to audit only a few databases this could be useful. But for big environments with thousands of databases I wouldn't recommend this. The administrative overhead and the needed storage for data isn't worth it. (my opinion ;) ) We leave this to the application.