January 22, 2009 at 10:40 pm
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:
January 23, 2009 at 8:57 am
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
January 23, 2009 at 9:07 am
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.
January 23, 2009 at 9:09 am
So you moved the application and database (both) from one server to another? Is that correct?
K. Brian Kelley
@kbriankelley
January 23, 2009 at 9:18 am
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.
January 23, 2009 at 9:35 am
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
January 23, 2009 at 9:45 am
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.
January 23, 2009 at 10:54 am
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.
January 23, 2009 at 11:53 am
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
January 23, 2009 at 2:41 pm
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply