Introduction
This article shows how to make role-based security work for a .NET 2.0 application on a shared hosting server using a remote SQL Server 2005 database.
Many have asked the question, “My ASP application uses role-based security, and it works well on my ‘localhost’ with SQL Server 2005 Express. How can I make it work on a production server in a shared hosting environment?”
Differences exist between implementation of a .NET role-based security web application in the development environment and the production environment. I decided to write this article after reading many complaints about newbies not understanding the implementation process. So a few points to be aware of when moving an application from the development machine to the production environment are mentioned here in the article.
Specific data providers and their respective code samples have their links posted in the Data Provider Implementation section and in the References section of this article.
Background
Beginning with the Microsoft .NET 2.0 Framework, role-based access for ASP.NET web applications is controlled by the contents of two files:
- The
web.config file - The
ASP.NET schema file
Once these two files are properly configured, Forms Authentication can then be used to create a secure web application.
When using Visual Web Developer (VWD) within its development environment, these two files are automatically configured for use on the localhost with the Cassini web server and a local user instance of SQL Server 2005 Express.
When deploying the application to the production environment, the default data provider must be changed from SQL Server 2005 Express. And the default ASP.NET schema file must be ported to a database file type for use within the web host’s production environment. Porting the ASP.NET schema file then requires that the web.config file be edited to use that file and its associated remote data server.
The web.config File
Among other uses, the web.config file is used to restrict access to directories (folders) of the ASP.NET web site. Within the VWD development environment, the ASP.NET Web Site Administration tool can be used to create access rules for the application’s web folders. This tool makes it easy to set up and configure a role-based security web application by automatically editing the web.config file to update all of the access rules for the application.
Also, web.config is used to declare which data provider to use for the ASP.NET schema file. The database type and connection string must be declared for the data provider.
When deploying the application to the production environment, the ASP.NET Web Site Administration tool cannot be used. And the web.config file must be manually edited with Visual Web Developer.
The ASP.NET Schema File
The ASP.NET schema file (Schema) is a database file, which contains the data schema, tables, views, and stored procedures, which are compatible for use with either the IIS or Cassini web server. Compatibility implies that the Schema must meet the specifications required by ASP.NET, such as… The Schema has specifically named tables with pre-determined table definitions, along with methods to create and access the data.
The Schema is used to identify users, define personalization preferences, define user roles, and to declare membership of users within the defined user roles.
For SQL Server data providers, the Schema can be generated automatically by VWD with the ASP.NET Web Site Administration tool. Or, if the web host allows their use, other tools such as the ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe) or the SQL Server Database Publishing Wizard can create the Schema.
For custom data providers, the developer must create a script to generate the Schema. Examples of such scripts are available from the links for custom providers that follow.
Issues with Data Deployment
By default, the Schema created within the VWD development environment is a Microsoft SQL Server 2005 Express database file, ASPNETDB.MDF, which is placed into the App_Data folder of the web application. The file is accessed by a local instance of SQL Server 2005 Express. The Cassini web server works well under this default scenario.
In a production environment, at least three reasons exist for not using the default Schema deployment scenario:
- On the IIS web server, ASP.NET recognizes only one App_Data folder; the one located in the root directory of the web server. And access to the App_Data folder is usually not allowed, especially in a shared hosting environment. (Imagine hundreds of web sites trying to share a single Schema file. Although in some strange universe, it may be possible to have a set of users, roles, and membership access rules identical to each of the other hosted web sites on the server. But, that situation is improbable in the real world.)
- SQL Server 2005 Express is not designed for production use and is not supported on production servers due to memory management issues.
- In a production environment, a remote database is used and not a local user instance of SQL Server 2005 Express. Local, as in the database server is installed on the same machine as the web server. Remote, as in the database server is not installed on the same machine as the web server.
A Solution to the Data Deployment Issue
A simple solution is to have the web server use a remote database server. And on that remote database server, a uniquely named database Schema exists for each web site having role-based access. ASP.NET can then implement role-based access for each web site on the shared server, according to the access rules particular to any given web site.
Although SQL Server 2005 Express is not supported within a production environment, other data providers can be used for the Schema. Choose a data provider that is supported by the web host.
When changed from the default data provider, the Schema's data provider must be declared in the application’s web.config file; and the web.config file must then be saved. The application should be momentarily taken off-line, and then placed back on-line for the change in data provider to take effect.
Data Provider Implementation
Thanks to ODBC and OLEDB technologies, custom data providers can be created for practically any database accessible by the .NET web server. Data providers for the Schema may include:
Using the Code
While much material exists for implementing a solution to this question, few seem to completely cover the subject. Mainly, because the code samples only show segments of the editing necessary to set up the web.config file.
This code sample includes the entire script of the web.config file. This is the web.config file that worked for my web application. The specifc names have been changed, but the content is valid. Simply substitute your specifics for the generically-subsituted specifics in my code example.
The configuration is for a remote SQL Server 2005 database. For this example:
The remote data server:THEREMOTESERVERURL (For example, 71.05.26.41)
The Schema file:THESCHEMA (A SQL Server 2005 data file, uploaded to the data server) (for example, wafflehut)
The Database Username: THEUSERNAME (for example, whodatis)
The Database Password: THEPASSWORD (for example, slaphappy)
web.config
<?xml version="1.0"?>
<!--
Please refer to machine.config.comments for a description and
the default values of each configuration section.
For a full documentation of the schema please refer to
<a href="%22%22%22http://go.microsoft.com/fwlink/?LinkId=42127"""">http://go.microsoft.com/fwlink/?LinkId=42127</a>
To improve performance, machine.config should contain only those
settings that differ from their defaults.
-->
<configuration>
<connectionStrings>
<remove name="LocalSqlServer" />
<add name="LocalSqlServer" connectionString="Data Source=THEREMOTESERVERURL;Initial Catalog=THESCHEMA;User Id=THEUSERNAME;Password=THEPASSWORD;" />
<add name="SqlProviderConnection" connectionString="Data Source=THEREMOTESERVERURL;Initial Catalog=THESCHEMA;User Id=THEUSERNAME;Password=THEPASSWORD;" />
</connectionStrings>
<system.web>
<authentication mode="Forms" />
<compilation defaultLanguage="c#" urlLinePragmas="true" debug="false" />
<membership defaultProvider="AspNetSqlMembershipProvider">
<providers>
<remove name="AspNetSqlMembershipProvider" />
<add connectionStringName="LocalSqlServer" name="AspNetSqlMembershipProvider"
enablePasswordRetrieval="false" enablePasswordReset="true"
applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed"
maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
passwordStrengthRegularExpression="" requiresQuestionAndAnswer="true"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</membership>
<roleManager enabled="true" >
<providers>
<remove name="AspNetSqlRoleProvider" />
<add name="AspNetSqlRoleProvider"
connectionStringName="LocalSqlServer"
applicationName="/"
type="System.Web.Security.SqlRoleProvider,
System.Web, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</roleManager>
<customErrors mode="Off" />
</system.web>
</configuration>
NOTE: For purposes of clarity, the application’s connection string and other configuration data are presented in plain text. Once the application is functional, be sure to secure the application’s web.config file by taking industry-accepted security measures as outlined at the following link: DPAPI and Triple DES: A powerful combination to secure connection strings and other application settings
Summary
Within the Visual Web Developer IDE, the default deployment scenario works well for creating, testing, and debugging a web application. The ASP.NET Web Site Administration tool (WSA) automatically creates the proper web.config file settings for web folder access rules. WSA, also, creates and updates the default Schema file, ASPNETDB.MDF. However, this setup only works in the development environment.
Changes must be made when moving the final completed application to the production environment. Required changes include:
- Creating a blank database on the remote database server. A username and password must be created for the web application to use when accessing the data. The web hosting provider can provide the details of how to access the remote database server to create the new database. The web hosting provider can also help with identifying the correct connectionString to use for accessing the new database.
- Scripting the ASP.NET schema to the new database. The web hosting provider can assist with how best to do this step. Some providers allow the use of a tool, such as the ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe). Other web hosting providers simply have the developer FTP the development datafile to a web folder, and then use a Restore function to populate the blank database file on the remote database server.
- Uploading the application's directories and ASPX files to the production web server. But, do NOT upload the App_Data folder nor its contents.
- Modifying the application's web.config file for the connectionString property for the Schema. It must point to the remote database server and the particular Schema data file for the application. Other connectionString properties must also be specified, such as authentication type (Forms Authentication), username and password.
- Modifying the application's web.config file to update both the membership and roleManager providers. The default providers must each be first removed and then added. The remove step eliminates the default SQL Server 2005 Express provider. The add step must follow the remove step, and it adds the new data provider.
- After the steps listed above are completed; rebuild, save, and test the web application. It should now function well with the role-based security features.
References
References - Code Samples
ASPNet101.com. (The connectionStrings and membership portions of). ASP.Net Tutorial - Membership/Roles with Remote DB (Other than SQL Express). Retrieved from http://aspnet101.com/aspnet101/tutorials.aspx?id=63 on May 19, 2008.
edba1970 (Blog Contributor). 2007, November 29. (The roleManager portion of). Re: Connection string in WEB.CONFIG for SQL Server 2005. Retrieved from http://forums.asp.net/p/1008091/2324843.aspx on May 19, 2008.
Guthrie, Scott. 2005, August 25. ScottGu's Blog. Configuring ASP.NET 2.0 Application Services to use SQL Server 2000 or SQL Server 2005. Retrieved from http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx on May 19, 2008.
Arafa, Moustafa. 2006, November 22. Deploying ASP.NET 2 Web Site to Production Server: Part II. Retrieved from http://www.vbdotnetheaven.com/UploadFile/dsdaf/111212006224845PM/1.aspx on May 19, 2008.
Allen, Scott. 2005, November 25. .NET Membership and Role Providers in ASP.NET 2.0 Part I. Retrieved from http://odetocode.com/Articles/427.aspx on May 19, 2008.
References - .NET Security
Foulds, Hannes. 2005, August 26. DPAPI and Triple DES: A powerful combination to secure connection strings and other application settings. Retrieved from https://secure.codeproject.com/KB/aspnet/dapi.aspx on May 19, 2008.
References - Tools & Utitlies
Microsoft Corporation. 2008. A Provider Toolkit. Retrieved from http://msdn.microsoft.com/en-us/asp.net/aa336558.aspx on May 19, 2008.
Microsoft Corporation. 2008. ASP.NET SQL Server Registration Tool (Aspnet_regsql.exe). .NET Framework Tools, ASP.NET Framework 3.5. Retrieved from http://msdn.microsoft.com/en-us/library/ms229862.aspx on May 19, 2008.
Microsoft Corporation. 2007, April 6. Microsoft SQL Server Database Publishing Wizard 1.1. DatabasePublishingWizard.msi Retrieved from http://www.microsoft.com/downloads/details.aspx?familyid=56E5B1C5-BF17-42E0-A410-371A838E570A on May 19, 2008.
References - .NET Role-Based Security
Burman, Dan. March 2005. Web Development with Visual Web Developer 2005 Express Edition and SQL Server 2005 Express Edition, Part 1. SQL Server 2005 Express Edition Technical Articles. Retrieved from http://msdn.microsoft.com/en-us/library/ms345152.aspx on May 19, 2008. Microsoft Corporation.
Stewart, Heath. 2003, June 17. Role-based Security with Forms Authentication. Retrieved from formsroleauth.aspx on May 19, 2008.
Microsoft Corporation. 2008. Introduction to Membership. ASP.NET Framework 3.5. Retrieved from http://msdn.microsoft.com/en-us/library/yh26yfzy.aspx on May 19, 2008.
Microsoft Corporation. 2005, August. Membership Providers. ASP.NET Technical Articles. Retrieved from http://msdn.microsoft.com/en-us/library/aa479031.aspx on May 19, 2008.
Microsoft Corporation. 2005, October. ASP.NET 2.0 Provider Model: Introduction to the Provider Model. ASP.NET Technical Articles. Retrieved from http://msdn.microsoft.com/en-us/library/aa479030.aspx on May 19, 2008.
References - Data Provider Code Examples
Andriniaina, Rakotomalala. 2005, December 20. Membership and Role providers for MySQL. Retrieved from http://www.codeproject.com/KB/database/mysqlmembershipprovider.aspx on May 19, 2008.
Bromberg, Peter A. SQLite 3.0 Membership and Role Provider for ASP.NET 2.0. Retrieved from http://www.eggheadcafe.com/articles/20051119.asp on May 19, 2008.
Snyman, Jacques. 2007, October 22. Suite of MySQL Provider Implementations for ASP.NET 2.0. Retrieved from http://www.codeproject.com/KB/webforms/MySQLsuite.aspx on May 19, 2008.
Microsoft Corporation. 2008. How to: Sample (ODBC) Membership Provider Implementation. ASP.NET Framework 3.5. Retrieved from http://msdn.microsoft.com/en-us/library/6tc47t75.aspx on May 19, 2008.
Microsoft Corporation. 2008. Sample (MS Access) Membership Provider Implementation. ASP.NET Framework 3.5. Retrieved from http://msdn.microsoft.com/en-us/library/44w5aswa.aspx on May 19, 2008.
Microsoft Corporation. 2008. ActiveDirectoryMembershipProvider Class (.NET 3.5). .NET Framework Class Library. Retrieved from http://msdn.microsoft.com/en-us/library/system.web.security.activedirectorymembershipprovider.aspx on May 19, 2008.
Microsoft Corporation. 2005, August. How To: Use Forms Authentication with Active Directory in ASP.NET 2.0. .NET Security. Retrieved from http://msdn.microsoft.com/en-us/library/ms998360.aspx on May 19, 2008.
Microsoft Corporation. 2005, August. How To: Use ADAM for Roles in ASP.NET 2.0. .NET Security. Retrieved from http://msdn.microsoft.com/en-us/library/ms998331.aspx on May 19, 2008.
Microsoft Corporation. 2005, August. How To: Use Forms Authentication with SQL Server (2000) in ASP.NET 2.0. .NET Security. Retrieved from http://msdn.microsoft.com/en-us/library/ms998317.aspx on May 19, 2008.
