View and User-Rights (Application Role)

  • 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

  • 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 ")

  • 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

  • You're welcome, Jörg 🙂

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

  • 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]

  • 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

  • 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

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

  • 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

  • Demands too many resources, isn't it? 🙂

  • 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

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

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