SQL SERVER – Strange SQL Transaction called UpdateLoginStats

I was preparing a demo for my upcoming presentation which is about couple of weeks ahead. While doing a stress test, I was looking at profiler and found that for every login there is an additional SQL transaction which is having ObjectName as UpdateLoginStats appearing even before Audit Login.

SQL SERVER - Strange SQL Transaction called UpdateLoginStats LoginStats-01

Initially I thought that it might be some kind of Logon Trigger which I might have created. But my further search revealed that there was no such trigger which I defined.

That made me curious and I was tracking back, what configuration I have changed. Here is my own blog which I referred:

SQL SERVER – SSMS: Configuration Changes History

SQL SERVER - Strange SQL Transaction called UpdateLoginStats LoginStats-02

That helped me in recalling that I have enabled feature called “Common Criteria Compliance”. Here is the query which I executed to enable it.

Solarwinds

EXEC sys.sp_configure N'common criteria compliance enabled', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Same can be done from UI also.

SQL SERVER - Strange SQL Transaction called UpdateLoginStats LoginStats-03

I searched for and was trying to understand what is UpdateLoginStats and what is does? Here is what I learned:

When this option is turned ON, SQL server will update login statistics for every login request. As per books online, there are few additional security logging is enabled with this option is turned on. These login statistics can be viewed by querying the sys.dm_exec_sessions dynamic management view. If we run below query:

SELECT session_id
,original_login_name
,last_successful_logon
,last_unsuccessful_logon
,unsuccessful_logons
FROM  sys.dm_exec_sessions
WHERE is_user_process = 1

SQL SERVER - Strange SQL Transaction called UpdateLoginStats LoginStats-04

Later I turned off the option using below

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'common criteria compliance enabled', 0;
GO
RECONFIGURE;
GO

Then I capture profiler again and as expected, no additional transaction before login.

SQL SERVER - Strange SQL Transaction called UpdateLoginStats LoginStats-05

And all those columns were NULL.

SQL SERVER - Strange SQL Transaction called UpdateLoginStats LoginStats-06

Sometimes you have to break the things to assemble it and learn. If you look at screen shot again, there is another transaction called CSECAuthMedAccess::InitReadWrite and I am not able to figure out what that means.

How many of you have ever encountered this? Would be great to learn from you if you figured out the internals of this.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER – To Find Events Mapped to Channels in Extended Events
Next Post
SQL SERVER – Looking Forward to Meeting you at SQLPASS 2015

Related Posts

No results found

1 Comment. Leave new

Leave a Reply

Menu