SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deployment for ASP.NET User Membership Role Management


Deployment for ASP.NET User Membership Role Management

Author
Message
mvbokker
mvbokker
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
I setup sql server membership provider within my dev environment and everything worked well. I then tried to deploy by doing a backup/restore of the database to the test sql server and this also worked well from within the visual studio ide, but when I deployed the website I could not logon. I ran into several issues..

I was trying to logon using windows authentication and encountered these errors.

Cannot open database "dbasename" requested by the login. The login failed.
Login failed for user 'computername\ASPNET'.


I added an aspnet user and granted access to the database but then received table privilege errors. I tried to add the table privileges to the ASPNET user, but this failed.

Questions?

Is it better to create a trusted account login/pwd for an application with role membership than to use windows authentication? If you create an application login account, will all managed users get access to whatever the trusted account has access to?
Also do you need to run aspnet_regsql.exe on the database you are deploying to or can you simply backup/restore on the deployment server?

Thanks!Crying
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25512 Visits: 1917
Trusted Connection = Windows login

Is the SQL Server on the same server as the IIS server where your ASP.NET application is running?

K. Brian Kelley
@‌kbriankelley
mvbokker
mvbokker
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
Right...meant Sql server authentication vs. Windows.

They may be on the same server and may not be. Initially they will be on the same server.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25512 Visits: 1917
So you moved the application and database (both) from one server to another? Is that correct?

K. Brian Kelley
@‌kbriankelley
mvbokker
mvbokker
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
Yes.

I did a backup/restore of the database and installed the asp.net website on IIS 5.1. I ran the app from vs2008 prior to running in IIS and it worked fine. Deployed in IIS, I received the permission errors noted above.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25512 Visits: 1917
Then it's probably an out of synch login-user mapping. Each Windows account, to include ASPNET, has a SID (Security IDentifier) to identify it uniquely. When you go from one server to another, though the account seems to have the same name, it has a different SID. SQL Server does use that SID when you add the login. And when you add the user, the SID is what matches up the user to the login. So your database has the old SID and your SQL Server has the new SID. This is what is known as an orphaned user in SQL Server.

Check to see if your database has a user by the name of OldComputer\ASPNET or just ASPNET. If either of these exist, then all you need to do is create a new user for the NewComputer\ASPNET login and give it the proper permissions.

In case you encounter this in the future with a SQL Server login, you can use sp_change_users_login to fix things up. Here's a video on how to do so:

Dealing with Orpaned Users in SQL Server


K. Brian Kelley
@‌kbriankelley
mvbokker
mvbokker
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
The strange thing about it was that on the same server, it ran fine within vs. Also, after granting aspnet all roles for user/role management, it still had no visibility to the tables.
mvbokker
mvbokker
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
I am obviously new to sql server...

Can you tell me how I can assign all aspnet roles/procedure to a user? I can see how to do it one by one, but is there a way to assign them at one time? This is within ss management studio.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (25K reputation)

Group: Moderators
Points: 25512 Visits: 1917
You are on SQL Server 2005, right? If so, first:

- Create a role. You can do this through SQL Server Management Studio's GUI.
- Make the ASPNET user account a member of the role. Again, you can do this through the GUI.

- Are all the objects in the same schema (such as dbo)? If so, you can GRANT *Permission* ON SCHEMA::*SchemaName* TO *Role* (you will need to open up a query window for that).

- If all access is through stored procedures (preferred from a security perspective), you only need to grant EXECUTE as the permission.
- If, however, the application makes direct access to tables and views, you will need to grant SELECT, INSERT, UPDATE, and DELETE. The shortcut for that is to grant ALL as the permission.

K. Brian Kelley
@‌kbriankelley
mvbokker
mvbokker
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 30
Thank you!

I created sql authentication and set up the website to run under one application userid. This prevents me from having to manage or store server/aspnet specific users in the database. One of the issues I had was because some controls within one of the webpages were using a trusted connection -- so it was creating the server/aspnet user which did not have permissions to do anything. I conformed all connections to use the app sql user and now everything works. I granted my app userid role privileges to all the dbo.aspnet objects and db_owner (which is probably overkill?).

I will take a look at what you suggested and modify the privileges.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search