AER (1/11/2012)
Jack,Thank you so muck for your help. I totally agree that the Windows Authentication is the most secure way of handling the application pools.
Now since I'm more on SQL Developer/DBA side rather than .NET Developer you should forgive me if this question sounds stupid:
in your 2nd case scenario, how would the WEB Users (not Developers) connect to the SQL Server if it is set only with Windows Authentication?
I thought I had shared that in my original post. The users would connect to the web site as they currently do. I assume that if they are intranet applications that AD is handling permissions. In case 1 of my original post the web application would then connect to the SQL Server using the domain login that it is running under and the SQL Server wouldn't know who the true end user is unless the web application is coded to pass the information to the database.
In case 2 the connection to the SQL Server would be made using the End users domain account so the SQL Server would know who the end user is using the SYSTEM_USER function, etc...
Also I would greatly appreciate if someone will tell me how applicable in this case scenario is an Application Role?
Or is it not a related topic at all?
Any help with this issue would be greatly appreciated.
Thank you.
Alex
Application roles are a whole different beast and in SQL Server 2005+ you wouldn't really want to use an application role you'd want to use EXECUTE AS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question