SQL Server – Login Auditing
SQL Server Login Auditing can be used to monitor login activities on SQL Server Database Engine. SQL Server can log both failed and successful login attempts on the server. It provides four different options for Login Auditing.
1. None – No Login activity is logged,
2. Failed logins only – Only failed attempts to login to SQL Server are logged,
3. Successful logins only – Only successful logins are logged,
4. Both failed and successful logins – Both successful and failed attempts are logged.
To configure these options you can use Management Studio, Go to Server Properties > Security Tab:
A Server restart is required when you change these options.
These logged event can be viewed using Event Viewer:
1. Open Event Viewer
2. Go to Windows Logs > Application,
3. Both Logon success and failure events can be found here:
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
Can I NOT add the successful logon to the Windows log and still store them in SQL? The application log fills up on my server so fast I can only troubleshoot (by filtering out info logs entries in App log) 3 days worth of fails and errors. The reason it has so many successful logons is it has many membvers sending entries in from several geo areas each sending in short spurts of new db entries. Each server sends a successful logon, enters a few rows into a table and goes away. Repeating every so often (variable) and with several SQL servers sending entries to one SQL that stores specific DB entries from each server.
To store these in SQL, you can create LOGON triggers or use SQL Auditing feature.
Becareful using Logon triggers which may cause logon failures to all users if configured incorrectly. When you can not connect by any other method use Dedicated Admin Access to the Server Instance.