Pau Lo
asked on
apps based on ms-sql, authentication question
Can I ask, if say you had an asp.net app, with sql 2005 instance that was the backend database for this basic payroll web application... and users need to login to the app with username/password.... are the users passwords typically hashed in the same table as those where you get the default sql accounts password hash like sa.
Also, what type of query is executed against the backend database when a user tries to login to such a web app, is it a a SELECT query against the password hash table? Or something different? Would there be a field in the table that has a default page to grant access to if username and password match a pair in the table, or a link to an error page if the username/password was wrong? How does the authentication query work and proceed based on if its a valid password or not?
The thing I was wondering about was if we find say a weak password against the default ms-sql "sa" account, for arguments sake, could that weak password associated with sa potentially grant an outsider access to an application from the web. Say the password for the sa account is "password" and as users credentials are hashed in the same table, if someone tried to login to the app with user "sa" password "password" could it potentially allow them access to the application, or will their likely be something in the hash table against the sa account that stops it for application login, its just for database admin/management etc?
Also, what type of query is executed against the backend database when a user tries to login to such a web app, is it a a SELECT query against the password hash table? Or something different? Would there be a field in the table that has a default page to grant access to if username and password match a pair in the table, or a link to an error page if the username/password was wrong? How does the authentication query work and proceed based on if its a valid password or not?
The thing I was wondering about was if we find say a weak password against the default ms-sql "sa" account, for arguments sake, could that weak password associated with sa potentially grant an outsider access to an application from the web. Say the password for the sa account is "password" and as users credentials are hashed in the same table, if someone tried to login to the app with user "sa" password "password" could it potentially allow them access to the application, or will their likely be something in the hash table against the sa account that stops it for application login, its just for database admin/management etc?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
yes, the syslogins database contains all SQL accounts, but it is not the same as application logins!!
you can try a small test, create a new application user, and see if it is added to the syslogins DB or not.
you can try a small test, create a new application user, and see if it is added to the syslogins DB or not.
ASKER
on the intranet app yes it was added....
where are they normally stored, a custom built table?
where are they normally stored, a custom built table?
yes, you may find a table named "users" or so.
ASKER
Will it use the same hashing algorithm as it does for sql authentication passwords? The last we'd want is for users passwords to be stored in the database plain text
this depends on how you develop the application, or what you use for managing your users, nothing related to the database itself.
ASKER
ok.... back to the other point.
When the app user enters his username and password and clicks "login" or whatever, can you give me a sample query the click will initiate in the database. I know it may depend, b ut just an example would help me better understand how the process works, no links, just an example please.
When the app user enters his username and password and clicks "login" or whatever, can you give me a sample query the click will initiate in the database. I know it may depend, b ut just an example would help me better understand how the process works, no links, just an example please.
I guess it might be like that 'using a stored procedure ' or some thing similar if you are sending query from you application.
IF(exists(Select *
from users
where Username= <USERNAME>
and PASSWORD = <HASHED_PASSWORD>))
return 1;
else
return 0;
IF(exists(Select *
from users
where Username= <USERNAME>
and PASSWORD = <HASHED_PASSWORD>))
return 1;
else
return 0;
ASKER