Navision & SQL server...

Can anyone tell me how Navision accesses SQL server?

I have this problem where Navision has to access beside our Navision database also another database which is linked to Navision database.

So when i try to access (in Navision) tables from the other DB i get this error message:Server user ‘prosen’ is not a valid user in database ‘otherDB’. This message is displayed until i grant user System Administrator role on SQL server. If anyone has any ideas how to solve this, please help.

lp prosen

Navision use Application Permissions to access SQL DB (the user only needs Access right). I think you need to give user “prosen” the permissions you want on “OtherDB”

  • Access to DB (this is needed to access OtherDB)
  • Select/Insert/… (or make it a member of a Group with those rights)
    Hope this helps.

It sounds like from a SQL Server database, you’re accessing a Navision database via ‘Linked Servers’, is that correct? If this is what you’re on about, access of SQL Server is by way of ODBC or OLE DB. [:)]

Connull, i got the error message from ODBC SQL server driver, do you have any ideas how to fix this?

Things are a bit simpler if you have the following scenario: 1. The table(s) to be accessed are in another database but on the same server as main database that you are connecting to, 2. Its OK, security wise, that the user(s) accessing the data can be database owners (members of the db_owner database role) in the main database you are connecting to. (Need not be sysadmins). If Yes for both, then just make your user(s) a member of db_owner in the main database. They don’t need to be db_owners in the second database but need appropriate permissions to the tables being accessed. If not Yes for both, you need to set up a linked server - you can do it with Enterprise Manager. You need to do this even if the database is on the same server, but you would like a weak non-dbowner user to access it. You of course need to do it to access a different server. Security is set up for the linked server, and you give the appropriate user permissions there, to the appropriate logins/users in the linked server and database. In all cases you need to create a view in your main database that selects from the table(s) in your second database or linked server, using either a 3-part or 4-part name as appropriate. You make an object in Navision with the same name and the LinkedObject property set to Yes. If you going through a linked server you additionaly set the LinkedInTransaction property to No. This is also described in a section of the Application Designers Guide. It sounds complicated but really isn’t - needing only a few clicks.

Robert, i made the views on SQL server, however i’m not sure how to make these objects in Navision and where do i set the properties. lp prosen

Me again, i managed to make the nessesary adjustements that Robert suggested however the damn thing still wont work :frowning: anyway thanx for all the help. lp prosen

I think that Navision uses Application Roles when connecting to the DB in an SQL Server. What this means is that once the user logs into Navision, Navision ‘applies’ the application role. When this happens the entire connection to the SQL Server inherits the security settings from the Application Role, ignoring whatever permissions you may have assigned to the user login itself. To correct this make sure that you give the Application Role permission to access the other tables. The name of the application role is ‘$ndo$shadow’ (without the quotes).

Hi Robert,

We are trying to do just this, i.e. create a view through which a Navision user can access data in a different DB. This all works as long as he is db_owner in the Navision DB, but when he is not it fails with a permissions error. We cannot allow all our users to be db_owner (170 people who would alll be able to back up, delete etc. as much as they would like once they create an ODBC connection would cause me sleepless nights) and have tried your suggestion of a linked server, but it appears you cannot create a linked server pointing to itself in SQL Server 2005, we get the message ¨You cannot create a local SQL Server as a linked server¨. The DB we are trying to access is on the same server box. Any ideas what we can do now or are doing wrong?

Many thanks

Meint

Hmm, got it working now by using the IP address to create the linked server, now it is now longer complaining about creating a linked server to itself. Created new user with appropriate permissions, supplied the linked server with the new credentials, created a view inside the Navision database to look at the table in the linked server, assigned the application role the appropriate permissions on the view and we are in business. Not sure about the performance impact of all of this, but it does not work without having to give the user db_owner access to the Navision database. Thanks to Robert for the hint on using a linked server.

You really should think twice about giving users db_owner access to your Navision database though, it means any user with a modicum of SQL knowledge can do anything to your data at the SQL level, including backing it up, dropping the DB, inserting incorrect data etc.

Meint

Grant the Guest account access rights for the other database. When using application roles access to remote objects is thru the Guest account.

Hi,

That would be by far the easiest option, however, I get the dreaded: ¨The combination of User ID and password entered is invalid¨. From all the research I have done so far it appears a linked server is the only way to go, but if anybody can give me any hints to get round this and use the guest account I am more than willing to give it a go.

Regards

Meint

Granting db_owner role is NEVER a valid solution for solving end-user access permissions. This grants the user unlimited access to the database including the right to change object definitions and also other users permissions.

BTW, I tried the suggestion of using the Guest account to the external database on SQL Server 2000 and it works fine. Anybody know for sure why this behaviour is different on 2005? The linked server approach was the only one I could get to work under 2005. And yes, giving db_owner to your users is a really bad idea unless you are really small and really 100% trust you users.

Regards

Meint