One very popular option for implementing user security in ASP.NET is to use Forms Authentication with the SQL Server membership provider. This provider creates several database tables to store user-related information, as well as a number stored procedures.
From time to time, a developer will attempt to add the ASP.NET Membership/Forms Authentication tables to their Entity Framework model (or LINQ to SQL, NHibernate, etc.) model. Before doing this, they will often have created referential constraints against these tables. When the mapping doesn’t work out quite the way they planned, they will ask how to make the mapping work.
There’s only one correct answer to this question: Don’t do it at all!
There are a number of good reasons why you should not make your database and code depend upon the SQL Membership Provider database schema. In this post, I will focus on a few of the most important:
- Separation of concerns
- Membership and authentication providers are supposed to be interchangeable
- The SQL Membership Provider database schema is an implementation detail
Separation of Concerns
Your application’s data model is designed to fit your application domain. It will change based upon the needs of the end users. It should not have to change because Microsoft decides to update the SQL membership provider, as well. That would violate the single responsibility principle. It is often dangerous to combine data which is not closely related into a single data model. This danger is compounded when data from two separate domains, written by entirely different companies, and designed for orthogonal purposes is shoehorned into a single entity model.
Membership providers are supposed to be interchangeable
One of the most important design intentions of the ASP.NET authentication and membership provider model is to make it easy to interchange providers. If you decide to stop using the SQL membership provider in transition to Open ID, domain authentication, Facebook authentication, etc., this should be a matter of, at most, a couple of days work to migrate data from one provider to the other, rather than a complete rewrite of your application, starting with the database and moving out from there.
The SQL Membership Provider database schema is an implementation detail
The publicly-documented interfaces to membership and forms authentication are the Membership and FormsAuthentication types, respectively, as well as the relevant sections of the Web.config file. If you write your code around these types, you have a reasonable expectation that your code will continue to work when the .NET framework is next updated. On the other hand, if you query the database directly, there is no guarantee that the schema will not change when the next version of .NET ships. If Microsoft makes a security-related change to the SQL membership provider, then it is conceivable that the schema could even change with a service pack. The cost of relying on an implementation detail is that you never really know.