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.

This entry was posted in SQL Tuning, SQLServerPedia Syndication, Tibits. Bookmark the permalink.

5 Responses to SQL Server Login Auditing

  1. Carl says:

    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

    • Tim Radney says:

      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? :)

      • Carl says:

        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.

Leave a Reply

Your email address will not be published. Required fields are marked *