Auditing failed logins in an SQL server is very important because it helps to track all the events that took place previously until the current moment.
As part of our Server Support Services here at Ibmi Media, we do regular SQL related activities for our esteemed customers.
We will look into the steps to be taken when auditing failed logins in SQL server.
More about Auditing Failed Logins in SQL Server
Auditing an SQL server is a process of investigating past happenings and therefore will require that the time frame is known.
In an SQL Server, Auditing both Login Failures as well as Successes is possible. Different approaches can be taken when auditing the logins in SQL Server including;
i. Via SSMS (SQL Server Management Studio)
ii. Via SQL Server Trace
iii. Via SQL Server Auditing
We shall discuss each factors briefly below.
Via SSMS (SQL Server Management Studio)
Follow the following steps to Audit SQL server via SSMS;
1. To begin, Make a connection to the SQL Server via the Object Explorer.
2. Next, Do a right click on the SQL Server and choose the Properties option from the popup menu.
3. Now click on the Security tab which will lead you to the Login Auditing page. Make the necessary changes here and click on the OK button to save.
There exist four different options to choose from when working with login auditing. There are;
None - This signifies that neither failed or successful logins will be audited.
Failed logins Only - Signifies that although failed logins will be audited, successful ones should be ignored.
Successful logins only - This means that only successful logins will be audited, leaving out failed logins.
Both failed and successful logins - This signifies that failed logins as well as successful logins will be audited.
After making the necessary changes, ensure that the SQL server service is restarted to enable the changes to be effected.
Via SQL Server Trace
To set up a trace from the same sql server or a different one, all login attempts to the SQL server should be audited and saved in a file.
Follow the steps below;
1. In the SQL Server Management Studio, Open Profiler. Then go to "Tools" followed by SQL Server Profiler. Another way to do this is to login to go to "All programs" and select SQL ServerPerformance Tools followed by SQL Server Profiler.
2. Next, Make a connection to the SQL Server Instance you want to work with and modify the trace file name.
3. Now save it to file and save as SQL table by choosing the save to table option.
4. The location when you want the trace file to be should be selected. From here go to the Event Selection tab just at the top.
5. Do check the Show all events box. Then Choose "Audit Login" followed by "Audit Login Failed" and click run to complete the process.
Via SQL Server Auditing
To do SQL Server auditing inSQL Server 2008, follow the steps below;
1. Using the SSMS, connect to an sql server instance. Go to the Audits via the Security dropdown.
2. Make a right click and select new Audit. Here give the Audit a name and select location where you want the Audit files to be saved and click OK to create Audit.
3. Now right click the recently created Audit and enable it.
4. Next, Right click on Server Audit Specification and Select new Server Audit Specification. Give it a name and select LoginAudit from the Audit drop down menu.
5. Save the Audit types as "Failed Login Group" and "Successful_Login_Group" and click ok.
6.Finally, right click on the new created group and enable it.
The audit logs can be viewed by right clicking Login Audit which was previously enabled and then select view Audit logs.
To view the audit results recorded in the SQL Server log, use the extended stored procedure in T-SQL, xp_readerrorlog to access the contents of the SQL Server logs. Use the command below;
EXEC xp_readerrorlog;
