Display all sessions under Database information

gerdhuebner

I'm running NAV 2009 R2 (Build 32012) classic client under SQL-2008 R2, simple security model, no traceflag.
I can't manage do display all sessions for a non-sysadmin sql-database login with traceflag 4616 set off (with traceflag set on, it works).
My database login TEST has got "VIEW SERVER STATE" permissions. (Each sql server login gets automatically VIEW SERVER STATE permission when the login is synchronized by the NAV client - in the simple security model this happens, when the database login is created within NAV). Furthermore the server public role has got "VIEW ANY DATABASE" permission by default (http://msdn.microsoft.com/en-us/library/ms189077.aspx). So the effective server permissions of TEST are "CONNECT", "VIEW SERVER STATE", "VIEW ANY DATABASE".

I recognized, that NAV client launches the following query:
SELECT COUNT(*), ... FROM [master].[dbo].[sysprocesses] within a SQL batch, in order to get and display the number of sessions. (sys.sysprocesses is a system-view within the master table)
When I do this query from SSMS, depending on the VIEW SERVER STATE permission granted or not to the user TEST and the EXECUTE AS LOGIN='TEST' activated, I get the expected result (1 session or more, respectively).
So it must have to do something with the application role $ndo$shadow under which the query is launched from NAV client.

Indeed, according to http://msdn.microsoft.com/en-us/library/ms190785.aspx, the only way to let an application role see server-level metadata (like sysprocesses) is either to activate traceflag 4616 or to use a certificate-signed stored procedure.
This seems to have been a fact already in SQL Server 2005. See http://support.microsoft.com/kb/906549/EN-US, e. g.
From that point of view the failure seems quite logical...

But according to http://blogs.msdn.com/b/nav/archive/2010/02/11/sql-server-trace-flag-4616-no-longer-required-for-dynamics-nav-2009-sp1.aspx?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+MicrosoftDynamicsNavTeamBlog+%28Microsoft+Dynamics+NAV+Team+Blog%29
trace flag 4616 is no longer required for NAV version 5.1 and above.

The only (logical) conclusion from these articles is for me that NAV client is using either a certificate-signed stored procedure in order to query the number of logins or that sp_unsetapprole is called to temporaliy disable the security context of $ndo$shadow and perform the query within the original server login context.
Indeed, if one takes a look at the things with SQL Profiler, the latter seems to be true.
But why is this not working in my constellation...- any ideas?

gerdhuebner

Well, the answer in my case is delivered via SQL Profiler.
Just analyze a TEST login into NAV and a subsequent call to File-Database-Information.
The results are:

....
declare @p5 varbinary(50)
set @p5=NULL
exec [sp_setapprole] '$ndo$shadow','FFFFFFCF41B20088D27236EBB7AC3FFFFF','none',1,@p5 output
-- activating the application role
-- @p5 now contains the cookie
....
exec [sp_$ndo$loginproc] N'Microsoft Dynamics NAV Classic client','6.0.32012.00'
....
exec [sp_unsetapprole] 0x01000000392AA04770745CF50C6FCE42A7DDF232BD732837826F99553DEB67B729230000
-- using the cookie to get back into the original server login context
....
SELECT HAS_PERMS_BY_NAME(null, null, 'VIEW SERVER STATE')
-- check if user who logs in has appropriate rights to count active sessions; terminate client if not.
....
SELECT COUNT(*), .... FROM [master].[dbo].[sysprocesses] ...
-- querying the number of sessions, yielding all NAV sessions in order to compare them to licensed sessions
....
exec [sp_setapprole] '$ndo$shadow','FFFFFFCF41B20088D27236EBB7AC3FFFFF','none',1,@p5 output
-- switching back to application role security context
....
....
-- now File-Database-Information action is performed
SELECT COUNT(*), .... FROM [master].[dbo].[sysprocesses] ...
-- performing the query again within the restricted security context, yielding exactly 1 for display of sessions

Conclusion:
In order to check the number of active sessions against licensed sessions, the NAV client switches back to server context for every user trying to log in.
But the number of active sessions is only displayed correctly if either the user has got sysadmin rights or traceflag 4616 is set on.
So it's not a bug, but a feature.
(see also http://www.comporsys.de/dynamics-nav-blog/der-nav-client-zeigt-in-der-uebersicht-der-aktiven-sessions-nur-die-eigene-session-an/)

navision2009R2

I concur. Even SUPER (NAV role) users cannot see others users' sessions, unless i set their logins as sysadmin in SSMS