Bobcares

Auditing Failed Logins in SQL Server

by | Sep 8, 2020

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:

  1. First, connect to the SQL Server in Object Explorer.
  2. Then, Right click on the SQL Server.
  3. Choose the Properties option from the pop-up menu.
  4. Click on the Security page which will bring you to where you can set the login auditing
  5. 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:

  1. Open Profiler from SQL Server Management Studio, navigate to Tools and then to SQL Server Profiler.
  2. Alternatively, login to server All programs, navigate to SQL ServerPerformance Tools, and then to SQL Server Profiler.
  3. Connect to correct SQL Server instance and Change trace file name.
  4. Now select save to file and save it as an SQL table by selecting the save to table option.
  5. Select the location where you want to put your trace file and then Go to tab Event Selection at the top
  6. Now check the box Show all events
  7. 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:

  1. Connect instance in management studio, under Security Dropdown, Select Audits.
  2. Now, right-click and select new Audit.
  3. Provide Audit name and select location where Audit files will be saved
  4. Now Click Ok to create Audit.
  5. Right-click newly created Audit and Select enable.
  6. Now, Right Click on Server Audit Specification and Select new Server Audit Specification
  7. Provide appropriate name and from Audit drop down select LoginAudit.
  8. Now fille the Audit Auction types as “Failed Login Group” and “Successful_Login_Group” and  click ok.
  9. 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.

 

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

var google_conversion_label = "owonCMyG5nEQ0aD71QM";

0 Comments

Submit a Comment

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

Never again lose customers to poor
server speed! Let us help you.

Privacy Preference Center

Necessary

Necessary cookies help make a website usable by enabling basic functions like page navigation and access to secure areas of the website. The website cannot function properly without these cookies.

PHPSESSID - Preserves user session state across page requests.

gdpr[consent_types] - Used to store user consents.

gdpr[allowed_cookies] - Used to store user allowed cookies.

PHPSESSID, gdpr[consent_types], gdpr[allowed_cookies]
PHPSESSID
WHMCSpKDlPzh2chML

Statistics

Statistic cookies help website owners to understand how visitors interact with websites by collecting and reporting information anonymously.

_ga - Preserves user session state across page requests.

_gat - Used by Google Analytics to throttle request rate

_gid - Registers a unique ID that is used to generate statistical data on how you use the website.

smartlookCookie - Used to collect user device and location information of the site visitors to improve the websites User Experience.

_ga, _gat, _gid
_ga, _gat, _gid
smartlookCookie
_clck, _clsk, CLID, ANONCHK, MR, MUID, SM

Marketing

Marketing cookies are used to track visitors across websites. The intention is to display ads that are relevant and engaging for the individual user and thereby more valuable for publishers and third party advertisers.

IDE - Used by Google DoubleClick to register and report the website user's actions after viewing or clicking one of the advertiser's ads with the purpose of measuring the efficacy of an ad and to present targeted ads to the user.

test_cookie - Used to check if the user's browser supports cookies.

1P_JAR - Google cookie. These cookies are used to collect website statistics and track conversion rates.

NID - Registers a unique ID that identifies a returning user's device. The ID is used for serving ads that are most relevant to the user.

DV - Google ad personalisation

_reb2bgeo - The visitor's geographical location

_reb2bloaded - Whether or not the script loaded for the visitor

_reb2bref - The referring URL for the visit

_reb2bsessionID - The visitor's RB2B session ID

_reb2buid - The visitor's RB2B user ID

IDE, test_cookie, 1P_JAR, NID, DV, NID
IDE, test_cookie
1P_JAR, NID, DV
NID
hblid
_reb2bgeo, _reb2bloaded, _reb2bref, _reb2bsessionID, _reb2buid

Security

These are essential site cookies, used by the google reCAPTCHA. These cookies use an unique identifier to verify if a visitor is human or a bot.

SID, APISID, HSID, NID, PREF
SID, APISID, HSID, NID, PREF