• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1080
  • Last Modified:

asp.net login.aspx DefaultMembershipProvider to query an sql table for users?

HI all,

i jsut created a new web app in visual studio and it auto creates these login forms for you. i wondered how they worked so submitted the form to get the error.

i was wondering how to configure these default builtin forms to connect to an sql users table, or do i change the submit button to query the db, and it cant be done via web config?

Thanks

Line 21:     <membership defaultProvider="DefaultMembershipProvider">
Line 22:       <providers>
Line 23:         <add name="DefaultMembershipProvider" type="System.Web.Providers.DefaultMembershipProvider, System.Web.Providers, Version=1.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" connectionStringName="DefaultConnection" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" applicationName="/" />
Line 24:       </providers>
Line 25:     </membership>

Open in new window

0
awilderbeast
Asked:
awilderbeast
  • 9
  • 8
  • 3
2 Solutions
 
EMB01Commented:
The default membership provider in asp.net mvc uses it's own database.  Are you wanting to use a custom database?
0
 
Alan WarrenApplications DeveloperCommented:
Hi awilderbeast,
did this yesterday, what a coincidence!

the location of the file (aspnet_regsql.exe) that installs the membership server objects to an existing (possibly new) sql db, depends which version of .net framework you are using.

In .net farmework version 4 the location is: C:\Windows\Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe

The aspnet_regsql.exe utility is a wizard, it will ask for credentials to access the db catalog ( sa is good ), authentication type ( I would use SQL Authentication here ).

Once installed, you can verify that the tables and sprocs exist by logging into Sql Server Management Studio as the System Admin (sa), while logged in as sa you might want to create a Login credential to associate with the new db. Then you can test the new login credential/user by logging out as sa and login to SSMS using the new login credentials. You may also want to give the new user some priveledges over the new db, dbo is not the best choice, but will suffice for testing, you can modify the schema mapping etc... later.

If successful logging the new user into SSMS and the new user has sufficient permissions to update tables, (just create a table and open it in edit mode to test), then you can create your connection string for you web.config.

Comment out the ApplicationServices connection string in your web.config, replace all occurrences of "ApplicationServices" in your web.config with the name of your new connectionstring, should find about 3 occurrences, role provider, membership provider and something else.

your new connection string should look something like this:
<configuration>
  <connectionStrings>
    <!--
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
    -->
    <remove name="cnYourChoiceOfNameHere"/>
    <add name="cnYourChoiceOfNameHere" connectionString="Data Source=yourserver;Initial Catalog=YourNewDB;Connect Timeout=15; pooling='true'; Max Pool Size=200;Persist Security Info=True;User ID=YourSqlUser;Password=YourSqlUserPwd" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Open in new window


After that the asp .net site/app should work, you should be able to create a new user and login.

HTH

Alan ";0)
0
 
EMB01Commented:
This is the easiest way I found to make the membership provider use your own database (a question I asked on stackoverflow):

http://stackoverflow.com/questions/12159926/vb-asp-net-mvc-custom-membership-provider
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
awilderbeastAuthor Commented:
in my db i have a load of tables named aspnet_ when i use the login page its looking for tables without the name aspnet_ do i rename them?

also can i alter these tables without breaking the logon? i.e the userID column wants a guid!
i cant be faffed to create guids, i want to set it to an integer and have it auto incremenent

can i make those changes without breaking it?

Thanks
0
 
EMB01Commented:
If you want to use your own tables, you must follow the steps I sent you in the link http://stackoverflow.com/questions/12159926/vb-asp-net-mvc-custom-membership-provider.  You have to implement your own membership provider.  It's not that hard and you can use all the same methods; you just have to write them yourself (via overrides).  I only am using the one right now, AuthenticateLogin().
0
 
Alan WarrenApplications DeveloperCommented:
You can do anything you want to, but the Membership Class won't function as expected if you do; unless you over-ride it, but why would you? It's one of the finest objects uncle Bill has given us for a long time...

Alan
0
 
awilderbeastAuthor Commented:
ah i use that class to create my users then?

do i just run the below from anywhere and it will create?
also do i rename all the aspnet_ tables to tables without the aspnet_ as all my pages are looking for tables without it?

public static MembershipUser CreateUser(
	string username,
	string password,
	string email,
	string passwordQuestion,
	string passwordAnswer,
	bool isApproved,
	Object providerUserKey,
	out MembershipCreateStatus status
)

Open in new window

0
 
EMB01Commented:
Yes, you could create your users with the new class that inherits the MembershipProvider.

You could run it anywhere you have instantiated the Membership provider class.  Just access methods like:

Membership.ValidateUser("userName")

This is completely separate of your current tables.  This is so you can use your own database.
0
 
awilderbeastAuthor Commented:
Just having issues adding a user at the mo. i thought if i used the in built register.aspx form it would create me a user.

no good, it keeps having different field names, which i can easily changed but just got hit with one i couldnt do anything about...

Cannot insert the value NULL into column 'LoweredApplicationName', table 'TESTDB.dbo.Applications'; column does not allow nulls. INSERT fails.
The statement has been terminated.

seems the register.aspx file doesnt auto fil in that field, should i change it so it could be null? i dont want to break it...
0
 
EMB01Commented:
I don't suggest using your old database, if that's what you're doing.  It may have fields that require certain values.  If you're using a new membership provider, just start fresh by using a new table.  If you have old data you want to use, just write a migration.
0
 
awilderbeastAuthor Commented:
im using the DBs created from the aspnet_reg.exe wizard

and for the accounts forms, using the default built in ones visual studio creates when you do a new app
0
 
EMB01Commented:
If you're just going to use the default databases then why do you need a custom membership provider?  What are you trying to do?
0
 
awilderbeastAuthor Commented:
i was just trying to use all the default login stuff, but they arent working...

the DB connections work but the default pages keep throwing out errors, as above
0
 
EMB01Commented:
Follow this tutorial to use the default membership provider properly http://www.asp.net/mvc/tutorials/mvc-music-store/mvc-music-store-part-7 otherwise, read this link to roll your own membership provider http://stackoverflow.com/questions/12159926/vb-asp-net-mvc-custom-membership-provider.
0
 
awilderbeastAuthor Commented:
ok i clicked asp.net config in visual studio
the url looks like this
http://localhost:62398/asp.netwebadminfiles/default.aspx?applicationPhysicalPath=\\ITVM\c$\TEST\&applicationUrl=/TEST

then i get this after its shown me the first page:-
There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

The following message may help in diagnosing the problem: Unable to connect to SQL Server database.


when i press the button to choose the datasource it says aspNetSqlProvider and thats the only option where is that set/ how do i create one?
0
 
EMB01Commented:
You may not have the correct connection strings set up in your web.config file.  I suggest you start a new project to learn how to use membership stuff by following the tutorial I posted, then either migrate your controllers/actions/views or try to fix your configuration errors.
0
 
Alan WarrenApplications DeveloperCommented:
Can you access the db catalog via Sql Server Management Studio (SSMS)?
You possibly need to login to SSMS using some admin credentials like 'sa', ensure the database has an associated Login defined like some_user and that some_user is mapped to your catalog.

Essentially you need to be able to log into SSMS using the credentials as defined in your connection string, in your web.config. If you can't, then you either need to modify your connectinstring or set up credentialling (Login, Role, Mapping etc...) in the sql server instance for the user defined in your web.config.

As a temporary measure, you could modify your connection string to use the 'sa' credentials, I strongly suggest you don't leave it that way, but if any user can establish a connection, 'sa' can.

Alan
0
 
awilderbeastAuthor Commented:
few!

I finally did it!
I just had to change the providers to SQL providers, all is working great!, good stuff this .net eh!

thanks for your help!
0
 
awilderbeastAuthor Commented:
This and adding SQL providers in web config gets it going
0
 
EMB01Commented:
@jarmod101:  Thanks, it wasn't a big deal I just wanted them to know for future reference "if someone is trying to help you find your solution and takes the time to post several comments to you to that effect" you should take a moment to appreciate that in whatever small way you can.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 8
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now