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, 172.16.23.120 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.