Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deployment for ASP.NET User Membership Role Management Expand / Collapse
Author
Message
Posted Thursday, January 22, 2009 10:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 23, 2009 11:20 AM
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!
Post #642270
Posted Friday, January 23, 2009 8:57 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #642523
Posted Friday, January 23, 2009 9:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 23, 2009 11:20 AM
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.
Post #642535
Posted Friday, January 23, 2009 9:09 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
So you moved the application and database (both) from one server to another? Is that correct?


K. Brian Kelley, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #642538
Posted Friday, January 23, 2009 9:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 23, 2009 11:20 AM
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.

Post #642557
Posted Friday, January 23, 2009 9:35 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #642584
Posted Friday, January 23, 2009 9:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 23, 2009 11:20 AM
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.

Post #642597
Posted Friday, January 23, 2009 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 23, 2009 11:20 AM
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.
Post #642653
Posted Friday, January 23, 2009 11:53 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #642685
Posted Friday, January 23, 2009 2:41 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 23, 2009 11:20 AM
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.
Post #642803
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse