Using the Microsoft Access Providers to Replace the Built-In SQL Server Providers

来源：作者： 2007-11-13 出处：pcdog.com

to test it out, open the page Login.aspx in your browser and login with the account you created earlier. If all turned out as planned, you should see the following after you logged in:

点击查看大图

Figure 10: The Test Page in the browser Showing the User's Name and Group Membership

while I am sure these 39 characters aren't the most exciting things you've ever seen, it's still quite a miracle you see them at all. Although it took you some time to configure the application, you haven't actually coded a lot. Simply by dragging and dropping, and declaratively adding controls and settings to your application, you have a complete security framework at your disposal. Personally, I see this as the greatest strength of .NET: a lot of power is available out of the box. If you don't like the standard behavior, it's still pretty easy to change certain aspects of the built-in functionality and replace them with custom components.

while it may seem that the Access Providers are a great asset to your developer's toolbox (and they are), there are also a few differences with the "real", SQL Server based providers that you need to be aware off.

Differences between the SQL Server Based Providers and the Access Based Providers

the biggest difference is the database schema. In the SQL Server implementation GUIDs (Globally Unique IDentifiers) are used to uniquely identify objects like Users, Roles and Applications. In the Microsoft Access database and the Access Providers, numeric values (Integers) are used. In itself this is not a problem as long as you stick to the Access Providers. However, it's not unlikely that over time your web site grows and you'll find that your Access implementation no longer cuts it. When you then want to upsize to SQL Server, you'll run into a few conversion issues. While it should be possible to upgrade the data to the SQL Server format, you'll need to carry out a number of "data massaging" tasks that may be time-consuming. Note that I haven't actually tried to upsize the Access database to the SQL format yet, so be sure to test this out thoroughly if you think you'll take this route in the future.

another difference is the absence of certain features like enforcing password strength requirements in the Membership provider. The readme.txt that comes with the Access Providers lists all of the missing features and "known issues".

the final difference is not as much related to the Access Providers, as it is to Microsoft Access itself: performance. While an Access database is useful in many situations, it's just not as powerful as its bigger brothers SQL Server 2000 and SQL Server 2005. For many small to medium-sized web sites, you'll find that Access works fine, though. Just don't use it to run the next Yahoo killer web site on it....

although the Access providers have different behavior than, say, the SQL providers, the services that ASP.NET 2.0 provides are not aware of this. For example, both the custom AccessMembershipProvider and the built-in SqlMembershipProvider have the same set of methods, as you can in the following diagram:

Figure 11: The Class Diagram for the Access and SQL Based Membership Provider

as you can see, both providers stick to a contract and implement the same set of methods, like CreateUser, GetAllUsers and so on. This contract is enforced by the abstract MembershipProvider class that both implementations inherit from.

Summary

the Access Providers used in this article may be a welcome tool for many developers. When you can't use SQL Server, but still want to use the built-in features for membership, role management, profiles and personalization, you have to look at other means to store your data. The Access Providers that I used in this article do exactly that: they allow you to use the complete ASP.NET 2.0 feature set, with a Microsoft Access database as the backing store.

this article didn't focus on the inner workings of the providers. In fact, the good thing about the provider model is that often you don't even have to look at the inner workings of things. As long as you know how to configure a new component, you can easily reuse functionality created by others.

so instead, this article focused on making the Access Providers work in your web application. You saw how to acquire them from the MSDN site. You then saw how to customize and compile them, so they're ready to be used in your own web sites. You then saw how to configure your ASP.NET 2.0 web application to make use of these new providers.

the article ended with a quick tour of using the providers and an overview of the biggest differences between the Access Providers and the SQL Server providers that ship with ASP.NET 2.0.

if you want your web site to work with the Microsoft Access providers, this article has shown you everything you need to know and do to embed them in your web application.

References and Related Reading

the following links may help you to better understand the .NET provider model, or help you find and fix problems related to security that you may run into when you install and use the Access Providers.

The Provider Model

The Provider Toolkit
Introduction to the Provider Model
Microsoft ASP.NET 2.0 Providers: Introduction (HTML)
Microsoft ASP.NET 2.0 Providers: Introduction (PDF)
A Look at ASP.NET 2.0's Provider Model

Security

How Do I Determine the Security Account that IIS Uses to Run My Web Site?
How Do I Fix ASP 80004005 errors?

Download Files

The sample site used in this article

Feedback by Other Visitors of Imar.Spaanjaars.Com
On Monday 8/28/2006 10:58:06 PM Robert Searing said: FINALLY an "all-encompassing" article that not only helps explain the ASP.NET new features, but provides a very good understanding of what is going on "underneath the hood" along with a tool box to tweak things. I just have one question---for newbies, such as myself, that weren't ever provided a very good understanding of namespace--could you dig down just a bit deeper on your comment: You may be tempted to "personalize" things, for example by changing the namespace in code from Samples.AccessProviders to MyCompany.AccessProviders for example. What would this do? Why would I want to change it? It works fine as it is....but I'm sure there is a reason I would want to change it. Is there a way you could provide just a basic understanding (very basic) of namespace and then perhaps explain why the Samples.AccessProviders works and then what changing it would do? Kind Regards, Rob Searing
On Monday 8/28/2006 11:38:11 PM Imar Spaanjaars said: Hi Robert, Simply put: namespaces are simply naming containers to avoid name collisions. You and I could both come up with a control called SomeCoolControl. If a page developer tried to add your and my control to a page at the same time, the compiler woulddn't now what SomeCoolControl we were talking about. With a namespace, you can avoid this problem. You call yours Searing.SomeCoolControl, I call mine Spaanjaars.SomeCoolControl and voila: instant unique names.... I have seen many people (including myself) that wanted to make third party code "theirs". A namespace is a great example of that: Instead of Samples.AccessProviders it would be easy to use find and replace and change it to Searing.AccessProviders or Spaanjaars.AccessProviders making it look like we did program all that code ourselves. Looks good for your colleagues, your boss and so on. Until things break apart, you don't know how to fix it and everyone realizes your ripped this code from some place else ;-) Just kidding. But whatever reason you may have to change it; you can do it pretty easily. It wouldn't effect the quality of the code. You could use find and replace and put the provider in the Searing.AccessProviders namespace and everything would still work. But, I recommended not to do it, because it also means changing other things in the web.config file. If you know what you're doing: by all means change the namespace. But if you're not sure, leave as much things as they are; the less chance you have of breaking important stuff... Hope that answers your question... Imar
On Tuesday 8/29/2006 12:24:18 AM Robert Searing said: Almost--and I promis not to belabor the point...answering this should help. So, when you state: System.Web.Security.SqlMembershipProvider Where is that physically located? Does this help the compiler know where to find the file? Same with Samples.AccessProviders.AccessMembershipProvider, SampleAccessProviders How does the compiler know where to find it---and, lastly, by changing the namespace--am I just changing the "Samples" or "Samples.AccessProviders". Thanks so much, Rob Searing
On Tuesday 8/29/2006 8:02:26 AM Imar Spaanjaars said: Hi Robert, System.Web.Security.SqlMembershipProvider is probably located in System.Web (not sure). Usually, it's recommended to name an assembly after the root namespace, but this is not required. So, you could store the namespace Samples.AccessProviders in an assembly called WhatEver.dll, but obviously, this is pretty confusing. Either way, the compiler doesn't care. It doens't help the compiler to find things. You reference an assembly, whatever it's called, and the compiler can see all the namespaces defined in that assembly. And if you want to change the namespace, you can change both. So, Searing.AccessProviders would work, as would AccessProviders as would Searing.Confusing.Namespace.That.Doesnt.Say.Much This is exactly what I meant in my article: at this point, you probably want to leave the namespace as is .... ;-) Cheers, Imar
On Saturday 9/16/2006 9:34:13 PM Mick said: Hi excellent article, I am at long last able to add an access db to my website, but I keep getting configuration error messages when I build the site, I've added the suggested namespace references from your article but still no joy :-)
On Sunday 9/17/2006 12:58:48 PM Imar Spaanjaars said: Hi Mick, What error messages do you get? Are they compile errors or compile warnings? Imar
On Sunday 9/24/2006 11:00:57 PM Ron Heimann said: Thank you very much :]
On Wednesday 10/4/2006 4:03:03 AM Allan Parson said: Hello Imar, Thank you for a great article. I ran the sample web site on my workstation, and it worked fine. But when I uploaded the same site to my hosting company, it generated "HTTP 500 - Internal server error " right after I enter my credentials in the Login.aspx page and click Login button. Although, if I enter the wrong credentials, it asks me to enter the right ones which means that it works to some point. Could you kindly tell me what might be a problem? Thanks a lot. Allan Allan
On Wednesday 10/4/2006 4:09:32 AM Imar Spaanjaars said: Hi Allan, This sounds like a security issue. A failed Login doesn't write to the database, while a successfull login does which means the account used by the webserver needs write permission to the database and its folder. Check out this FAQ for solutions to this problem: http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=263 Cheers, Imar
On Wednesday 10/4/2006 5:46:34 AM Allan Parson said: Hi Imar, You was exactly right. That was a 'write' permission to a DB file problem. Thanks a lot! Allan
On Sunday 10/15/2006 12:05:25 AM Mohamed Salem Korayem said: Excellent all-in-one article....really appreciate it
On Sunday 10/15/2006 4:30:58 PM Gabe said: Hi Imar Thank you for this posting. I was having problems with the sql server i am hosting my site at. I needed something like this. I have 2 questions. 1. i created a PasswordRecovery object on my form. when i am running the application, i try to retreave a user's password but i get the following error: Parser Error Message: Provider cannot retrieve hashed password Source Error: Line 11: [clear/] Line 12: [add name="AccessMembershipProvider" Line 13: type="Samples.AccessProviders.AccessMembershipProvider, SampleAccessProviders" Line 14: connectionStringName="AccessFileName" Line 15: enablePasswordRetrieval="true" -any ideas? 2. when i create an user using the CreateUserWizard how can i forze the new user to be in a default role? Thank you!!! Gabe
On Sunday 10/15/2006 4:37:38 PM Imar Spaanjaars said: Hi Gabe, You can't use enablePasswordRetrieval with a value of true when you're hashing passwords. Hashed password cannot be converted back to their original clear text version. Instead, you can use enablePasswordReset="true" which gives the user a new, random password. Alternatively, you can choose not to use password hashing, but use encryption instead. Personally, I wouldn't do that as hashing is a bit safer. You can assign a user to a role with the follow code *in the CreatedUser event for example) Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser Roles.AddUserToRole(CreateUserWizard1.UserName, "Default Role") End Sub Hope this helps, Imar
On Sunday 10/15/2006 7:27:26 PM Gabe said: Thank you for your help. I got that to work. Now i have another problem. I had everything working the way i wanted but i needed to install SSL for my domain. right after i did that when i try to use my login.aspx, i get an http 500 error after i click on login. Most likely causes: The website is under maintenance. The website has a programming error. any ideas?
On Sunday 10/15/2006 7:40:35 PM Imar Spaanjaars said: Hi Gabe, You can take a look here: http://www.spaanjaars.com/QuickDocId.aspx?quickdoc=264 to see how to turn generic 500 error messages into more useful information. If that doesn't help, I suggest you post your problem at a forum like the one at http://p2p.wrox.com. It's a much better platform for posting technical questions than my web site. Cheers, Imar
On Sunday 10/15/2006 8:01:15 PM Gabe said: Thank you again... i just get "Cannot write to DB File" , i looked at the code from the provider and it looks like this: HttpContext context = HttpContext.Current; if (context != null) { context.Response.Clear(); context.Response.StatusCode = 500; context.Response.Write("Cannot write to DB File"); context.Response.End(); } i just do not anderstand why after enabling SSL the connection seems to get lost. I am going to try posting where you suggested. Thank you so much for all your help.
On Sunday 10/15/2006 8:35:20 PM Imar Spaanjaars said: Hi Gabe, You're welcome. When you've posted your question there, send me the link and I'll take another look. I haven't run into into issue myself, so I cannot give much advice, though.... Imar
On Tuesday 10/17/2006 5:04:10 AM Gabe said: Hi Imar I wanted to get back to you since you helped me so much. The problem is related to write access to the App_Data folder. when i enabled IIS it reseted all the permissions set on my folders. My hosting company has a way for the users to set different permission levels for each folder. I cleaned everything i did before, then i re-uploaded my code and reset permissions. You don't really have to do all that but since i was testing with different settings i had a big mess. You could just set "write" permissions to when your db files are. Thank you again. Gabe
On Tuesday 10/17/2006 8:26:39 AM Imar Spaanjaars said: Hi Gabe, Thanks for the follow up. Glad it's all working now. In case you hadn't seen it, Allan asked a similar question and I referred him to http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=263 Might be useful in the future..... Cheers, Imar
On Friday 10/27/2006 4:48:41 AM Cornelius said: Excellent Article Imar!!. Exactly what I need. Thanks.
On Tuesday 10/31/2006 1:41:49 AM cjonex said: i looked high and low for this crap and gave up a month ago

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a CAPTCHA image now. This means that if you want to leave a comment, you'll need to type in the text you see on the image. If you can't see the text clearly, click the New Image button to get an image with a new text. For more details, see this news item.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the QuickDocId of the document.
For more information about the Talk Back feature, check out this news item.

更多内容请看PCdog.com--SQL Server专题