SQL Server Login Auditing
In recent months I have been asked to ensure we are auditing both successful and failed logins, not just failed logins. It is simple enough to open SSMS, connect to the instance, right click on the server, chose properties, click on security and then check the proper radio button. When putting together or most recent updates to our server installation guide I decided to spend the few minutes to research how to make this change with few steps.
In my case I need this to be both failed and successful logins. I will execute the following code within SSMS.
EXEC xp_instance_regwriteN'HKEY_LOCAL_MACHINE', N'SoftwareMicrosoftMSSQLServerMSSQLServer',N'AuditLevel', REG_DWORD, 3
Notice I am updating the registry and setting the AuditLevel to a 3. What are the other options?
None = 0
Successful Logins Only = 1
Failed Logins Only = 2
Both Failed and Successful Logins = 3
Here is a visual as well.
6 Comments
I have found that logging to this level makes the SQL Server logs unuseable. I have satisfied the requirement for seeing all logins by setting up SQL Server Audit to log all logins (successful & failed) with the SQL log only recording failed logins.
nb: SQL Server Audit available in Enterprise Edition only
Carl
Auditing is the way to go, but if it is not an option and your security department mandates both failed and successful logins what can you do? 🙂
Hi Tim,
If they mandate it has to be in the SQL Server log, then not much. But if the audit output file will satisfy their requirement, then send it there and keep your SQL Server logs for DBA/operational information.
It always drove me nuts when you’re looking for log (operational) information and all you see is a million successful logins.
Carl
Most of my environment is Standard Edition so I have no choice. At least increasing the number of logs helps me a bit but it is still cluttered with successfull login entries.
Agreed, there are fewer options for this in Standard Edition.
Though this is a somewhat old post, I wanted to spread my solution: I use a Perl script to read the log files of all my servers (when I was admin of multiple servers) and it filters out all the successful logins, so failed logins and failed jobs stick out like proverbial sore thumbs. I caught some intrusion testers this way with a timestamp and IP address in our network of where they came in from.
And I still have the full log if I need to see succussful logins for some reason. The nightly maintenance job emails me a DBCC summary and I have filtered logs sitting elsewhere.