January 3, 2012 at 5:29 am
We have standard USER >>> app/web server >>> database setup.
The connection between app/web server and database has historically been a SQL login, but we are now implementing Kerberos to have better audit/security records.
The problem we have is that if "user\johnSmith" now has an active login and is now given permissions to run sp_doSomething through the app/web server he can just log in with Management Studio and do just that without using the app/web server.
Obviously we don't want users able to login except through the app/web server.
This seems like it should be a normal thing for people to deal with but I can't see any simple method to get round this... Any ideas?
January 3, 2012 at 5:51 am
Typically, in a configuration like the one you describe, one would use a single login to authenticate from web to database server. If you require the ability to distinguish which user is doing what at the database level, then you are sacraficing the ability to limit their access to the database directly. One might say, "why do you care whether they can access the database directly if you are only giving them a limited set of permissions- they can only do those actvities whether it be through the app, or at the database level"... but assuming you have a good reason for this, the best solution I can come up with in this scenario is to handle the requirement at the network level. If there is a physical firewall in place, limit access to the server via the SQL Server port to only the web/app server. If not, you can do this at the OS level.
January 3, 2012 at 6:07 am
Let me add complexity in your security model. It might ease up your selection 😉 What if you want this application to be accessible by other domain users as well?
IMO web facing applications shouldn’t get any access to database directly. They should always comminute via application server and application server should have mapping between business user & database user (in Business Logic layer). Intranet applications are exceptions to this rule.
January 3, 2012 at 8:02 am
The only method I can see is creating a secondary TCP endpoint on a different port and assigning the Business Users access via this port and then locking this port down so only the web/app server can access it.
This would involve a connection string change on the web/app server and blocking port 1234 on the db server to all traffic except the web/app server. Business User Group Database Login changes would be Deny Connect to default TCP and Grant Connect to new TCP.
DBA's could then access normally using SSMS.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply