SQL Server Authentication using ASP.Net-easy but i'm confused

[eramgarden]

Been reading about Authentication against SQL Server and finally found this site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/PAGHT000010.asp

We have sql server database, each user has a Login (my manager doesnt want to use Windows Authentication). Login page has "user id" and "password". Then I need to authenticate the user against the SQL database based on that userID/password..

That site shows a connection string with userid and password, then makes a select to Pubs database. It says : must include the user name and password of the database user

But I have like 100 database users with Logins. And I dont know their passwords anyway. then how can I use the SQL Server authentication? Should I be building the connection string dynamically in the code with their userid and password...instead of having a connection string in the web.config?

Just really lost! and Im sure its not that hard..

 

[PlausiblyDamp]

If you want (perhaps are being forced is a better phrase) to use sql authentication then you will need to generate a connection string for every user. If the information they provide to your login screen matches their sql account then you will be able to build the correct connection string.
If the information is different then you will have to prompt them for that as well, annoying and invariably going to cause confusion and misery.

Best thing is if you manage to get this working you will be rewarded for your efforts by worse performance and increased load on your SQL Server. Bonus!

The main reason for the performance impact is creating a connection per user per database effectively prevents ASP being able to pool the connections.

Ideally you want to validate the user at the website level and based on that decide what they can and cant do, all data access is done under the single account used by the ASP application.

 

[eramgarden]

Ideally you want to validate the user at the website level and based on that decide what they can and cant do, all data access is done under the single account used by the ASP application.

Not sure what you mean...do you mean use Windows authentication..so NO login page where users enter their userid/password?

I did mention Windows authentication to my manager but he says maybe the SQL user Login is different from the Windows user login and the application depends on the "SQL user login ID" to do whatever it needs to do...

for example, SQL UserID might be XXX but Windows userID is YYY..is that possible???

 

[PlausiblyDamp]

If the users are longing into your website then that is how you identify them. All your security is based around the user and associated groups used by the website. These may be windows based if you are using windows authentication, however this could be your own security model implemented as Forms authentication.
Regardless of how they login to the site only a single user (the web application itself) ever accesses the database.

SQL Logins and windows accounts are completely separate concepts and dont overlap.

 

[eramgarden]

Still light bulb is not as bright..

I also read about the Forms Authentication. This what confuses me:

Regardless of how they login to the site only a single user (the web application itself) ever accesses the database. ...

I understand that..So, Ill have a userid/password in the web.config...

Now, my website user enters his userid/password on the login page...my website connects to the database using the "userid/password in the webconfig".... now how do I know the users login/password exists in the database? I can get the login ID from the sql server..but how password are encrypted in sql server...

u know what I mean? I connect to sql DB using the userid/pwd I have in the web.config..but my users also have a userid/password..how do I authenticate them??

 

[PlausiblyDamp]

You dont. The users sql login and password never enter into things.

 

[eramgarden]

Hmm..then how would I know they entered the correct userid/password?
User enters his userid/pwd....website connects to the database using the userid/pwd in the webconfig ..this means the application can connect to the database..doesnt mean the user can use the database...right?

if i only use the "userid/pwd in webconfig"..wouldnt that be like windows authentication (and my manager doesnt want windows authentication)...

so confused..just need an example even if in puesdo code...

 

[eramgarden]

I finally got it

http://aspnet.4guysfromrolla.com/articles/103002-1.aspx