On the patterns & practices team, to catalog app solutions, I use a simple “scenario and solution” approach. My manager calls these “cartoons.” The key is to create a visual representation of what’s important — similar to a whiteboard sketch. In fact, there was a point where we called these “whiteboard solutions.” Below is an example of the skeleton of a solution for a common ASP.NET intranet scenario where users are in AD and the role store is in SQL Server. Notice how lean the solution is (which makes it easy to scan to see if it’s right for you.)
This figure shows a Web app to database scenario with users in AD.
This figure shows a common solution pattern for this scenario.
- A dedicated application pool is used and configured to run under a custom domain service account with access to the database.
The application’s virtual directory is configured in IIS for Windows authentication. Anonymous access is disabled.
- ASP.NET is configured for Windows authentication <authentication mode=”Windows”/>
- Application is configured to use SqlRoleProvider
- The solution configures the provider to use a SQL Server role store for which the connection string is contained in the <connectionStrings> section of Web.config.
- Role-checks (user’s Windows group membership) are performed by using role manager APIs with WindowsTokenRoleProvider
- If you have role segmentation in your application then you use URL authorization. e.g. You might have pages that only members of the “Sales” role should be able to access and others that only members of “HR” should be able to access.
- The database connection string includes Integrated Security=SSPI or Trusted Connection=Yes for Windows authentication.
- The database connection string is held in the <connectionString> section of the application’s Web.config. This can be encrypted by using a protected configuration provider (DPAPI on a single machine, RSA if in a Web farm). Tradeoff here is added deployment complexity vs. keeping the database name and location a secret.
- SQL Server configured for Windows authentication
- A SQL Server login is created for the application’s application pool identity.
- The login is mapped to a database user for the Web application.
- The database user is placed in a database role for the Web application.
- Database permissions are granted to to the database role. Ideally, role only grants execute permissions on necessary stored procedures.
- Browser to Web App. SSL is used between browser and Web server to protect sensitive data on the wire.
- Web App to Database. If you’re not in a secure data center, then IPSec or SSL can be used between the Web server and database server to protect sensitive data on the wire. Choose IPSec to encrypt all traffic between servers or SSL to encrypt per application or service.
Initial Catalog=aspnetdb;Integrated Security=SSPI;”>
<roleManager enabled=”true” defaultProvider=”SqlRoleManager”>