authentication problems

[zerkat]

Need some help with setting up authentication for .net web app. This is my first time setting up authentication in .net so I might be missing something.

What I have is an app that needs two levels of permission - on the root folder domain users will have permission and in the admin folder only the admins will have permission.

I am working with IIS 6, VS 05 and SQL 05. The site in IIS has integrated authentication enabled - anonymous and other auth methods are disabled. The domain user group and admin group have permissions in SQL to the appropriate database. My web.config contains:

authentication mode="windows"
Identity impersonate = "true"

connectionString="Data Source=servername\serverInstance; Database=DBName;Trusted_Connection=Yes;Application Name=AppName"

I tested the site with one of our test users on our development environment. The test user can access a page under the root of the site and execute a stored procedure with no problems but when I try to go to a page located in the admin directory I receive an error stating that the user does not have permission to execute a stored procedure. Even though this user is in both groups, domainUser and admin, and they both have permissions to the DB. I also gave the users explicit execute permission to the stored procedures. I checked that SQl was using windows authentication mode and it is.

From event viewer it looks like .net is not impersonating the user in the admin folder. The error logged is:

The execute permission was denied on the object 'spStoredProcedureName'
Account Name: Network Service

The last time I got this error was because I didn't have identity impersonate = true in the web.config but it's there now and there is only one web.config for the whole site. I removed domain users on the file permissions and tried just using the admin file permissions for the whole site and get the same result. I also checked various other settings in IE to no avail.

 

[jmeckley]

you have authentication configured. now you need to configure authorization. here is one example

<system.web>
   ...
   <authorization>
      <deny users="?" />
      <allow users="*" />
   </authorization>
</system.web>
<location path="my secure directory">
    <authorization>
      <allow roles="DOMAIN\Name Of Admin Group" />
      <deny users="*" />
   </authorization>  
</location>

? = anonymous users
* = all authenticated users

so the main part of the side is denied to any users not authenticated and available to anyone who is authenticated.
the 'my secure directory' will allow any user who is part of the Domain\Admin group to access pages within this directory. It will deny all other users.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[zerkat]

Thanks for the sample. I had made an authorization section in web.config and gave the admin group rights and no one else to see if there was a problem with the group. That did not work.

Right before I left work last night I saw a posting on another technical forum by a DBA. He suggested trying GRANT EXECUTE TO groupName On StoredProcedure. Even though I already granted execute through the GUI, I ran the script in a new query window in SQL and everything started to work for my test user.

I asked our DBA this morning if there is a difference in setting the permissions up through the GUI or a query and she said no. Maybe there is something with my set up of the database, or the way the database was originally set up - it was here when we got here so no one here is claiming ownership, or the fact that we had it running in SQL 2000 backwards compatibility mode and changed it to SQL 2005. Who knows but it's working now. DBA said she would test it out and see if she can figure out why that happened.

Thanks for the help.