Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

View and User-Rights (Application Role) Expand / Collapse
Author
Message
Posted Monday, December 7, 2009 3:21 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:44 AM
Points: 32, Visits: 1,216
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
Post #829715
Posted Monday, December 7, 2009 6:41 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:16 PM
Points: 363, Visits: 1,321
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 ")
Post #829806
Posted Monday, December 7, 2009 8:32 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:44 AM
Points: 32, Visits: 1,216
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
Post #829931
Posted Monday, December 7, 2009 11:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:16 PM
Points: 363, Visits: 1,321
You're welcome, Jörg
However take care of your data when using "guest", see http://www.mssqltips.com/tip.asp?tip=1172.
Post #830145
Posted Monday, December 7, 2009 2:05 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #830263
Posted Tuesday, December 8, 2009 1:03 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:44 AM
Points: 32, Visits: 1,216
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
Post #830462
Posted Tuesday, December 8, 2009 7:51 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:44 AM
Points: 32, Visits: 1,216

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
Post #830691
Posted Tuesday, December 8, 2009 8:05 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:16 PM
Points: 363, Visits: 1,321
Try to add to a SQL trace the Security Audit ---> Audit Statement Permission Event
Post #830706
Posted Tuesday, December 8, 2009 8:40 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, September 18, 2014 3:44 AM
Points: 32, Visits: 1,216

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
Post #830736
Posted Tuesday, December 8, 2009 10:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 18, 2014 11:16 PM
Points: 363, Visits: 1,321
Demands too many resources, isn't it?
Post #830817
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse