Auditing failed logins in the SQL server at times is important to analyze the events that happened in the past.
As a part of our Server Management Services, we help our customers to perform complex SQL related tasks regularly.
Let us today discuss the possible steps to audit failed logins in SQL server.
Auditing failed logins in the SQL server
To obtain the login details of a particular database in real-time is an easy task. However, to perform an investigation on an event that happened in the past, we will require these details from a particular time frame.
SQL Server permits the auditing of both login successes and failures. There are different methods available to audit the logins in SQL server.
1. Using SQL Server Management Studio
2. Using SQL Server Trace
3. Using SQL Server Auditing
Let us now look at each of the methods in detail.
Using SQL Server Management Studio
The first method to turn auditing on is by using SQL Server Management Studio. For this, follow the steps below:
- First, connect to the SQL Server in Object Explorer.
- Then, Right click on the SQL Server.
- Choose the Properties option from the pop-up menu.
- Click on the Security page which will bring you to where you can set the login auditing
- When the choice is made, click the OK button.
While selecting the options for login auditing, there are four options available. This include:
- None : Neither successful nor failed logins will be audited.
- Failed logins only: Failed logins will be audited, but successful logins will be ignored.
- Successful logins only: Successful logins will be audited, but failed logins will be ignored.
- Both failed and successful logins: Login will be audited regardless of success and failure.
For this setting to take effect, we need to restart the SQL Server service as the server reads this setting only during startup.
How to view the results of the audit?
The results of the audit information are recorded in the SQL Server log. We can use the extended stored procedure in T-SQL, xp_readerrorlog to view the contents of the SQL Server log. To dump the results of the error log to a recordset, use:
EXEC xp_readerrorlog;
An integer parameter can be given to the extended stored procedure to read the older SQL server log. For instance, to see the contents of the 3rd log we would pass a parameter of 2 as shown below:
EXEC xp_readerrorlog 2;
Likewise, to search the current error log and only return failed logins we can use the following command:
EXEC sp_readerrorlog 0, 1, ‘Login failed’
Alternatively, with log management software, we could obtain these details from the Application event log for the operating system. To view the Application event log, expand System Tools, and then Event Viewer.
If you look in this event log, you will be looking for events with a source of MSSQLSERVER or MSSQL$<Instance Name>.
Successful logins for SQL Server 2005 and 2008 will have an event ID of 18454 and failed logins will have an event ID of 18456. SQL Server 2000 uses the same event ID for both, making it impossible to determine if the event signifies success or failure without looking at the event details.
Using SQL Server Trace
We can set up a trace from the same server or a different server to audit all the login attempts to SQL Server and save it safely in a file.
For this, follow the steps below:
- Open Profiler from SQL Server Management Studio, navigate to Tools and then to SQL Server Profiler.
- Alternatively, login to server All programs, navigate to SQL ServerPerformance Tools, and then to SQL Server Profiler.
- Connect to correct SQL Server instance and Change trace file name.
- Now select save to file and save it as an SQL table by selecting the save to table option.
- Select the location where you want to put your trace file and then Go to tab Event Selection at the top
- Now check the box Show all events
- Choose “Audit Login” and “Audit Login Failed” and once done click on run.
To set up this monitoring, there is no need to restart SQL Server Services. If you have logged into the server to start the trace make sure you do not log off or the trace will be stopped. Keep your login as an active login to the server.
Using SQL Server Auditing
SQL Server auditing is easy to set up and can enable it to audit login attempts from SQL Server 2008. The steps to enable it includes:
- Connect instance in management studio, under Security Dropdown, Select Audits.
- Now, right-click and select new Audit.
- Provide Audit name and select location where Audit files will be saved
- Now Click Ok to create Audit.
- Right-click newly created Audit and Select enable.
- Now, Right Click on Server Audit Specification and Select new Server Audit Specification
- Provide appropriate name and from Audit drop down select LoginAudit.
- Now fille the Audit Auction types as “Failed Login Group” and “Successful_Login_Group” and click ok.
- Finally, right Click on new created group and select enable.
To view the audit logs, right-click Login Audit that we enabled and select view Audit logs.
[Need any further assistance in fixing SQL errors? – We’re available 24*7]
Conclusion
In short, auditing failed logins in the SQL server at times is important to analyze the events that happened in the past. Today, we saw how our Support Engineers enable it.
0 Comments