Navision allows more user than license permits

tro#1

Do you have any clue?!?

Version NA 4.00 SP1 (4.0 SP2)
Sequal Server 2005 (9.0.2047)

Navision license permits 26 user. So usually 28 user (26 + 2 extra ones) can log onto Navision.

But in this case I was the 37th user which logged on. I checked:
File/ Database/ Information
Current Sessions: 37
Licensed Sessions: 28

I logged on with customer license - not with developer license.

What I noticed so far:
At Sequal Server:
User A has got Server Role: "sysadmin"
User B does not have Server Role: "sysadmin" - only User mapping: "public"

User A cannot log onto database: message that license does not allow more user

User B can log onto database - even if there have already more user than allowed accessed the database.

And user B works with customer license.

Just to let you know: It is one database with two companies. But I don`t know what this could have to do with it.

Thanks for any ideas

ara3n

Are the users all using Navision as client or other applications?

This looks like a big hole that clients can take advantage of.
I bet a lot of clients would want to move to sql 2005 just for this reason.

ara3n

OMG I just tried this on one of our clients db they have 77 licenses on sql 2005. There were 65 people logged in. They are using 4.0 sp3.
I opened clients to reach 77 users.
Tried to log in as sa and got the license error.
Tried to log in as regular user and got in. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Savatage

WOW they could have saved on 70+ licenses 8)

:-$

kine

Yes, this is problem of permissions. If you open the Session table as common user, you will see just your session. Nav than have no way how to calc existing sessions correctly. If you login as user with enough rights, you see all sessions and NAV will not let you in... :-)

To "prevent" that you need to do "GRANT VIEW SERVER STATE TO <login name> " to grant access for al users to server state table.

ara3n

why would a client do this? They can have 200 on the system for free.

David_Singleton

ara3n wrote:

why would a client do this? They can have 200 on the system for free.

Well because otherwise they would get something for free, and I don't think customers would want that :whistle:

kine

Will be patched in Update 1 for SP3 this month... :-)

David_Singleton

kine wrote:

Will be patched in Update 1 for SP3 this month... :-)

Hmm well this is an all MVP thread, so it looks like MSFT got their money's worth from us.

Alex_Chow

The original poster is not MVP.... 8)

David_Singleton

deadlizard wrote:

The original poster is not MVP.... 8)

I realize that, but MVPs don't ask questions, we solve them. By the way there was a level of sarcasm/humour in my post :whistle:

Alex_Chow

Sarcasms are hard to detect by reading typed letters.

Nonetheless, I think the original poster deserves an MVP by discovering this bug? I'm sure the end users would definately vote him MVP.

Savatage

kine wrote:

Will be patched in Update 1 for SP3 this month... :-)

what else does SP3 do? cause it better worth it!!!!!!!!!!!
else no thanks!

ara3n

My guess it will be able to run in vista.

Luc_VanDyck

David Singleton wrote:

I realize that, but MVPs don't ask questions, we solve them. By the way there was a level of sarcasm/humour in my post :whistle:

And you did it again ... I mean the level of humour in your post.
I have posted some questions lately ... and I'm an MVP ... oeps :oops:

David_Singleton

I'll add smilies next time. I just think this was a very funny thread.

kine

Actual status: NAV 5.0 needs to have trace flag 4616 enabled - description:

Makes server-level metadata visible to application roles. In SQL Server 2005, an application role cannot access metadata outside its own database because application roles are not associated with a server-level principal. This is a change of behavior from earlier versions of SQL Server. Setting this global flag disables the new restrictions, and allows for application roles to access server-level metadata.

It seems that this will disable the feature which prevents common users to see other sessions...

gedas

Hi,

Trace flag 4616 could be enabled now on any SQL 2005
DBCC TRACEON(4616,-1)
This will solve problems.

As Kine mentioned - this trace flag will be required by NAV.

Scott_Frappier

As part of your SQL Server installation you should probably do the following:

01.) Open the Start Menu
02.) Go to Microsoft SQL Server 2005 Group
03.) Go to Configuration Tools Group
04.) Click on SQL Server Configuration Manager
05.) Click on SQL Server 2005 Services
06.) Right-click on SQL Server ($INSTANCENAME) and click on Properties
07.) Click on the Advanced tab
08.) Go to Startup Parameters
09.) Enter the string so that it looks as follows (notice the end is the only change):
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-T 4616
10.) Click OK
11.) Restart the SQL Server service

PureHeart

kine wrote:

Yes, this is problem of permissions. If you open the Session table as common user, you will see just your session. Nav than have no way how to calc existing sessions correctly. If you login as user with enough rights, you see all sessions and NAV will not let you in... :-)

To "prevent" that you need to do "GRANT VIEW SERVER STATE TO <login name> " to grant access for al users to server state table.

Kane Can you please explain me how to use that command? ](*,) ](*,)
I've tried to use it but it returns me always an error! ](*,) ](*,)

Scott_Frappier

Pureheart:

You should just need to add the 4616 trace flag instead of granting permissions to viewing the server state. This is the method that Microsoft is recommending for users w/ 4.0 SP3+.

Waldo

Scott Frappier wrote:

As part of your SQL Server installation you should probably do the following:

01.) Open the Start Menu
02.) Go to Microsoft SQL Server 2005 Group
03.) Go to Configuration Tools Group
04.) Click on SQL Server Configuration Manager
05.) Click on SQL Server 2005 Services
06.) Right-click on SQL Server ($INSTANCENAME) and click on Properties
07.) Click on the Advanced tab
08.) Go to Startup Parameters
09.) Enter the string so that it looks as follows (notice the end is the only change):
-dC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf;-T 4616
10.) Click OK
11.) Restart the SQL Server service

I noticed this is the best way to go.
When connecting remotely (via Management Studio) to SQL Server and setting the traceflag with command, didn't do the job for me, only locally on my laptop it did ](*,) .

When doing it locally with the Configuration Manager, it worked.

:-k

kine

Waldo wrote:

When connecting remotely (via Management Studio) to SQL Server and setting the traceflag with command, didn't do the job for me, only locally on my laptop it did ](*,) .

When doing it locally with the Configuration Manager, it worked.

:-k

To set the flag globally, you need to use DBCC TRACEON (4616,-1)...

(the second parameter is what you need... :-) but of course after restart the flag will be lost, if it is not set in the startup parameters...

Waldo

Then that's probably the case.

So what you're saying, if you do DBCC TRACEON, that "startupparameters" are not affected ... then going through the "configuration manager" really is the best way to go

kine

Yes, and it is the way how it is written in documentation - add it into runtime parameters

Waldo

kine wrote:

Yes, and it is the way how it is written in documentation - add it into runtime parameters

I get the "inside message"

mplatvoet

About this trace flag...

The first thing is that Microsoft says the following (in general) about trace flags:

"If you enable traceflag 4616, SQL Server turns off catalog permission checks completely for application roles. This solution is recommended for upgrading old applications to the new version, but probably leaves your master database more exposed than you might like, as it applies to all tables in the master database. It should be used as a temporary solution only."

My conclusion is that, if SQL Server is only used with NAV, it should not be a problem to set this flag. But if you have more applications that have a different security system, users might be able to check for other application roles than the one they were assigned by the system.

What do you think about this?

Regards
Mike.

kine

MS Recommend to have dedicated server for NAV. If you have more applications using the server, it is on your own responsibility...

johanna

Scott Frappier wrote:

Pureheart:

You should just need to add the 4616 trace flag instead of granting permissions to viewing the server state. This is the method that Microsoft is recommending for users w/ 4.0 SP3+.

Hi Scott,

I have the same problem about the number of the sessions is also exceed the number of users permitted in the license although I have set the trace flag -T4616 in the SQL Server Configuration Manager. I'm using NAV 4 SP3 with SQL Server 2005. There are many of same connection id, user id, login type, idle time (blank), login date, login time, database name, host name, cpu time, memory usage, physical IO, wait time, blocking connection (other connection id), blocking host name in the NAV session.
Why could this happen? Because if we are open 2 NAV, we will get different connection ID.
Please help me. Thank you.