Security recommendations asp.net to SQL Server

[millerk]

Hi all,
I'm looking for recommendations on the best security setup for our situation. We have a Win 2K server that will host asp.net apps that allow access to data in SQL Server 2000 running on a different Win 2K server.

This will only be used on our internal intranet. We need to be able to limit access to the database depending on the user. Some will have no access, some will be able to view data and some will be able to modify and add data. We would like to avoid having the user enter user names and passwords. I know that we can use integrated windows authentication to control access to the web pages, but I'm not sure what the best way is to authenticate to the database. Some of our users have Win2K on their computers but the majority are on NT 4 so using Kerberos and delegating credentials doesn't look like an option. Althought this would be ideal if there are any other similar solutions.

Currently, our apps are mostly VB programs that access the SQL Server db using Windows authentication. All access is controlled through execute permissions on stored procedures. Only admins have direct access to the tables.

Anyone have ideas on the best way to do this?

Thanks in advance,
Shanti

 

[millerk]

anyone?

 

[RealQuiet]

Don't know if it's the best way, but I have set up a custom users table in the database. In the default page, I use the integrated Windows authentication and then just validate that username against my users table. I also have a couple of other tables that I have set up to create dynamic menus based on the user's security level. One thing I haven't been able to find is a way to automatically send the users back to a certain page if they haven't been validated. I need this to prevent someone to trying to directly access a page without going through the menu. I guess I would like it to work like the Forms Authentication method where you can specify a login page to send the users to if they are not authenticated. Because I am using Windows authentication, I don't know if this is possible. Any ideas?

 

[RealQuiet]

I have now placed
response.redirect("Default.aspx)
in the Session_Start event in the global asax. In the default.aspx page, I validate the user against the database (based on their Windows logon), and issue a session.abandon() if they are not validated. This seems to prevent someone from going to try to directly access a page without going through the validation process. Someone could still access a page they shouldn't have access to if they type it in directly after being validated.
You could also make sure that request.servervariables(&quot;http_referer&quot <> &quot;&quot; to make sure that they didn't type the url in directly.

 

[millerk]

If you are using Windows Authentication, you should be able to set up file permissions on each indidvidual page. So if you don't want Joe to be able to go directly to the HR salary list page, you can use NTFS file permissions to deny his windows account access to the SalaryList.aspx file. I think you will have to set up impersonation in your web.config file so ASP.net will try to access the file using the user's account.

We have considered doing something like this or having a table of users in the database. The problem is that in our db tables, we need to be able to track which users last edited a record or created a new one. Currently we can do this because the connection to SQL is made from vb clients with windows authentication so SQL knows who the user is. I'm not sure how best to do this connecting from the web server to the db. We could use standard SQL logins but we don't really want to maintain a whole set of standard logins separate from the windows logins in SQL. Plus, we don't want users to have to enter usernames and passwords everytime they use the ASP.Net app. And if we just give the aspnet account a login in SQL, we would have to have another method of identifying users. Which would mean we would have to rewrite all our stored procedures to take a user name as a parameter.
Hmmm, maybe it would be easier to convince our IT dept to upgrade everyone to Win 2K. Then we could use Windows authentication and pass the credentials to the database server. Maybe not.