I need to create an ASP.NET front end for an existing SQL Server Database. The Database has 200+ SQL Server User names in it. Each user name is assigned to any of the 9 SQL Server Roles. All ASP.NET pages will have sensitive data that only certain users within the SQL Server roles can view. All pages are driven by the data on the ASP application.

When a user enters a record the record gets a stamp with the USER() stamp from SQL Server. This is used to open their records next time they come into the database. Windows Authentication is not possible in this environment due to political reasons. I know, this would simplify things and make this more secure, but…(politics)

Here is my question:
I need to get the SQL Server user name and the password from the user that comes into the site. How do I keep this user name and password securely so that I can use it in all 70+ pages that the application will have? I do not want them to have to re enter their usename and password more than once, and i need this to be secure.

If I create one blanket SQL Server user that I place in the web.config file, I lose the ability to track individuals inside of the database and the database becomes useless.




Sal

__________________
Sal

You can use forms authentication to create a login system. The login actually have to do anything, but you could probably link it up to the database server's users table to verify that they are a valid SQL user. For this query you'll need some admin role user for the connection string.

Once the user is logged in, you can store the username and password in session so that you can retrieve it on all the pages that are making queries.

You could store the connection string in the web config like this:
"user id={0};password={1};Data Source=<server>;Initial Catalog=<database>;"

Then whenever you use the connection string, just run it thru String.Format:
strConnString = ConfigurationSettings.AppSettings("ConnString")
objConn.ConnectionSring = String.Format(strConnString, Session("uid"), Session("pwd"))

This way you can keep the connection details centralized but have the user part dynamic.

My database table for keeping users and passwords is SQL Servers sysxlogins table from the Master database. You mean that this table can be used in forms authentication? I am not sure if I want to use a sytem table for this, but I do not want to keep another table with usernames and passwords when sysxlogins and sysusers is keeping that already.



Sal

If you want to use the SQL logins as your user logins then you don't have much other choice. Your login process doesn't have to really do anything aside from take a username and password. Then any time you try to make a query call using the user provided name and password the query will either work (good login) or fail (bad login). The only way you might have of telling that the user login is good when they actually log in is to try a simple query. If you get back a bad password error from SQL you'll know that the user and/or password is bad. The passwords in SQL Server are stored in some encrypted or hashed for so you may not be able to query that table directly to check the login.