Handle sql-server permissions gracefully with Navision

Question

Background

I am in the process of creating an application (referred to as MyApp) which must read data out from a SQL Server database handled by Navision. Navision users should to be able to use my application, without modifying permissions in the database.

Navision's handling of permissions seems to be on the application layer. It performs the checking of permissions without storing them in the database.

Problem

Navision overwrites users, permissions, and other related objects in the database when synchronizing with the database, so the normal approach of creating a DB user and just using that won't work.

Possible Solution

What I think would be the most appropriate solution is to create a MyApp role in Active-Directory, which grants the necessary permissions on the DB, and add this role to all users.

I do not know how to do this, or even if it's possible. Other solutions, or proposals, are welcome, but please only suggest solutions with can be managed from within ActiveDirectory or Navision.

The server is an SQL Server 2008 server running Navison 5, and the client is Navision 6. I'm using Active Directory for Windows Server 2K8.

EDIT:

My app is a crate creating and designing application. It needs to read out the customers' names and IDs, and a few items in the items table, and that is why I need this functionality

Answer 1

In general, skipping the NAV layer and reading/writing directly to the DB is not recommended at all as you're bypassing all of NAVs business logic which is stored in the table and report objects in NAV.

What does your app intend to do (broad strokes if you can't get specific) and would using a NAV add-in or dataport be feasible?

Answer 2

If you use the enhanced security model in NAV, user permissions are synchronized to SQL Server. However, these SQL permissions are mapped to an app role in SQL Server, rather than the user's login. If you use the standard security model, all users map to a single SQL app role which is a super user (less secure).

If you want to access the data in SQL Server using the NAV security model (i.e. through the SQL App roles NAV creates), you should use the CFront API (installed via the SDK option). If you are using NAV 2009 web services are also an option.

If you want to access SQL Server directly, then you will have to manage the permissions yourself using SQL Server. If you create a SQL script to grant permission it is easy to restore anything which NAV might delete during Synchronize Logins.

You can't grant SQL permission from Active Directory exactly as you described. Instead you must map Active Directory groups to either SQL Server logins or NAV Windows Logins (depending on whether you decide to access SQL directly or go through a supported NAV API). Note: the permissions associated with the role are managed in SQL or NAV respectively; not in AD.

From an administration perspective, you can simply add and remove users from this Active Directory group. If you use the NAV enhanced security model each user in the AD group must also have an entry in Windows Logins, and whenever you make changes you must Synchronize Logins. This slight inconvenience is a hangover from the native database.