MS SQL Server Security And Navision Security

jorgebraga

Can anyone tell me how do Navision and SQL Server Security Integrate?

My problem is the following:

I'm using Navision 3.70 over an SQL Database.
I'm using SQL Server users (and not Active Directory).
I have an SQL Server user in Navision.
I set Groups and permissions for this user in Navision.

I go to the SQL Server and none of this Groups or Permission seem to be there.

The refered user cannot do what it does from Navision using a simple ODBC connection directly to MS SQL Server.

e.g. The user in Navision can access the Client Table . Using SQL server connection it can not do anything on that table.

What am i doing wrong in SQL Server? If i'm not doing anything wrong how the heck can Navision overcome this problem?

How do i access the data with the same security rules as those defined in Navision?

Do i have to replicate Navision security (Groups and Permissions) in SQL server?

Txs in advance for your time.

DenSter

You need to add your database users to SQL Server with SQL Server authentication, and give them user rights to the Navision database. Then, in Navision, you add those users to the security (Tools, Security, Database Logins). Then you click the roles button and add the correct roles to the user.

The permissions in Navision will not appear anywhere in SQL Server, they are database driven permission within Navision's proprietary security model. Access to the database goes through an application role on SQL Server, and uses the authentication setup in your database users.

Depending on whether your users will need to import objects or be able to alter the design, you will give them more rights (like db_owner on the Navision database, or db_owner to master if they need to upload licenses).

jorgebraga

Daniel

Txs for the very useful help.

So what u are saying is that Navision implements an aplication role over SQL Server and so its own security model. Therefore there is no direct way (from Navision) to replicate the security model to SQL Server, and so have the same permissions for a given user (or group).

Is there a way to bypass this behaviour ?

Are there any tools that can do this directly?

Where are the groups and permissions stored then?

Txs in advance for your time.

DenSter

Navision is built out of objects (tables, forms, codeunits, etc) and those are stored in the same database that the data resides in. The security model has roles with permissions to certain objects, also kept in tables inside the database. Then you assign roles to users, which is also kept in the database. You don't see all these tables in the object designer, because some of them are system tables, or virtual tables. The data however is really there, it's just encrypted probably, so you can't just get to it from SQL (although I have not tried this myself).

It is not necessary to replicate permissions into SQL, because the Navision app doesn't use those. All Navision cares about is that the user is authenticated inside SQL (by way of SQL users or domain users) and the role/user setup inside the Navision database takes care of permissions.

So the sequence is:
1: create a sql database user (you don't have to set database access)
2: open Navision and add the same user name as a database login. The system will synchronize this user, so when you look at the SQL user after inserting it into Navision, you will see that database access has been added to the user.
3: you assign a role to the user.

Of course this is assuming that you have roles and permissions already set up.

There's a document on your product cd, in the doc folder, that explains all this. I don't mean to say rtfm, but it has a really elaborate section on security.

I'm afraid that if you have created roles/groups/permissions inside SQL Server that you have wasted all that time. I am sure you could write a script to save the data and import it into Navision (you do NOT want to start writing into Navision SQL tables directly, trust me), but I'd have to analyze that some more.

jorgebraga

Once again txs 4 the help.

But i think i'm not explaining myself correctly.

The problem (and maybe i should have started by this) is that i want to use Navision Data in an external program. and still use Navision security.

For instance i want to build an Excel sheet that reads Navision data directly from SQL Server.

U seem an experienced user and probably u advise to create an OLEDB or ODBC connection to the SQL Server and then fetch the data. The problem is that i want to do this but still use the security definitions i set in Navision (and not those present in SQL Server that are just public on the database like u said). The problem is that these definitions do not exist (or are visible) inside the SQL Server and so i cannot define the same restrictions as i did in Navision. So if i set a user to play the role of data_reader inside the database that hosts Navision Data (with all the company tables ) i need to define permissions and replicate the work i've already done inside Navision.

Please keep in mind that my Navision works fine for all my users, i can create users, set roles, set permissions, etc. I rtfm already. Quickly but i did.

I just want to use the data directly from SQL Server but keeping security defined in Navision.



Txs in advance for all your help.

itspeter

Navision is using Application Role. Read this http://www.informit.com/library/content.asp?b=STY_Sql_Server_7&seqNum=51&rl=1.

If you want to access from other application, you need to activate the role in your application. However, the easiest way is to grant permission in Sql Server.

And, the fastest way is to grant dbowner. :roll:

DenSter

I don't know how to accomplish that. And I would never tell anybody to establish an ODBC connection to the Navision database (wouldn't even know how ).

Have you considered approaching it from the other side though, and create an excel export from Navision, like a push instead of a pull. That way you have all the permissions right there, and you don't have to worry about keys and flowfield values and option values and things like that.

jorgebraga

Txs to all.

I've learned a lot from u guys.

1 - Navision talks to SQL Server using App Roles (not using server login permissions)
2 - Navision 1st sets this Role when it is installed
3 - The app role that Navision uses is called $ndo$shadow (it seems to be that one)
4 - App Role was a new feature in SQL Server 7 (i'm a bit out of date on that one!!! :oops: )



My question now is "What is the password?"

Awnsering Daniel:

Yes Pull is better than Push - but pull requires aditional Licenses - and licenses cost money. Push requires SQL Server Users - and i already have that.

I dont need to use all of Navision Tables - just a set i know well.

There is a user profile in my Navision Implementation that just checks a few items. But they do it all at the same time.

At the same time i need Excel and Word Reports on a frequent basis. Same issue.

Txs once again for all your BIG help.

DenSter

I have nothing against people using their SQL skill to take optimal advantage of the technology, as long as you realize that when Navision writes to the SQL tables, there is a heck of lot of SIFT indexing and other stuff going on in the background that you have to replicate if you want to write directly to the Navision SQL tables.

I can't help you with the password thing, sorry

As far as the licenses... I thought the people that want to use this feature already have users and roles set up in Navision, wasn't that your initial question? So if they already have logins to Navision, then it won't cost extra money. Navisin works with concurrent users anyway, so if they log in, do their thing and log out it should take as many sessions.