I am designing a web application that will have probably around ~1000 users. The website is facing the internet but it can only be used by partners (i.e. the general public cannot just "sign up an account"). Account maintenance will be handled by our own IT support.
I am considering having each individual user to have their own SQL login associated. This way I do not need to store their salted/hashed password – SQL server will handle the authentication. This also allow us to use SQL Server based audit and logging tool.
Is there any disadvantage doing that?
Best Answer
Yes, plenty :)
You will have to setup encryption from the application server to the database server as the password is passed in clear text.
All login requests from the internet will result in an expensive authentication request to the database server.
You are not able to use connection pooling which will result in having a single connection for each user accessing the database.