Access from NAV to a SQL view in different DB not allowed

bastir

Hi,

I've created a simple sql view which is selecting date from a table in another database on same server.
Furthermore I've enabled this view in NAV, that user can read the data in a form/page.

But everytime a user tries to open this form/view he gets an Login-failure: "User ID and password are invalid"
Our users have public "right" on sql.

I've given them dbo rights on the other database but it didn't help.
When I try to access the data via Excel, everything works great.

It seems to me, that the NAV application role prevents me from accessing the external view.
Can anybody point me in the right direction how to solve the problem.

Thanks
Sebastian

Lars_Westman

Unfortunately You either have to enable the guest account and give that account access to You NAV db or setup the users that need to access the linke table as db_owner in the NAV db (along withpermissions in the database the view reads. I would prefer the latter since You still set the permissions inside NAV that restricts what the user can do inside NAV. However if the users gets hold of SQL Management Studio or any other tool and connects to the db they can do stuff You wouldn't like. But at least in this scenario You know who the user is. Only You can decide what suits your situation.

http://msdn.microsoft.com/en-us/library/dd355092.aspx

bastir

Hi Lars,

thanks for your reply.
Unfortunatly our users can use tools like SQL Mgt. Studio or even hav Excel installed (it is really to easy to use excel to access the data if you are db_owner)

But I also cannot move the tables from the other database to the NAV database.

Could you please explain me a little bit more the solution of the guest account.
Where do I've to enable the account.

Is it not possible to access the remote database from NAV with a user, that has db_owner rights in the target database.
Which user/role is logging in to the other database ndo$shadow or another user/role?

Thanks in advance
Sebastian

ndbcs

As long as the user is not db_owner NAV uses a application role ($ndo$shadow in standard security model) to access the NAV db.
The application role is only known in the NAV db. Imho there are no chances to access an other db with an application role.

In that situation I always try to implement a solution where only a NAS needs to access the other DB and give the NAS account db_owner role.

Another alternative is to use a linked server. You can store credentials in the linked server configuration.
Your view uses the linked server to access the other db. So the view uses the credentials of the linked server.
I really don't like that solution, but it works ...

The last alternative I'm aware of, is to implement some kind of replication.

It depends on your needs, what the best solution is.

Tobias

bastir

Hi Tobias,

thanks for the detailed answer, I also believed that the ndo$shadow role is preventing us from accessing the "remote" tables.
So I'll test a little bit more, strange that is is better to put the database to another sever and create a linked server connection, than setting credentials to the view.

I'm missing some statement like

CREATE VIEW foobar ... EXECUTE AS [UserWithDBOwnerRights]

the same you can use with stored procedures.

Best regards and "einen ruhigen ersten Advent"

Sebastian

ndbcs

You can also create a "loopback" linked server (link the server with itself).

EXEC master.dbo.sp_addlinkedserver @server = N'LOCALHOST', @srvproduct=N'SQL Server'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LOCALHOST',@useself=N'False',@locallogin=NULL,@rmtuser=N'userWithPermission',@rmtpassword='########'

Really nasty...

Dir auch einen ruhigen ersten Advent.
Tobias

Lars_Westman

But the main issue remains: You still need to be db_owner in the NAV database to access data outside the NAV db.

ndbcs

No, not with the linked server approach.

Tobias

bbrown

ndbcs wrote:

No, not with the linked server approach.

Tobias

But the database is on the same server. So "Linked Server" doesn't apply. Use the Guest account.