View and User-Rights (Application Role)

Question

View and User-Rights (Application Role)

stryk

Ten Centuries

Points: 1172
More actions
December 7, 2009 at 3:21 am

#207026

Hi all,
I'm not sure how to explain my problem properly, so I'll start from the scratch:
I have a database - call it "DB_A". All users connect to this DB via an application (MS Dynamics NAV) which uses an internal "Application Role" (in "DB_A") called "$ndo$shadow".
I have another database - call it "DB_B" which is on the sames SQL Server.
Now I want to create a "View" (call it "View_A") in "DB_A" looking at a table (call it "Tab_B") in "DB_B".
I grant ALL rights on this View to "public".
I include this "View" in the NAV application. When trying to read from this View the users get an error message ("Invalid User or Password").
I guess this is because the "$ndo$shadow" role ("DB_A") somehow does not get access to "DB_B".
In SSMS, when using the same Login-Account - in this case only "public" - it works OK.
The current workaround is to use a Linked Server to "DB_B", here mapping the User-Rights.
So the actual question is: Is there another possibility? Without Linked Server? Can I give "$ndo$shadow" access to another database "DB_B"?
Any ideas welcome, appreciate your feedback. Thanks in avance.
Kind regards,
Jörg
Jörg A. Stryk
MVP - MS Dynamics NAV

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

dmoldovan SSCertifiable Points: 6334 More actions · Answer 1

You'll need the "guest" user in the other database. See BOL ---> Application Roles and http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/ ("Con: Crossing Databases Requires the guest User ")

stryk Ten Centuries Points: 1172 More actions · Answer 2

Yeah ... you made my day 😛

This works!

Small thing with tremendous impact ...

THANKS a lot!

Best regards,

Jörg

Jörg A. Stryk
MVP - MS Dynamics NAV

dmoldovan SSCertifiable Points: 6334 More actions · Answer 3

You're welcome, Jörg 🙂

However take care of your data when using "guest", see http://www.mssqltips.com/tip.asp?tip=1172.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Right, the problem with enabling "guest" is that you are allowing everyone else who has any kind of access to your SQL Server to get into DB_B also, not just the NAV app users from DB_A.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

stryk Ten Centuries Points: 1172 More actions · Answer 5

Thanks for the hint!

Well, "hardening" a NAV database outside the internal NAV security system is actually a really mean issue. Once this "$ndo$shadow" role was bypassed - e.g. simply using MS Excel to connect to the SQL Server - the only role applied is "public", which grants them way too many rights ...

My customer is quite aware of this and we have started a global "database security" project. And thanks to your advice we will consider the "guest" issue, too, of course.

But the primary goal was to get easy access to these remote Views, and that's solved (again, thanks a bunch).

Now we will dig into the "hardening" stuff ...

Best regards,

Jörg

Jörg A. Stryk
MVP - MS Dynamics NAV

stryk Ten Centuries Points: 1172 More actions · Answer 6

OK, I'm currently looking into various issues about "hardening" (createing roles, etc.).

According to that I would like to evaluate features to monitor the user access. So far I have created SQL Profiler Traces to monitor the Audit Login/Logout and I enabled the logging to the SQL Errorlog.

It would be convenient, if an Operator could be informed whenever a "permission issue" occurs. Therefore I created an Alert for events with "severity level" 014 (insufficient permission). Well, this Alert triggers whenever a login fails, but NOT if e.g. a SELECT permission on a table was denied.

But what I'd like to do is to monitor all those queries which fail due to this "permission denial" - obviously 014 is not sufficient.

Is there a chance to accomplish this?

Jörg A. Stryk
MVP - MS Dynamics NAV

dmoldovan SSCertifiable Points: 6334 More actions · Answer 7

Try to add to a SQL trace the Security Audit ---> Audit Statement Permission Event

stryk Ten Centuries Points: 1172 More actions · Answer 8

Hmm ... this does not work as I hoped ...

But: I found out that the standard sys.messages of severity "14" are not logged by default. So I set "WITH_LOG" to TRUE for message 229 - et voila, it works! Now the Alert triggers whenever e.g. a SELECT permission was denied, etc.!

Thanks & best regards,

Jörg

Jörg A. Stryk
MVP - MS Dynamics NAV

dmoldovan SSCertifiable Points: 6334 More actions · Answer 9

dmoldovan

SSCertifiable

Points: 6334

December 8, 2009 at 10:06 am

#1089353

Demands too many resources, isn't it? 🙂

chrisgatguis Grasshopper Points: 11 More actions · Answer 10

Hi,

sorry to dredge this old post up but it is exactly the same problem that I am facing.

I have given the guest account (on both databases DB_A & DB_B) connect permissions but still get an access denied error message in Navision Nav.

We are trying to do the exact same thing as the OP (view in DB_A which looks at a table in DB_B)..

the only difference is that this is in the SQL 2005 forum and we are actually using 2008 R2 so wondered if there are some specific differences with regards to the guest account between the two versions.

Thanks

Chris