SQL error when new user wants to connect

Hey guys,

we just created a new user on Navision , now when we try to connect with it we get this error, user was created first on the SQL server, then created in Navision and logins were synchronized afterwards… quite straight forward as always…

3857.sql issue.JPG

You need to set traceflag 4616 in sql server…

you can google with set trace flag 4616 for help…

here is one ex

http://wiki.dynamicsbook.com/index.php?title=Trace_flag_4616

Thanks, I will try it tonight in our Maintenance window and let you know (and verifiy solution in case it was).

Cheers

Wassim

Well, you don’t have to wait for y “Maintenance Window” - you could enable the TF even during runtime:

DBCC TRACEON (4616, -1);
GO

If this does not work, you have to grant this permission manually:

GRANT VIEW SERVER STATE TO PUBLIC

but is this normal to happen with only 1 user? my other users do not have a problem… shouldn’t be a general problem if it was generating from the server config?

Well, NAV requires - for its authentication process - to read some “meta” data from the SQL Server which is not publically available. Therefor the older NAV versions needed the TF 4616 aktive, as this “makes the SQL Server to publish” this required information. But actually this is/was a (little?) security risk.

The new NAV versions don’t need 4616 anymrore, but they need the server right VIEW SERVER STATE. This permission needs to be granted to all “public” users so that they could authenticate.

If you are an admin or developer you probably will have more rights (e.g. db_owner or maybe sysadmin) than ordinary users (public). So if necessary just GRANT VIEW SERVER STATE TO PUBLIC - and of course it would be the wrong way to turn public users into db_owner or sysadmin!

Hi Wassim This error normally occurs due to issues that occur while creating Login ID in SQL Server.

  1. Open SQL Server Managment Studio.
  2. Click on New Query.
  3. Copy & Paste Query given Below.
  4. Replace USERID with the User ID for which the error is coming.
  5. Select the Whole Query.
  6. Press F5 to execute

QUERY -

USE [master]
GO
GRANT VIEW SERVER STATE TO [USERID]
GO