A couple of days ago I was playing with my small SQL Server enviroment (test) and auditting login events.

I went ahead and created the audit for FAILED_LOGIN_GROUP and SUCCESSFULL_LOGIN_GROUP (let it run for a while).

USE [master]
GO
CREATE SERVER AUDIT [Audit_LoginEvents]
TO FILE 
(
 FILEPATH = N'<PathToStoreData>'
,MAXSIZE = 100 MB
,MAX_FILES = 10
,RESERVE_DISK_SPACE = OFF
)
WITH
(
 QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit_LoginEvents] 
WITH (STATE = ON)
GO

USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [FailedLogins]
FOR SERVER AUDIT [Audit_LoginEvents]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO

Now that the audit has some data we can collect it and see whats going on, I created for myself a procedure to retrieve all rows from the .sqlaudit files and store them into a table.

yeah I decided to get everything insede a new DB but this could be changed to #table or a table on master.

CREATE DATABASE LoginEvents
GO
USE LoginEvents
GO
--DROP TABLE LoginEvent
--TRUNCATE TABLE LoginEvent
CREATE TABLE LoginEvent
( 
  Id int IDENTITY PRIMARY KEY,
  SourceFile nvarchar(200) NOT NULL,
  LoginName nvarchar(100) NOT NULL,
  Succeded bit,
  127.0.0.1 varchar(20),
  EventDate datetime,
  AplicationName varchar(100),
  HostName varchar(100),
  DatabaseName varchar(100),
  ErrorMsg nvarchar(250)
)
GO

--DROP PROCEDURE ReadAudits
CREATE PROCEDURE ReadAudits @Path nvarchar(200)
AS
BEGIN
CREATE TABLE #Files_Pre
(
Subdirectory nvarchar(200),
depth int,
isFile int
)
CREATE TABLE #Files_End
(
FullPath nvarchar(200),
FileName nvarchar(200),
)
INSERT INTO #Files_Pre
EXEC master.sys.xp_dirtree @Path,0,1
DELETE FROM #Files_Pre WHERE Subdirectory NOT LIKE '%.sqlaudit'
INSERT INTO #Files_End
SELECT @Path+Subdirectory AS FullPath, Subdirectory AS FileName FROM #Files_Pre WHERE isFile=1
INSERT INTO LoginEvents.dbo.LoginEvent
SELECT f.FileName,
           fnA.server_principal_name AS LoginName,
   fnA.succeeded,
   fnA.client_ip AS IP,
   fnA.event_time AS Date,
   fnA.application_name,
   fnA.host_name,
   fnA.database_name,
   'Failed_msg' = CASE
   WHEN fnA.succeeded = 'True' THEN 'Login successful'
   WHEN fnA.succeeded = 'False' THEN SUBSTRING(fnA.statement,0,250)
   ELSE 'UNKNOWN'
   END
FROM #Files_End f 
CROSS APPLY sys.fn_get_audit_file(f.FullPath,default,default) fnA
WHERE fnA.class_type='LX'
END

Runnig this procedure will populate the table.

now that we have the data we can start checking whats there.

In a server with almost no activity we can see 14 failed logins from ssms (which was me), 125 connections from the SQLserverCEIP.

You can check the errors from the failed connections.

the IP and the number of connections made from them.

which logins are being used and from where

If you are having problems with failed logins this may help you to find whats happening there.