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.

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

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

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

Same can be done from UI also.

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

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.

And all those columns were NULL.

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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.