1

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 .

Additional Info: enter image description here

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."

2

I created the exact same audit and couldn't reproduce the problem you describe. When I log in with a login that doesn't actually exist:

enter image description here

And for an intentionally wrong password:

enter image description here

I even tried a name with a huge number of leading spaces and tabs, just to see if the name would get truncated and look blank, but leading whitespace gets trimmed.

Of course I would expect SID to be null in the first case, because what SID would you want to show for a login that doesn't exist (and therefore can't have a SID)? And for the second case, perhaps because a login couldn't authenticate, a match to SID wasn't made?

You'll have to provide more info to determine why server principal name isn't being populated. What is the application name? What does the statement say? What is the client (IP address in the statement)? Can you ask that person what they're doing?

  • I see the message "Network error code occurred while establishing a connection;the connection has been closed."So may be connection has not been established and that is why i see NULL entry and empty string name.But there are no reports of users not being able to logging in to the web application.Wonder why this is happening. – user9516827 Apr 4 at 16:50
  • 1
    Sounds like when a login can't be attempted because of a connection failure, yeah, you need to do more digging into that as opposed to worrying about the login name. Just because nobody is complaining doesn't mean it isn't happening. Could be an asynchronous process or a rogue user or a code fragment left behind or any number of things that wouldn't affect users or that they wouldn't notice happening. – Aaron Bertrand Apr 4 at 17:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.