ASP.NET Security for SQL Server

Problem
In all of my ASP applications I need to have at least basic security, how can I do so in simple manner?  The reality is that in the majority of my applications I need membership, profiles, role management and personalization, are any applications available directly from Microsoft to handle this need?  If so, where can I find them?  Do they integrate with the .NET 2.0 framework?  If so, how do you set this up?  Where can I find more information?
Solution
With the .NET 2.0 framework, it ships with the ASP.NET SQL Server Setup Wizard.  This wizard will go through the process of setting up tables, views, stored procedures, roles, etc. in order to manage membership, profiles, role management and personalization in a standardized manner.  These objects can be installed on a SQL Server instance and database you specify during the installation.  This wizard can be found at - C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe.

Installation Process

Below are the steps to install the ASP.NET SQL Server Setup Wizard.  In order to initiate this process, double click on C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe which will start the wizard.

ID	Description	Screen Shot
1	Intro Screen - Overview of the application and installation process. Press the 'Next' button to continue the process.
2	Setup Option - Select the 'Configure SQL Server for application services' to install the product. Press the 'Next' button to continue the process.
3	SQL Server Instance - Select the SQL Server instance, authentication means and database.  Press the 'Next' button to continue the process.
4	Settings Summary - Review the installation summary before installing the database objects. Press the 'Next' button to continue the process.
5	Close the wizard - Press the 'Finish' button to end the process.

Installed Objects

ID	Description	Objects
1	Tables	aspnet_Applications aspnet_Membership aspnet_Paths aspnet_PersonalizationAllUsers aspnet_PersonalizationPerUser aspnet_Profile aspnet_Roles aspnet_SchemaVersions aspnet_Users aspnet_UsersInRoles aspnet_WebEvent_Events
2	Views	vw_aspnet_Applications vw_aspnet_MembershipUsers vw_aspnet_Profiles vw_aspnet_Roles vw_aspnet_Users vw_aspnet_UsersInRoles vw_aspnet_WebPartState_Paths vw_aspnet_WebPartState_Shared vw_aspnet_WebPartState_User
3	Stored Procedures	aspnet_AnyDataInTables aspnet_Applications_CreateApplication aspnet_CheckSchemaVersion aspnet_Membership_ChangePasswordQuestionAndAnswer aspnet_Membership_CreateUser aspnet_Membership_FindUsersByEmail aspnet_Membership_FindUsersByName aspnet_Membership_GetAllUsers aspnet_Membership_GetNumberOfUsersOnline aspnet_Membership_GetPassword aspnet_Membership_GetPasswordWithFormat aspnet_Membership_GetUserByEmail aspnet_Membership_GetUserByName aspnet_Membership_GetUserByUserId aspnet_Membership_ResetPassword aspnet_Membership_SetPassword aspnet_Membership_UnlockUser aspnet_Membership_UpdateUser aspnet_Membership_UpdateUserInfo aspnet_Paths_CreatePath aspnet_Personalization_GetApplicationId aspnet_PersonalizationAdministration_DeleteAllState aspnet_PersonalizationAdministration_FindState aspnet_PersonalizationAdministration_GetCountOfState aspnet_PersonalizationAdministration_ResetSharedState aspnet_PersonalizationAdministration_ResetUserState aspnet_PersonalizationAllUsers_GetPageSettings aspnet_PersonalizationAllUsers_ResetPageSettings aspnet_PersonalizationAllUsers_SetPageSettings aspnet_PersonalizationPerUser_GetPageSettings aspnet_PersonalizationPerUser_ResetPageSettings aspnet_PersonalizationPerUser_SetPageSettings aspnet_Profile_DeleteInactiveProfiles aspnet_Profile_DeleteProfiles aspnet_Profile_GetNumberOfInactiveProfiles aspnet_Profile_GetProfiles aspnet_Profile_GetProperties aspnet_Profile_SetProperties aspnet_RegisterSchemaVersion aspnet_Roles_CreateRole aspnet_Roles_DeleteRole aspnet_Roles_GetAllRoles aspnet_Roles_RoleExists aspnet_Setup_RemoveAllRoleMembers aspnet_Setup_RestorePermissions aspnet_UnRegisterSchemaVersion aspnet_Users_CreateUser aspnet_Users_DeleteUser aspnet_UsersInRoles_AddUsersToRoles aspnet_UsersInRoles_FindUsersInRole aspnet_UsersInRoles_GetRolesForUser aspnet_UsersInRoles_GetUsersInRoles
4	Database Roles	aspnet_Membership_BasicAccess aspnet_Membership_FullAccess aspnet_Membership_ReportingAccess aspnet_Personalization_BasicAccess aspnet_Personalization_FullAccess aspnet_Personalization_ReportingAccess aspnet_Profile_BasicAccess aspnet_Profile_FullAccess aspnet_Profile_ReportingAccess aspnet_Roles_BasicAccess aspnet_Roles_FullAccess aspnet_Roles_ReportingAccess aspnet_WebEvent_FullAccess

Next Steps

• Check out the ASP.NET SQL Server Setup Wizard in a development environment and see how this application handles security relative to your needs and/or your current techniques.
• If this application offers additional functionality, then consider integrating this application in your environment.
• With all of the code in an unencrypted manner, you have the opportunity to customize the code as needed or see how techniques implemented by this solution can be customized and incorporated into your applications

Forum Posts

• Discuss this tip: http://blogs.mssqltips.com/forums/t/369.aspx
• There are 0 comments for this tip