?
Solved

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

Posted on 2012-08-31
22
Medium Priority
?
1,053 Views
Last Modified: 2012-09-05
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
Comment
Question by:awilderbeast
  • 9
  • 8
  • 3
20 Comments
 
LVL 14

Expert Comment

by:EMB01
ID: 38354075
The default membership provider in asp.net mvc uses it's own database.  Are you wanting to use a custom database?
0
 
LVL 26

Assisted Solution

by:Alan Warren
Alan Warren earned 1000 total points
ID: 38356355
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
 
LVL 14

Expert Comment

by:EMB01
ID: 38356372
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:awilderbeast
ID: 38363293
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
 
LVL 14

Expert Comment

by:EMB01
ID: 38363301
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38363494
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
 
LVL 1

Author Comment

by:awilderbeast
ID: 38363757
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
 
LVL 14

Expert Comment

by:EMB01
ID: 38363787
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
 
LVL 1

Author Comment

by:awilderbeast
ID: 38363947
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
 
LVL 14

Expert Comment

by:EMB01
ID: 38364123
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
 
LVL 1

Author Comment

by:awilderbeast
ID: 38364157
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
 
LVL 14

Expert Comment

by:EMB01
ID: 38364218
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
 
LVL 1

Author Comment

by:awilderbeast
ID: 38364281
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
 
LVL 14

Expert Comment

by:EMB01
ID: 38364296
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
 
LVL 1

Author Comment

by:awilderbeast
ID: 38364459
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
 
LVL 14

Accepted Solution

by:
EMB01 earned 1000 total points
ID: 38364578
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
 
LVL 26

Expert Comment

by:Alan Warren
ID: 38366058
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
 
LVL 1

Author Comment

by:awilderbeast
ID: 38367902
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
 
LVL 1

Author Comment

by:awilderbeast
ID: 38367905
This and adding SQL providers in web config gets it going
0
 
LVL 14

Expert Comment

by:EMB01
ID: 38368133
@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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Integration Management Part 2
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question