Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

[How Do I:] Set Up the SQL Membership Provider

0 (0 Likes / 0 Dislikes)
  • Embed Video

  • Embed normal player Copy to Clipboard
  • Embed a smaller player Copy to Clipboard
  • Advanced Embedding Options
  • Embed Video With Transcription

  • Embed with transcription beside video Copy to Clipboard
  • Embed with transcription below video Copy to Clipboard
  • Embed transcript

  • Embed transcript in:
    Copy to Clipboard
  • Invite a user to Dotsub
In this video, we're going to talk about setting up the SQL membership provider. With ASP.NET 2.0, Microsoft's included membership providers, along with other providers like profile and roles that have really added a lot of functionality and made life easier for developers and also have prevented developers from making some very common mistakes that caused security errors in previous applications. They have taken best practices and implemented them. And the best example of this is the SQL membership provider for when you want to do forms authentication but don't want to build the system from scratch. There's a few tips and tricks that you need to know in order to make this work, and so we have this sample application for you to walk through and use yourself to make sure you understand how to get it going. So, as you see, we have an ASP.NET application, done in VB. There really won't be any code, other than the web.config, but we'll include the project anyway. So, as you see, we have a login view control. And the login view control is designed to allow me to display different views for different logged-in status. When I have anonymous, I can display these words, "You are currently not logged into the system", and give you a log-in control. And when you are logged in, we want it to display a control that shows you your logged-in status, as well as "You are logged in as" and a user name. We're using the controls from the toolbox that are in the Login section to do these things. Now without having already configured membership, this really won't work, because it needs some way to understand how to get to the system. And the secret sauce here is, if I go into my web.config, the default web.config would have just a connection string section that opens and closes with no entries. The first thing we do is enter a clear, and then we add the "LocalSQLServer" connection string. The "LocalSQLServer" connection string will point to a database, a SQL Server database, SQL Server 2005 in our case, on my local server, but it could be any server anywhere, to a database that I designate. The default, as we'll see, is aspnetdb, and then I can either use credentials, or the best practice is to use a trusted connection. Now the only other thing I have to do in my web.config to make this all work is I have to set the authentication mode equal to "Forms". It defaults to Windows, so that'll be an edit you'll have to make. Nothing else has to change in the web.config. Let's try running the application now, knowing that when we look at SQL Server, that database doesn't exist. We start the system. And it logs me in as anonymous. I'm logged in as anonymous. Technically, that's how it works, but I really haven't logged in. I haven't been authenticated with the membership system. Right now, I'm hitting the website as an anonymous user, through IIS. Now I could type in anything here, and it won't help me, because there is no back-end database. The system is designed, no matter what I type in, it goes and it tries to find that aspnetdb database to validate what I've typed in. It already deals with things like SQL injection and other common exploits against an authentication system. That's one of the reasons why it's really a best practice to use the membership system. But it's saying that it can't open the database aspnetdb, requested by the log in. If I don't even have the connection string to the local database, then it won't even display this information, because it won't consider my system as using the membership provider, and therefore none of the controls will work. So how would we get this database? Well, rather than making us go and run scripts or do any of those things, we can use an application that Microsoft has provided us. It's in the Windows Microsoft.NET Framework Version 2 subdirectory, and it's called aspnet_regsql.exe. And it sits right next to regIIS and some of the other files you may have played with if you've tinkered with this directory. When we run it without command-line prompts, it opens up a wizard. And this wizard will walk us through. It'll create the SQL Server database. Or it will add the tables necessary to any database we designate. So let's run through this wizard real quick. We do want to configure SQL Server for application services. In other words, we want to create membership profiles, role management, personalization. This is going to do all of that. We're mostly concerned with membership. But it applies to all these other areas as well. We can also use this wizard to remove options. I'm going to use my server name, Windows authentication, and then I get to pick a database name. In the database name section, I can pick any of my existing databases, or I can pick Default. If I choose Default, it will automatically assume the name aspnetdb. And that's what I want anyway. So in this way, you can package this membership tables, which we'll look at in a minute, into any database you want, or you can keep them in a separate database. The choice is yours. So now it's summarizing. It will now run and create the database. And when I hit Finish, I can go back into SQL, hit F5 to refresh, and now I see I have a new database called aspnetdb. If I go into the tables for aspnetdb, I can go and look at Membership. And we'll look at what kind of entries it has. It has an ApplicationID, to allow us to use one database for multiple applications, the UserID, Password, Format. We even have the ability to use encryption with random salt with the password. Any kind of PIN if we need it. Email. The email in all lowercase, so we don't have to deal with conversions. As well as other information, such as password questions. This is a pretty powerful implementation, because it has so many different features in it. Okay, now that we know the database exists, let's run our application again. And I still don't have a user name and password, so how do I get that to work? Well, we're going to close down our system and go to website in Visual Studio 2005 ASP.NET configuration. And in ASP.NET configuration, we get an application that will run on the system, that lets us go and configure security. It also lets us do provider configuration. These three links correspond to these three tabs. So if we go to Provider, we'll notice that ASP.NET SQL Provider is automatically selected. When we go to Security, we have the option to create users, manage existing users, select authentication types, play with roles. But we want to just create a user, so we can log in and test our system. We'll do something very simple, Test. The password has to be at least 7 characters. And it has to have a non-numeric, like # or $ or something like that. So I'll type in one that meets those criteria. Email, [email protected] It'll still work. And we'll just do test and test. The security question and answer can be used for extra authentication or for sending somebody the forgotten password. When I create this, it says it's completed correctly. And now I can go back to the main page. And I see that I have a user. So let's go back in to Visual Studio, run this, and see if I can log in with this new user. Test, type in the password, and now we see that our system has logged me in. And so this removes the complexity of building an authentication system for most developers, which is something they're not typically qualified to do. If you do want to build your own membership provider, you can, and then you can plug it in. You can override all the members. We'll talk about that in future How Do I videos. Right now, I'm going to log out. And we'll go back into our system and take a look at it one more time. Stop running it. We have our web.config. This one connection string. And we have our controls that we use to leverage the membership provider. We can also leverage it programmatically, but, again, that's a top for another day. I hope this helps you understand the moving parts and the basic setup for using the SQL membership provider. It's something that everyone should be looking at and using wherever appropriate.

Video Details

Duration: 9 minutes and 34 seconds
Country: United States
Language: English
License: All rights reserved
Genre: None
Views: 6
Posted by: neudesicasp on Aug 23, 2013

Join Patrick Hynds as he demonstrates how to set up the SQL Membership Provider to add Forms authentication to your ASP.NET 2.0 Web site. There are a few tips and tricks involved, such as using the aspnet_regsql.exe utility, which are covered in the video.

Caption and Translate

    Sign In/Register for Dotsub to translate this video.