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

change the providers to use SQL Server Instead of SQL Express issue

I have followed the tutorial by scott's gu here:

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

This tutorial is just basically so I can port users,membership,etc that is handled by ASPNETDB.mdf from sql express to sql server. I've finished doing the steps, but now after uploading the whole database to my web host. How can I add users, roles, etc?? It's easy back before because we can access it via the ASP.NET Configuration, but now it's just a bunch of tables... and if I have to insert it manually to the table then I wouldn't know what to insert and which is which.
0
kuntilanak
Asked:
kuntilanak
  • 24
  • 13
  • 7
  • +1
1 Solution
 
Dale BurrellDirectorCommented:
You have to write the code to manage the users yourself at this point. Depending whether you want to allow people to sign up themselves, or whether you want to manage it yourself will depend how you approach this.

If people can sign up themselves you can use the membership controls that come with asp.net e.g. createnewuserwizard etc

If just you then you can use something like http://msdn.microsoft.com/en-us/library/aa479399.aspx
0
 
kuntilanakAuthor Commented:
Well actually using the database it self is pretty much stupid as I will only have 2 users... these users are for admins login... My site won't have users registering on it. Is there a way to change it so that forms authentication doesn't use database, i.e not use all the tables generated?
0
 
Dale BurrellDirectorCommented:
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kuntilanakAuthor Commented:
should I then remove this:

<remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="Data Source=winsqlus03.lxa.perfora.net,1433; Initial Catalog=db323219488; User ID=      dbo323219488; Password=Tucson85719@;" providerName="System.Data.SqlClient"/>

if providing the logins from web config? and also the password is in SHA, so how wll I convert my password to the SHA1?
0
 
Dale BurrellDirectorCommented:
Sorry dude... I've never converted a website in that fashion, I know I could do it, but I wouldn't be able to go into detail until I'd done it.

Its a fairly complex area and to be honest I wouldn't like to see you trying to hack it around without at least a basic understanding of how it all hangs together. There are many good tutorials out there on asp.net authentication and asp.net user management.

That said in your case I'd leave it - if your 2 users are already in the database then you will never have to change it anyway.

And as a simple solution if you keep your development site running with sql server express then you can always add new users to that and then manually copy the data from that database over the the production database.
0
 
kuntilanakAuthor Commented:
problem is that when I run the aspnet_regsql.exe all the users that I created got deleted... the table is basically empty
0
 
Dale BurrellDirectorCommented:
If you backup your sql server express database and then restore it to the sql server you are using for production you'll have exactly the same database with exactly the same users and won't need to run aspnet_regsql.exe.
0
 
guru_samiCommented:
FYI: If the connectionstring you posted is real....be careful to post any sesitive data like passwords....beware this is viewable to anyone.

 I believe it is for your custom clubsite. If yes...it is a bit complex.
You have two options:
1:Use membership tables:
Pros: Easy to adapt with existing clubsite code. Easy future expansion if needed e.g. user signup
Cons: You will have those membership/roles/ tables just for two users

2: Use pure forms authentication storing username/pwds in web.config
Pros: no database for users and everything is in web.config
Cons: It will be a bit complex and take some time as login control and security in default clubsite is hooked to membership.
OR check this tutorial that uses single table to store username/password/roles and uses formsauthention:
http://www.devhood.com/tutorials/tutorial_details.aspx?tutorial_id=433

Let us know which way you want to go and if you have any questions.
0
 
kuntilanakAuthor Commented:
well...actually what I really want to do is just to hard code the username and password for two users in the web config.. problem is, as you said... it's hard and I have no idea how...


0
 
kuntilanakAuthor Commented:
can the mods here delete the connection string I wrote...I forgot to erase the password
0
 
_Katka_Commented:
Hi, there's a good article at MSDN how to protect your DB.

http://msdn.microsoft.com/cs-cz/magazine/cc164054(en-us).aspx

Otherwise you can define the users and stuff at IIS at:

Control Panel -> Administrative Tools -> Internet Information Services IIS Manager

after the console is open select then

Sites -> YourWebSite

many options for your website should appear on the right side, in ASP.NET section you should be able to define your:

.NET Trust Levels
.NET Roles
.NET Users

and also:

Connection Strings

1) add your connection string there
2) define your user/profile/role which allows you to use the connection string you've defined above

This should ensure reasonable security for you. You can add more users here later. Here is a good article to explain it in further detail:

http://weblogs.asp.net/scottgu/archive/2006/02/24/ASP.NET-2.0-Membership_2C00_-Roles_2C00_-Forms-Authentication_2C00_-and-Security-Resources-.aspx

regards,
Kate
0
 
kuntilanakAuthor Commented:
I understand the concepts of form authentication and stuff... I only want a way right now to populate my aspnet_Membership, aspnet_Users, etc..etc because now I can't use Website Administration Tools in order to add users/roles/etc... it needs to be done manually... but what is the easiest way.
0
 
_Katka_Commented:
0
 
kuntilanakAuthor Commented:
I've done what is in that article. And this part:

"Please notice various tables created for storing various configuration settings, membership, roles, profile data and personalization data etc"

Again...my question would be. How would we add users and roles to this table? For example the do.aspnet_Role table it has an application id field... if I were to create a role from the Website Administration Tool then I would only need to enter the role name and it would generate the application id automatically for me... but then in this case... if I want to insert something to this table I need to enter the applicationID manually which I don't know what it is
0
 
kuntilanakAuthor Commented:
If I use an ASPNETDB.mdf and Web Management Tool to create a role admin and looked at the dbo.aspnet_Role table I can see the following image. As you can see that the application id is 2c488eb3-d589-4644-8470-5e82be72c048. However when I tried to use this in my application id in SQL Server I got the following error

Incorrect syntax near 'c488eb3'.

INSERT I*****
admin.JPG
0
 
_Katka_Commented:
So it is a web hosting out of your hand. The application ID can be determined by querying the DB <see the code snippet>, but in your case I'd guess that it is your unique Application ID so you can write your own ID to be later referred to. Is there any help, or can you contact some (site support) to get your answer ?

regards,
Kate
protected String getApplicationId()
    {
        string applicationIdString = "";

        using (SqlConnection conn = new SqlConnection(mySqlConnection))
        {
            conn.Open();
            const String selectQuery = "SELECT ApplicationId FROM aspnet_Applications WHERE ApplicationName=@p1";

            using (SqlCommand cmd = new SqlCommand(selectQuery, conn))
            {
                SqlParameter p1 = cmd.Parameters.Add("@p1", System.Data.SqlDbType.NVarChar);
                p1.Value = Membership.ApplicationName;

                SqlDataReader dr = cmd.ExecuteReader();

                if (dr.Read())
                    applicationIdString = dr[0].ToString();
            }

            if (conn != null)
                conn.Close();
        }

        return applicationIdString;
    }

Open in new window

0
 
kuntilanakAuthor Commented:
well the applicationID field is just a field of type uniqueidentifier and based on the article here http://msdn.microsoft.com/en-us/library/ms187942.aspx it says that I can either use newid() function or I can directly input something like  a string constant in the form xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. But then when I do that... it complaints?
0
 
_Katka_Commented:
If an GUID is not accepted I'd go with ApplicationName but maybe without the path so if you have

Application Name = /MyWebSite
Application Id = MyWebSite

Further explanation can be found here:

http://weblogs.asp.net/scottgu/archive/2006/04/22/Always-set-the-_2200_applicationName_2200_-property-when-configuring-ASP.NET-2.0-Membership-and-other-Providers.aspx

regards,
Kate
0
 
_Katka_Commented:
Also I would try to use GUID with brackets { } first. As in:

{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
0
 
kuntilanakAuthor Commented:
Now here's the new error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__aspnet_Ro__Appli__46E78A0C". The conflict occurred in database "db323219488", table "dbo.aspnet_Applications", column 'ApplicationId'.

INSERT INTO dbo.aspnet_Roles(ApplicationID, RoleID, RoleName, LoweredRoleName, Description) VALUES (CAST('2c488eb3-d589-4644-8470-5e82be72c048' AS UNIQUEIDENTIFIER), CAST('973b3b99-f41c-4f6d-a90b-5f50e179fa4a' AS UNIQUEIDENTIFIER), 'Admin', 'admin', NULL);
0
 
_Katka_Commented:
That is correct I'd guess, because that FK in roles is referencing the GUID of Application which is different in this case. This really is messy. Did you tried newId() ? Just to be sure on format of the GUID or whatever it may be ?

regards,
Kate
0
 
kuntilanakAuthor Commented:
new id gives the same thing...so I don't know what else
0
 
_Katka_Commented:
Can you post here the address of your web-host (just home page), I'll try to figure out more ? Also would it be possible to put here the screenshot of that registration screen you see, to get a picture of what is going on (erase all the sensitive info) ?

regards,
Kate

0
 
guru_samiCommented:
kuntilanak:
1: Create a new aspx page
2: Drag and Drop CreateUserWizard on that page and use that to create new user:
Follow step 6 of this tutorial: http://www.asp.net/learn/security/tutorial-05-cs.aspx
Try that first ...and see if you can create a user and login with that user credentials...once you can do that we can move on to assigning roles to that user...
Note: There are some flaws in that starter kit with the login control....but you will still be able to login using the control it already has.
0
 
kuntilanakAuthor Commented:
so you're saying the create user wizard is just an intermediate step to create users because we don't have access to the web management tool?
0
 
guru_samiCommented:
yes...that's correct....Website Admin tool also uses the same control.....did you check the tutorial....
0
 
kuntilanakAuthor Commented:
I've only took a glimpse look at it... so say this works and I can add users... then how can I add roles?
0
 
Dale BurrellDirectorCommented:
This is going down the exact path I first suggested, but have you confirmed you can't just upload the database you've used for development?

And if you haven't its fairly trivial to insert 2 users straight into the database without writing a line of code.
0
 
guru_samiCommented:
-->then how can I add roles?
Once you are able to create user then you will simply need this code block to assign role....

 if (Roles.RoleExists("Administrators"))
        {
            Roles.AddUserToRole(username, "Administrators");
        }
Basically you will need that code in OnCreatedUser event of CreateUserWizard....so the above code is executed whenever you create a user.
0
 
kuntilanakAuthor Commented:
>>This is going down the exact path I first suggested, but have you confirmed you can't just upload the >>database you've used for development?

The problem is that I don't have any way to backup the database in aspnetdb.mdf... any tutorial to do that?
0
 
guru_samiCommented:
Check the aspx and code behind file attached....that is all you need..
Createuseraspx.txt
Codebehind.txt
0
 
Dale BurrellDirectorCommented:
>>The problem is that I don't have any way to backup the database in aspnetdb.mdf... any tutorial to do that?

You can attach it from within Management Studio upon which point its like any other database and can be backed up etc.
0
 
guru_samiCommented:
I noticed on thing in clubsite...If you Click the Membership Menu tab....there is already functionality to create user....and if there are no users in the database....the first user is assigned the Administrators role. The Page you should be looking for is Member_Register.aspx and its code behind.
0
 
kuntilanakAuthor Commented:
okay I was able to create a user, however when I tried to login with that user it always says the login fails..
below is my login button.. also I can see that the role table is still empty and users in role is also empty
<Club:RolloverButton runat="server" ValidationGroup="Login1" Text="Login" ID="LoginButton"
                                        CommandName="Login" />

Open in new window

0
 
kuntilanakAuthor Commented:
how could you also say that the first user would be assigned the role Administrators? As there is no role currently in the table...
0
 
kuntilanakAuthor Commented:
anyhow I am able to create a Role in the database by using the following command, but still when I tried to login... it doesn't logs in
if (!Roles.RoleExists("TestRole"))
{
  Roles.CreateRole("Admin");
}

Roles.AddUserToRole("admin", "Admin");

Open in new window

0
 
guru_samiCommented:
--->how could you also say that the first user would be assigned the role Administrators? As there is no role currently in the table...

That was only true if you are using ClubSite's Member_Register page....
Member_Register.aspx.cs has this code which creates Role if it does not exist...
if (Membership.GetAllUsers().Count == 1)
            {
                // Validate "Administrator" role is avaliable
                if (!Roles.RoleExists("Administrators"))
                {
                    Roles.CreateRole("Administrators");
                }
                // Add the user to the role
                Roles.AddUserToRole(user.UserName, "Administrators");
            }

--->anyhow I am able to create a Role in the database by using the following command, but still when I tried to login... it doesn't logs in

Can you see the user and roles in the database? Are you getting error that login attempt failed or simply nothing happening?

From your code I see you have named your role "Admin" while clubsite has its security set for Rolename ="Administrators". So you might want to create "Administrators" role and add user to that role.
0
 
kuntilanakAuthor Commented:
I can see the users and roles and I can see that the table users in roles are there....when I login it keeps on saying I have the wrong username and password
0
 
guru_samiCommented:
did you make changes to the web.config....can you share that...
0
 
guru_samiCommented:
oh make sure you don't post connectionstring with password
0
 
kuntilanakAuthor Commented:
here it is
<connectionStrings>
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="Data Source=winsqlus03.lxa.perfora.net,1433; Initial Catalog=db323219488; User ID=	dbo323219488; Password=password;" providerName="System.Data.SqlClient"/>
		<add name="ClubDatabase" connectionString="Data Source=winsqlus03.lxa.perfora.net,1433;Initial Catalog=db323219488;User Id=dbo323219488;Password=password; "  />
	</connectionStrings>

Open in new window

0
 
guru_samiCommented:
ok two things to check in aspnet_membership table:
Make sure IsApproved field = true and IsLockedOut field =true

did you use clubsite's default member_register page to create new user?
0
 
guru_samiCommented:
oops...IsLockedOut field =false
0
 
kuntilanakAuthor Commented:
yes I did use the member_register page to create the new user.... I just copied and paste the whole CreateUserWizard block
0
 
kuntilanakAuthor Commented:
okay...the isApproved field is false... how can I change that?
0
 
kuntilanakAuthor Commented:
what's weird is that the code behind has explicity said that the isApproved is true but why does it get stored as false in the table?
try
        {
            DataSetTableAdapters.MemberInfoTableAdapter da = new DataSetTableAdapters.MemberInfoTableAdapter();
            MembershipUser user = Membership.GetUser(CreateUserWizard1.UserName);
            da.Insert(((Guid)(user.ProviderUserKey)), Addr.Text, Phone.Text, fname.Text, lname.Text);
            user.IsApproved = true;
            Membership.UpdateUser(user);
            FormsAuthentication.SetAuthCookie(CreateUserWizard1.UserName, false);
        }
        catch
        {
        }

Open in new window

0
 
guru_samiCommented:
-->yes I did use the member_register page to create the new user.... I just copied and paste the whole CreateUserWizard block

Ok that creatuserwizard has - DisableCreatedUser="True" ...which causes IsApproved to set to false.
you can set it to false..so next time you create a user it is already approved..

--->.the isApproved field is false... how can I change that?

Via C# code like this:
 MembershipUser user = Membership.GetUser("yourusername");
        user.IsApproved = true;
        Membership.UpdateUser(user);

via Sql query:

Update aspnet_Membership set IsApproved=1 where username='yourusename'
0
 
kuntilanakAuthor Commented:
thanks guru_sami! now it worked! yay!!
0
 
guru_samiCommented:
Oh I didn't pay attention to that code at all...ideally it should have activated the user....
but my guess is somehow the was some problem in executing that code and so Membership.UpdateUser was never executed...
0
 
kuntilanakAuthor Commented:
yes... the Membership.UpdateUser was forgotten for some reason... I guess it's a bug on their end
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 24
  • 13
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now