In my SQL Server 2016 server,i set up a user logins Audit for Login successful and Login Failed action. Here is the script.
USE [master]
GO
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20190404-091348]
FOR SERVER AUDIT [Audit_SQL_Logins]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO
USE [master]
GO
CREATE SERVER AUDIT [Audit_SQL_Logins]
TO FILE
( FILEPATH = N'D:\SQL_Logins_Audit\'
,MAXSIZE = 20 MB
,MAX_FILES = 50
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = '77795756-1928-4ce4-85f5-e3ad2e3e22e5'
)
WHERE ([server_principal_name]<>'name1' AND [server_principal_name]<>'name2' AND ([server_principal_name]<>'name3' AND [server_principal_name]<>'name4') AND ([server_principal_name]<>'name5' AND [server_principal_name]<>'name6'))
ALTER SERVER AUDIT [Audit_SQL_Logins] WITH (STATE = ON)
GO
In the Audit Logs,
I can see lots of failures with Server Principal SID as NULL and name as empty string.Earlier when i set up the login i didn't give any filters and that time the Audit logs showed only success and there were no failures.
I don't understand what is getting failed here since the log shows NULL .
Here is the screenshot. This is a production database server and users are logging in from the .Net Web application. I see the message "Network error code occurred while establishing a connection;the connection has been closed."