- Hosts: Steve Stedman / George Stedman
- Topic: Login Auditing
- Recording Date:
- Listen on Spotify!
Login Auditing Stedman SQL Podcast Sn 3 Ep 9
In this episode of the Stedman SQL Server Podcast, Steve and George Stedman compare two powerful native SQL Server options for login auditing: SQL Server Audit and Extended Events. They explain why auditing logins matters for compliance requirements such as SOX, PCI, and HIPAA, demonstrate how to configure both approaches with downloadable scripts, and discuss the pros, cons, performance impact, and data captured by each method. The episode also highlights why logging successful logins to the SQL Server error log is a poor practice, explores real-world auditing and troubleshooting scenarios, and shares best practices for long-term audit retention, security monitoring, and identifying suspicious login activity. Whether you’re a DBA, developer, or security professional, this episode provides practical guidance for implementing reliable SQL Server login tracking without sacrificing performance.
Podcast Transcript:
Steve Stedman 00:16
Hey everyone, and welcome to Stedman SQL Server Podcast. This is season three, episode nine, and today’s topic is login auditing showdown, SQL Server audit versus extended events, and some ways that we think you should not do it as well outside of that. So, Steve Stedman here, and today George Stedman has joined me for this episode. We’re with Stedman Solutions, and today we’re going to dive into one of the most misunderstood topics of login auditing.
So, have you ever tried to answer the who’s logged into the production server at 2 am question, only to realize that your error log is full of noise and your audit trail is missing half the story. Well, today we’re going to compare two native SQL Server tools for auditing logins and logouts. We’re going to talk about the SQL Server audit, and we’re going to talk about extended events. We’ll break down each and go over exactly how to set them up, what you’re actually going to get, performance impact, storage, and real-world pros and cons, whether you’re chasing compliance for SOX, PCI, or HIPAA, or troubleshooting connectivity issues, or just want to know who’s logged in. When this episode gives you the scripts and the decision framework you need. The target audience for this is SQL Server DBAs, developers, security compliance teams, and anyone who needs reliable login tracking without killing performance. Need help with any of this, just reach out to us. We’re here to help. But first, a word from our sponsor:
Is your SQL Server slowing down your business? Slow queries, bottlenecks, and unexpected issues eating up your time. Introducing Database Health Monitor, the powerful tool built by our team at Stedman Solutions. Get real-time insights into performance with over 100 built in reports, index analysis, weight stats tracking, and proactive alerts. Quickly diagnose and find problems before they impact your operations with an easy to use interface for DBAs and developers. Monitor unlimited servers, tune queries, and keep your databases running healthy and fast. Download a free trial today at Databasehealth.com and take control of your SQL server performance.
All right. Hey George, thanks for joining me on the podcast today.
George Stedman 02:40
Thank you for having me.
Steve Stedman 02:42
Yeah, so let’s start talking about why login auditing really matters, and kind of what, why this is important. Can you think of any reasons why we want to, why we may need to do this, or a client may ask us to do this?
George Stedman 02:59
In the big one that I could think of as compliance for peace or like credit card processing or HIPAA. Actually, I don’t know if HIPAA has a SQL login auditing requirement, but I know like PC, yes, or whatever it is for credit card audit CI, yeah, PCI and SOX.
Steve Stedman 03:20
So, yeah, so basically we work with a lot of clients that are in finance, healthcare, other business-related areas where they need to know who’s who’s using the SQL server, and they’ll have some kind of a compliance or audit that they have to go through, and they’ll say, well, we need to capture tracking of logins, so what most DBAs know is you can just go into the settings for your server and say log this all to the error log, and that’s one of those things that let me just share my screen here real quick, and I’ll show you an example of that. So, first off, for the demo, we have this command prompt window with a bunch of data flying by in it. I’m going to just click here to see what it is. What this is, is a script that I wrote that basically has 100 threads. It’s logging people in, waiting a few seconds, logging them out while they’re logged in. It runs a query just to say select get date and simulating some work to show that there’s people coming and going from the system. Okay, that’s running in the background. We’re just going to let that run just like a real load might be going on your SQL server, but what we’re seeing in this is an example of someone who turned on the setting, and I’ll show you where that setting is here first. And this is not the way to do it.
George Stedman 04:48
Maybe you should show where the setting is if we’re not recommending it, but I guess we could recommend to go check it turned on.
Steve Stedman 04:57
So, if you own the server properties, you click on secure. 30, there is this option here to log nothing failed logins, successful logins, or both failed and successful. So I’ve turned on both failed and successful logins, and what it’s currently tracking is in this, the error log, which is a – I always argue is a great place for SQL Server to store errors, since it’s often called that the log file viewer. It logs in all kinds of stuff, but what’s really interesting is, as I refresh this, this script that we have running is doing hundreds of logins a minute, and we are nowhere near seeing hundreds of logins a minute in here, so it’s showing that there’s a lot getting lost in the error log in the SQL Server log when this is happening. So our recommendation is that, yeah, it’s probably a good place to store failed logins, because as a DBA, that’s something that you’re going to want to track. But as far as storing successful logins in that log, not a real good thing to do, plus storing every successful log in there, every successful login will completely just flood your log, so you don’t actually see any errors. All you’re going to ever see in there is login succeeded, and it’s going to bear very real problems that are showing up in your system that you want to know about it. So, yeah, we don’t recommend that. Let’s jump back, so I guess. Here’s the thing, George: you’re working somewhere, or we’re working with a client, and somebody in management is doing some kind of an audit when they come along and say, “Well, we need to log every user that logs into the system. Okay, we’re telling people we don’t want to do it in the error log. What’s what’s what are some other ways that we could do it if we’re telling people not to do it that way? Where would we start?
George Stedman 06:48
I guess simple one is a SQL Server audit.
Steve Stedman 06:55
Yep,
George Stedman 06:56
I guess I don’t, I didn’t look too much into this one. This is this was yours.
Steve Stedman 06:59
So I’ll roll over a little bit, but basically SQL Server Audit is designed to do exactly this, plus some other stuff, but it’s designed to track who’s logged in, when they log in, what’s happening, successful logins, failed logins, and it even tracks if anybody changed the audit, so if we’ve got an audit running and some hacker wants to turn it off or change it, that gets logged too. So, I guess we can go into the step-by-step setup of how that works. The other one we’re going to show in a little bit is extended events, and this is kind of built off of extended events, but it’s a really lightweight way of tracking logins compared to other ways of doing things, so why don’t.. why don’t I share my screen again?
So, what we’ve got here.. I’ve.. I’ve got.. we’ve got a server that we’re using for testing on here. I’m just going to run SP who two and see who do we have connected. Well, we’ve got a whole bunch of logins down here that are test user one through nine. Those are all users I created that are being run by that other script that are being logged in and logged out continuously. You can see there’s a whole bunch of them all logged in right now with sleeping tasks just waiting. So there are ways that you can set up the SQL Server audit using the dialogs in SQL Server Management Studio. Now we’re not going to cover that, because frankly, I really, really dislike using the dialogs to set things like this up, because they’re just so painful. So we have a script here that you’re going to be able to download from the podcast page on Stedman Solutions associated with this podcast, as well as the other script, we’re going to show you in a little bit, and what we’re going to do here. Step zero, I’ve got here, this is just to clean up some previous tests, so if we’d run this before, there would be some script, some things running. I’m just going to make sure that we didn’t have a test case of this still running, so I’m going to turn all those off. They all failed because they weren’t actually running, and then what you do is you just basically say create a server audit and you give it a name, you tell it where it’s going to log, and I’ve created a temp directory for the demo purposes, but this is something you might want to log longer term than that, but the max size of the file is 200 megabytes, and you can adjust that, and the max number of rollover files will be 20. If you want to keep this indefinitely, what you may do is extend that number even larger and say keep 1000 of them, but then have some process that moves them off of the server to long-term storage, or something like that. Once the file has been written to, it’s easy to move at that point. And then there’s a setting in here for Q to lay, which is sort of how long things will get cached before it writes to the file, and I found that if you set that down to zero and it tries to do it on the fly, we end up losing a lot of logins with that. So, by setting the Q delay to 1000 which is a second, there is a chance you may lose up to a second worth of your logging if the system restarts, but it runs. Lot smoother with a setting at that level. Another important thing in here is the on failure setting, setting that to continue, meaning that if something does go wrong in here, it’s going to just continue and let things going.
Steve Stedman 10:13
The other option is you can set the on failure to be fail operation, and what that’ll do is if something gets overloaded or stuck on the logging, it’s going to actual actually keep people from being able to log into the system, so I’ve just got it set to on failure, continue, and then with this you can set up where clauses where you’re basically saying where, and if we were to uncomment that, and I’m just going to show example here, just uncomment this, and we’d say where the client app name is not like SQL Agent T SQL job step, well, that would throw out all of the job steps logins that are coming from the SQL Server Agent. For instance, for demo purposes, I’m going to leave that in there. We don’t have that much running on the SQL Server Agent on this server, so it’s not going to hurt anything. So we just create the audit by highlighting the script and running it, way easier than trying to figure out what some of the dialogs mean in SQL Server to do this. Then we’re going to alter the audit and turn it on, and next we’re going to say that this is going. This audit is going to be auditing successful logins and failed logins. So now that it’s running,
George Stedman 11:33
can I jump in real quick?
Steve Stedman 11:35
Yeah, go for it, George.
George Stedman 11:37
Yeah, the other cool thing you can do with the SQL Server audit is if you scroll back up to your original initialize command,
Steve Stedman 11:44
yep,
George Stedman 11:45
we can, we can edit this a little bit when we actually publish it, but the where it says to file, you can change that to file to to application log to save it to the event viewer, or to security log to save it to your event viewer app security logs, which is the best way, or is a really good way to save it somewhere where it can’t easily be deleted.
Steve Stedman 12:10
Yep, good point. And then, if you’re using some other integration tool that grabs those normal logs and roll them up somewhere, that data can go along there as well with it too. So, good point,
George Stedman 12:21
that’s that’s one of the biggest perks of using the SQL Server Logic, too, is you can save it to those Windows tools that are already integrated into a lot of third-party applications.
Steve Stedman 12:32
Yep, okay, so we’ve been through the whole process to get it set up. Did we actually turn this on? I don’t remember, we got distracted with questions there. Yep, so it’s on, then now it’s sitting there monitoring data, and because we have that script running, that’s just logging people in and out. We should just be able to go and query, and what this is going to do is go look for that SQL audit file that’s been created. It uses this sys.fn underscore get audit file to pull the information in about the audit. Let’s run this, and I’ve ordered it by the most recent event time, and we can see right now, and it’s being logged in UTC. Not sure why it does that, because in this case the server set to Pacific Time, but 2016 would be 116 Pacific Time, which is what time it is right now, and we can see that test user nine was the last one that logged in, and the action here, LG is, is login successful, and LGIF would be login failed. If you saw any of those, and then if we.. if I don’t know if we’ve got enough in here, but if we go back to the very beginning, you can see AU SC, that’s what logs who changed the audit, so you can go through and find things that are related to specifically to who’s edited or who’s changed the logic. The audit, there’s one thing I’m going to throw in here is I’m just do a top 100 because if, as the audit grows over time, if you don’t select a certain top amount there, that could take a while to run this query. So we run this, there’s 100 we get it back real quick, we can see things like, well, it succeeded. Well, LG is told us that already, anyway. And see the session ID that they connected as, what login they used, the SQL Server Principle SID – not entirely useful all the time, but it’s just there. The client IP – this is handy to know what server they’re coming from because this 192168 22 dot 21 that’s my laptop right now. That’s where these scripts are running, simulating all the load. What the application name is, this would be Management Studio, or different things like that. If it was those kind of connections, additional information, there’s some XML here, and then some information about the statement.
So, if we grab, well, there’s.. if we grab that XML, we can see there’s more information. It’s really just looking at a lot of the same information that we’re looking at here, plus a little bit more, so. Yeah, not.. I mean, if you want more information than what’s being displayed, you can go parse that out of the XML if needed. So now this is up and running. It’s going to stay up and running until somebody turns it off, and the way you would turn it off would be to run commands like this that we had at our beginning of our script to clean it up and turn it off. This is one of those things we were, we’ve worked with a number of clients on this, where they’re trying to understand, like who all is logging into the system, either for auditing some type of an audit that has to be done, or when we’re looking at like migrating a server to make sure we’ve got all the users figured out that have to be migrated to the new server. We can log this and track, like with one client we tracked for a whole week, all the logins, and then, but after, like, the first couple hours running it, we added a where clause to exclude a number of the common logins, so that we were only tracking those that we didn’t already know about. So this script is going to be included. You can download it with the podcast, and it’s really to use it in your own environment. You would simply just have to go change the path to where you’re going to save the files on disk, and then run with it from there. So, any questions on that, George, before we want to move on to the next one?
George Stedman 16:18
Not particularly, but the big, yeah, I guess I always say the big bonus of this one is, yeah, you can save it to your event logs or your secure Windows Security log, which is tamper-resistant for auditing purposes.
Steve Stedman 16:36
Yep.
George Stedman 16:38
Oh, you also get a ton of information.
Steve Stedman 16:41
Yeah, super useful, and it’s the kind of thing that, as long as you’re not running out of disk space and you’re cleaning up over time, you can let it run for a very long time, and it doesn’t put very much load overall on the whole system. Okay, great. That brings us next to extended events. So, why are extended events, what are they going to be due to be different from what we’ve just done here? Or why would you want to use extended events?
George Stedman 17:09
Extended events are actually what the SQL audit grabs gets its data from, but the cool thing about actually working with the raw extended events is it’s overall a lighter weight process, so if maybe you’re not doing those compliance, you don’t need the like required compliance features of the SQL audit, you can run extended events to just get the information pretty much quick and dirty, like it’s less resource overhead, it’s you said it’s just overall lighter, and you are a lot more flexible in where you can send the data. I don’t think you can actually send it to the security log or the application log, but you can, because you can throw it into it, like you can throw it into a table, you can throw it into a file, you can kind of do whatever you want with it.
Steve Stedman 18:02
Yep,
George Stedman 18:02
Because it’s the more raw method. So, but the flip side of that is it’s a little trickier to tweak and configure and set up.
Steve Stedman 18:13
Okay, so when we say trickier to configure and set up, well, that what George really means is if you don’t have our sample scripts from this, it’s trickier to configure and set up, because once you have the sample scripts, they’re both about just as much work to set up either way. Okay, shall we go into a demo on that then, George?
Steve Stedman 18:33
Here we go. This might be tricky. I’m doing the mouse clicking, but you’re doing the description of what we’re doing.
George Stedman 18:40
The first big difference with that you can do with extended events is if you look at the script, we have login and logout auditing, so with that you could actually see how long someone’s session was, and if you’re also tracking like queries and stuff that maybe like maybe at like 3am last night you’re like, well, like, why did this run well? You can go see, like, looks like the SQL agent started and then stopped after that query was done. So, or maybe there’s surprise downtime in the middle of the day as a developer when it’s the wrong table, you could go and see whose logins correlate with that time.
Steve Stedman 19:19
Yep, so the script we have here, I think, is very, I don’t know, similar themed to the audit one that we just showed, where we have our cleanup is the top, is make sure that we didn’t have it running for before, so I’m just going to run that, clears it out, then this is the thing that creates it all and sets it all up, followed by an alter event to turn it on, and go ahead and run that, and I guess we’ll run that and let it collect for a minute, but you’ll notice in here we can do where clauses similar to what we saw in the other, and basically when we say, like in this example, it would track everything that was in. Not the SQL Server agent user on this specific server that we left that SQL 2022 name in there for there, okay. So, how do we get at the data now, George?
George Stedman 20:15
We have the results query below, so this query is, you can also use where clauses in, so if you’re trying to filter down to a specific time, you could filter by time, you could filter by login, but it’s as you can see, it’s a little messier on the query because it’s actually going through and parsing the the extended events raw data file, so.
George Stedman 20:43
A little messier than the SQL audit one, but if you look down at the bottom, it’s kind of just it throws it into like a temp table, throws it into…
Steve Stedman 20:55
Choosing a CTE.
George Stedman 20:56
Yeah, it’s using a CTE, yeah. And then at the bottom there you have the select top 100 and that’s your real select statement.
Steve Stedman 21:05
Yep,
George Stedman 21:06
The other ones that look nice.
Steve Stedman 21:09
Yep, so if we run this just enter one to go look at like the directly pulling from fn underscore xe file target read file that brings it in as g, just a bunch of xml, and click on it and see what the XML looks like, there’s all the data in there, and any of those things we can then parse out. So, what the CTE is doing then is it’s doing the parsing here, and then, like George says, it’s just returning the results down below. So, if we run this again, ordering by login time, we can see that the most recent login was test user three, but then also associated with that connection ID, I think that’s the same one. There we have two lines that are one of them is the log out, login and log out, pretty darn quick there, and then seeing the same things down here, log in and log out, so one of the big difference here is that we’re tracking logouts, which the other one was not tracking, so if you want to look at something like, well, what was the average session length, you could track that with this,
George Stedman 22:17
and like I said earlier, it’s the quick and dirty, it’s a little more lightweight, maybe it doesn’t always grab everything, like we can see here, it’s got your host name, but it doesn’t have your IP.
Steve Stedman 22:29
The client IP, I could not figure out how to make a track, there may be some way to do it, but we might need another event on that, so but with the basic extended events for tracking login and log out, it does not appear that it tracks the IP address at all. So, there’s a benefit to using the login auditing over the extended events, is that you get the IP address with that. So, both of these scripts are available to download with the podcast. They’re easy to use, just copy and paste it. Just need to make sure that that temp directory, or whatever directory you’re actually logging to, is set up appropriately. Pretty straightforward to use there, I guess. We’ve done all the hard work, so that nobody has, so it’s easy for anyone else who’s going to use this now. Anything else on the demo here, George? Before we want to kind of move back into the topic,
George Stedman 23:16
I guess. I think extended events is definitely, if we’re going to do a quick, quick comparison between the two of them, I guess. SQL Server of it audit is higher performance. It’s a little bit easier to use, because you could technically use the GUI. It’s good for standard actions. It does not natively have login, log out tracking, so you don’t actually know how, like, you know when they logged in, but you don’t know how long they were logged in. So, if someone had, like, a multi-day session, it would be kind of hard to track what they were doing. And then it’s got all the auditing, auditing features, like compliance friendly, and it’s got the IP, it’s got pretty much everything that the compliance vendors want.
Steve Stedman 24:01
So, I think then if someone is out there and they have they’re at a point where they’ve been told that they need to do auditing and our recommendation is you give the SQL Server audit a try first because of the pros and cons outlined here, and then if you need something more, or something different, or more temporarily, check out extended events, but do not turn on the log every successful login to the error log, that’s just a mistake in my opinion. Okay, so some things to think about, kind of on the best practices with this, I mean, some environments that we work in, they only need to keep this information for a few days, others it needs to be kept indefinitely, and if you’re in one of those positions where the data needs to be kept for a very long time, well, one of the things to consider there is this directory that all. These are being written to just creating a script or a batch or something that will grab that and copy it off to some permanent long-term storage, probably even zip those files and store them off to somewhere that’s that you can be able to keep around for a long time. The other thing that would be interesting, if we wanted to do alerting on suspicious login, any thoughts on that one, George?
George Stedman 25:24
I guess, yeah. Either of these, you could put into a job and have some kind of logic to determine what is a suspicious login, like maybe you just have an email alert if it’s someone logs in outside of business hours, that’s not like a service account.
Steve Stedman 25:42
Good, or pretty simple, or if you have a policy that says no one is supposed to log in as SA, which is supposed to be a good practice.
George Stedman 25:51
Yeah.
Steve Stedman 25:52
Perhaps you have an alert that runs and tells you when somebody logs in as the SA user.
George Stedman 25:58
Or if you have those service accounts, like, say, you have, like, a third-party application that uses your service, your SQL Server, and you configure the SQL Server login, like you’re supposed to. You could track to see if that login ever comes in from not the same IP or not one of the application IPs. Oh, yeah, that’s a good one.
Steve Stedman 26:19
One, or even like a specific user, like let’s say we’ve got the George login, and we know that George is only allowed to connect from his laptop. In that case, if we see you suddenly connecting from seven other servers, well, that tells you maybe someone’s got a hold of your username and password, and that would be a bad thing, right?
George Stedman 26:38
Yeah,
Steve Stedman 26:39
yeah,
George Stedman 26:39
or I’m lost, and I’m just opening SSMS on every server that I can find.
Steve Stedman 26:46
Yeah, yep. Okay, so cool. I think that kind of wraps up the overall demo, and the pros and cons, and how to do it. I mean, the thing is, you could go write these scripts yourself, but grab them and use them. It’s going to save you some time, I think. I,
George Stedman 27:06
and even just starting with our scripts and googling how to modify them, or even AI, how to modify them and tweak them for your specific scenario, like I said, or extended events is kind of, kind of messy, but you could definitely tweak it to get a lot more information than the SQL audit can, which is great for troubleshooting and like doing, like trying to figure out why something happened. When
Steve Stedman 27:37
so we were on a call with a client a couple weeks ago and they were asking, do you have any way to do this type of auditing, and I’m like, oh my gosh, we just wrote these scripts, and we can do it this way or we can do it that way, and we had it running for them, I think, later that day, no problem, quick and easy, it’s just having the right tools in your tool belt, okay, so if anyone wants to submit any questions for future episodes, one of the things we’re going to try and do is answer questions about SQL Server at the end of the podcast. So feel free to send that to us. You can email me, Steve at Stedman solutions.com or you can post comments in the in the link below the podcast, and we’ll see what we can find there, and get back to it. Emailing me is better, because I’m guaranteed to see it. I don’t always catch all the comments on YouTube, for instance. And I guess that you think of anything else you want to cover before we wrap this up. It’s just.. it’s one of those.. how so complicated, but it’s not..
George Stedman 28:37
Definitely.. I would just do a shout out to say, if you just go check your go check your setting in SSMS or on your SQL server, just go check to see what it’s set at. If you’re logging all the logins, it’s kind of clogging your error log up, and there’s better ways to do it.
Steve Stedman 28:58
Yeah, and it’s really kind of embarrassing if you’re monitoring performance, and you go and look at, like, what’s the most poorly performing query, and it’s the query that’s actually accessing your error log on SQL Server, that’s a sign that there’s something that’s not being done right there. We ran into that on a performance assessment just last week with a client. So, okay, great. Well, George, thank you for joining me today.
George Stedman 29:20
Thanks for having me.
Steve Stedman 29:21
That think that wraps it up. Thanks, everyone, for watching. And don’t forget to smash that like button and click the bell icon on YouTube in order to get notified of future updates or podcasts. Have a great day. Bye. thanks for watching our video. I’m Steve, and I hope you’ve enjoyed this. Please click the thumbs up if you liked it, and if you want more. Information, more videos like this, click the subscribe button and hit the bell icon, so that you can get notified of future videos that we create, do.